A N

February 4, 2016

Get content of larg files, replace and bulkimport into SQL

Filed under: Tips — donrsh @ 2:23 pm

I had a few and larg log files which was around 500 MB each and looked like this:

Login Succeeded	server38	admin	2013-07-11 16:10:35	1
..Logout Succeeded	server4438	admin1	2013-07-11 16:15:35	1
Login Succeeded	server4438	admin2	2013-07-11 16:18:14	1
..Logout Succeeded	server4438	admin	2013-07-11 16:24:05	1
Login Succeeded	server1106	admin1	2013-07-12 10:53:20	1
Login Succeeded	server1106	admin2	2013-07-12 11:34:29	1

[System.IO.File]::ReadAllLines Read More Here

$Files = Get-ChildItem  'C:\Files'
foreach ($File in $Files)
{
	$FullName = $File.FullName
	([System.IO.File]::ReadAllLines($FullName)) -replace ('..Logout Succeeded', 'Logout') -replace ('Login Succeeded', 'Login') |
	Out-File -FilePath 'C:\Files\Export.csv' -Append
}

The result is:

Login server38    admin   2013-07-11 16:10:35 1
Logout  server4438  admin1   2013-07-11 16:15:35 1
Login server4438  admin2   2013-07-11 16:18:14 1
Logout  server4438  admin   2013-07-11 16:24:05 1
Login server1106  admin1  2013-07-12 10:53:20 1
Login server1106  admin2  2013-07-12 11:34:29 1

Now lets bulk import it to SQL

-- 1
GO
CREATE TABLE CSVTest
(
            Event VARCHAR(40),
            CitrixServer VARCHAR(40),
            Username VARCHAR(40),
            "DateTime" VARCHAR(40),
            "status" VARCHAR(40)
)
GO

-- 2

BULK
INSERT CSVTest
FROM 'C:\Files\Export.csv'
WITH
(
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n'
)
GO

--3

select [Event],[CitrixServer],[UserName],CONVERT(datetime,[DateTime]) [DateTime],[Status] into Logs from CSVTest

Advertisements

January 11, 2016

January-2016-scripting-games-puzzle | Get-UpTime

Filed under: Tips — donrsh @ 3:25 pm

My solution for January-2016-scripting-games-puzzle

Get-UpTime


<#	
	.NOTES
	===========================================================================
	 Created on:   	2016-01-11 08:28
	 Created by:   	Arash Nabi
	 Mail:          arash@nabi.nu 	
	===========================================================================
	.SYNOPSIS
		A brief description of the Get-UpTime function.
	
	.DESCRIPTION
		Get uptime of the remote server. This is 2016-January Scripting Games Puzzle
	    For more info please visist: http://powershell.org/wp/2016/01/02/january-2016-scripting-games-puzzle/
	.EXAMPLE
		PS C:\> Get-UpTime -ComputerName 
	    PS C:\> $value1 | Get-UpTime
#>

function Get-UpTime
{
	[CmdletBinding()]
	param
	(
		[Parameter(Mandatory = $false,
				   ValueFromPipeline = $true)]
		$ComputerName = $env:COMPUTERNAME
	)
	
	Process
	{
		try
		{
            $AllObj = @()
			foreach ($PC in $ComputerName)
			{
				$params = @{
					'ComputerName' = "$PC"
				}
                $PingComputer = Test-Connection -ComputerName $PC -ErrorAction SilentlyContinue
				if (-not ($PingComputer))
				{
					$params.Add("Status", "Offline")
					Write-Warning "$PC is Offline"
				}
				# 
				if ($PingComputer)
				{
					$objOption = New-CimSessionOption -Protocol Dcom -ErrorAction SilentlyContinue
					$objSession = New-CimSession -ComputerName $PC -SessionOption $objOption -ErrorAction SilentlyContinue
					if ($objSession)
					{
						$LastBootUpTime = Get-CimInstance -CimSession $objSession -Namespace ROOT/cimv2 -ClassName Win32_OperatingSystem | select LastBootUpTime
						if ($LastBootUpTime)
						{
							$upTime = New-TimeSpan -Start $($LastBootUpTime.LastBootUpTime) -End (get-date)
							$params.add("StartTime", "$($LastBootUpTime.LastBootUpTime)")
							$params.Add("Status", "OK")
							$params.Add("UpTime (Days)", "$($upTime.Days)")
						}
						
						if ($upTime -gt 30)
						{
							$params.add("MightNeedPatched", "Yes")
						}
						else
						{
							$params.add("MightNeedPatched", "No")
						}
					}
					else
					{
						$params.Add("Status", "Error")
					}
				}
				
				$objresult = New-Object psobject -Property $params
				$AllObj += $objresult
			}
			
		}
		
		catch [System.Net.WebException], [System.Exception]
		{
			$errorMessage = $_.Exception.Message
            Write-Warning -Message $errorMessage

		}
		finally
		{
			$AllObj | Select-Object ComputerName, StartTime, 'Uptime (Days)', Status, MightNeedPatched | Format-Table -AutoSize
		}
	}
	
}


	

December 11, 2015

Powershell – Get Disc Report

Filed under: Tips — donrsh @ 3:33 pm
<#	
	 Created on:   	2015-09-09 
	 Created by:   	Arash Nabi
	 Email: 	    arash@nabi.nu 
     
     Get disk report. Export the result to csv file for further tuning in Excel.
	
#>
try
{
	$VerbosePreference = 'Continue'
	$diskObj = @()
	$Servers = Get-Content -Path "C:\Servers.txt"
	
	foreach ($server in $servers)
	{		
		# CIM stuff
		$objOption = New-CimSessionOption -Protocol Dcom -ErrorAction SilentlyContinue
		$objSession = New-CimSession -ComputerName $server -SessionOption $objOption -ErrorAction SilentlyContinue
		if ($objSession)
		{
			$params = @{
			'ServerName' = $server
			}
			$allDisks = Get-CimInstance -CimSession $objSession -Namespace ROOT/cimv2 -ClassName Win32_Volume -ErrorAction SilentlyContinue | Where-Object { $_.Capacity -gt 1 } |
			select driveletter, capacity, freespace
			foreach ($disk in $alldisks)
			{
				$DisksizeGB = [math]::round($disk.capacity/1GB)
				$diskLetter = $disk.Driveletter.Replace(':', '')
				$DiskUsed = [math]::round(($disk.capacity - $disk.freespace) /1GB)
				$DiskFree = [math]::round(($disk.freespace /1GB))
				$percentFree = "{0:P1}" -f ($DiskFree/$DisksizeGB)
				$params.Add($("$diskLetter Drive"), $("($percentFree Free) Total $disksizeGB GB, Used $DiskUsed GB, Free $DiskFree GB"))
			}
			# Create objects
			$objresult = New-Object psobject -Property $params
			# Add object to result
			$diskObj += $objresult
			Write-Verbose -Message $server
		}
		else
		{
			Write-Warning "No CimSession connection to $server. Aborting"
		}
	}
	$diskObj | select Servername, 'C Drive', 'D Drive', 'F Drive', 'E Drive', 'G Drive' | Export-Csv -Path c:\export.csv -NoTypeInformation
}
catch [System.Net.WebException] #, [System.Exception]
{
	$errorMessage = $_.Exception
	Write-Warning -Message $errorMessage
}
finally
{
	Write-Verbose -Message 'End of Script'
}

October 8, 2015

Get server architecture with CimSession

Filed under: Tips — donrsh @ 12:26 pm
<#   
    .NOTES
    ===========================================================================
     Created on:    2015-10-08 
     Created by:    Arash Nabi
     Email:         arash@nabi.nu 
 
    ===========================================================================
    .DESCRIPTION 
        Get server architecture info through CimInstance.
        The function creates a new cimSession to a remote computer and collects
        server architecture information and then creats a powershell object of them.
    .Exampel
        PS C:\> Get-Architecture -Servers $value1
     
#>


function Get-Architecture
{
	[CmdletBinding()]
	[OutputType([int])]
	Param
	(
		[Parameter(Mandatory = $true,
				   ValueFromPipelineByPropertyName = $false,
				   Position = 0)]
		$Servers
		
		
	)
	
	Process
	{
		try
		{
			$serverarch_obj = @()
			
			foreach ($server in $servers)
			{
				
				# CIM stuff
				$objOption = New-CimSessionOption -Protocol Dcom -ErrorAction Stop
				$objSession = New-CimSession -ComputerName $server -SessionOption $objOption -ErrorAction Stop
				
				$WinOS = Get-CimInstance -CimSession $objSession -Namespace ROOT/cimv2 -ClassName Win32_OperatingSystem -ErrorAction Stop
				$Model = (Get-CimInstance -CimSession $objSession -Namespace ROOT/cimv2 -ClassName Win32_ComputerSystem -ErrorAction Stop ).Model
				$IP    = (Get-CimInstance -CimSession $objSession -Namespace ROOT/cimv2 -ClassName Win32_NetworkAdapterConfiguration -ErrorAction Stop | 
                    where { $_.ipaddress -like "1*" } | select -ExpandProperty ipaddress | select -First 1 -ErrorAction SilentlyContinue)
				$CPU   = Get-CimInstance -CimSession $objSession -Namespace ROOT/cimv2 -ClassName Win32_Processor -ErrorAction Stop
				
				
				$params = @{
					'ServerName'        = $server
					'Architecture'      = $WinOS.OSArchitecture
					'Operating System'  = $WinOS.Caption
					'Model'             = $Model
					'CPU'               = ($CPU.Name | Select-Object -First 1)
					'IP'                = $IP
					'RAM'               = ([math]::round($WinOS.TotalVisibleMemorySize/1MB)).ToString() + ' GB'
					'Cores'             = ($CPU.NumberOfCores).count
					'LogicalProcessors' = ($CPU.NumberOfLogicalProcessors).Count
					
					
				}
				
				$allDisks = Get-CimInstance -CimSession $objSession -Namespace ROOT/cimv2 -ClassName Win32_Volume -ErrorAction Stop |
                     select driveletter, capacity
				
				foreach ($disk in $alldisks)
				{
					$DisksizeGB = [math]::round($disk.capacity/1GB)
					$diskLetter = $disk.Driveletter.Replace(':', '')
					$params.Add($("$diskLetter Drive"), $("$disksizeGB GB"))
					
				}
				
				# Create objects
				$objresult = New-Object psobject -Property $params
				
				# Add object to result
				$serverarch_obj += $objresult
				
			}
			
		}
		catch [System.Net.WebException], [System.Exception]
		{
			$errorMessage = $_.Exception.Message
            Write-Warning -Message $errorMessage
		}
		finally
		{
			$serverarch_obj
		}
	}
	
}

October 5, 2015

Delay a certain minute (countdown) with Powershell Write-Progress

Filed under: Tips — donrsh @ 12:24 pm
<#	
	.NOTES
	===========================================================================
	 Created on:   	2015-09-09 
	 Created by:   	Arash Nabi
	 Email: 	    arash@nabi.nu 

	===========================================================================
	.DESCRIPTION 
		The function creates a write-progress bar with coundown. Usefull in script
		when you want to paus a certain minut(s)
	.Exampel
		Delay-Time -Min 10
	
#>
function Delay-Time
{
	[CmdletBinding()]
	[OutputType([int])]
	Param
	(
		
		[Parameter(Mandatory = $true,
				   ValueFromPipelineByPropertyName = $true,
				   Position = 0)]
		[int]$Min
	)
	$D = get-date
	$x = $min * 60
	$length = $x / 100
	while ($x -gt 0)
	{
		$f = $d.AddMinutes(1)
		$min = [int](([string]($x/60)).split('.')[0])
		$text = " " + $min + " minutes " + ($x % 60) + " seconds left"
		Write-Progress "Please wait until $f" -status $text -perc ($x/$length) -Verbose
		start-sleep -s 1
		$x--
		Write-Progress -Activity Done! -Completed -Verbose
	}
	
}

Restore Microsoft SQL *.bak file with the help of Powershell

Filed under: Tips — donrsh @ 12:17 pm
<#	
	.NOTES
	===========================================================================
	 Created on:   	2015-09-09 
	 Created by:   	Arash Nabi
	 Email: 	    arash@nabi.nu 

	===========================================================================
	.DESCRIPTION for 'Run-DBQuery' function
		The function connects to a Microsoft SQL database and query a TSQL query
	.Exampel
		Run-DBQuery -Database SERVER\SQLEXPRESS -QueryString "SELECT * FROM [table].[dbo].[DatabaseName]"
	===========================================================================
	.DESCRIPTION for 'Restore-DB' function
		The function restore a *.bak file to a Microsoft SQL database.
	.Exampel
		Restore-DB -Database DatabaseName -ServerInstance SERVER\SQLEXPRESS -BackupFile C:\bakfile.bak -ReplaceDatabase
		You have two switches, ReplaceDatabase and Confirm.
#>
function Run-DBQuery
{
	[CmdletBinding()]
	[OutputType([int])]
	Param
	(
		[Parameter(Mandatory = $True,
				   HelpMessage = "Please specify the SQL Server instance. Ex Computer\Instance",
				   ValueFromPipeline = $False)]
		[ValidateNotNullorEmpty()]
		$Database,
		[Parameter(Mandatory = $true,
				   ValueFromPipelineByPropertyName = $true,
				   HelpMessage = "Please specify the TSQL query.",
				   Position = 0)]
		[OutputType([System.Data.DataTable])]
		$QueryString
		
		
	)
	
	$ConnectionString = "Data Source=$Database;Integrated Security=True;User ID=;Password="
	
	$command = New-Object System.Data.SqlClient.SqlCommand ($QueryString, $ConnectionString)
	$adapter = New-Object System.Data.SqlClient.SqlDataAdapter ($command)
	
	#Load the Dataset
	$dataset = New-Object System.Data.DataSet
	[void]$adapter.Fill($dataset)
	
	#Return the Dataset
	return @(, $dataset.Tables[0])
}
function Restore-DB
{
	[CmdletBinding()]
	[OutputType([int])]
	Param
	(
		[Parameter(Mandatory = $true,
				   ValueFromPipelineByPropertyName = $False,
				   HelpMessage = "Please specify the SQL Server Database Name. Ex MyDB",
				   Position = 0)]
		[ValidateNotNullorEmpty()]
		$Database,
		[Parameter(Mandatory = $True,
				   HelpMessage = "Please specify the SQL Server instance. Ex Computer\Instance",
				   ValueFromPipeline = $False)]
		[ValidateNotNullorEmpty()]
		$ServerInstance,
		[Parameter(Mandatory = $false,
				   HelpMessage = "Please specify the SQL Server instance. Ex Computer\Instance",
				   ValueFromPipeline = $False)]
		[ValidateNotNullorEmpty()]
		$BackupFile,
		[Parameter(Mandatory = $false,
				   HelpMessage = "Do you want to replace this database?",
				   ValueFromPipeline = $False)]
		[switch]$ReplaceDatabase,
		[Parameter(Mandatory = $false,
				   HelpMessage = "Confrim the action?",
				   ValueFromPipeline = $False)]
		[switch]$Confirm
	)
	try
	{
		$Mod = Get-Module -ListAvailable | Where-Object { $_.Name -eq 'SQLPS' }
		if ($Mod)
		{
			Write-Verbose "Powershell SQL Module exist in $env:COMPUTERNAME. Continuing to restore database $Database"
			Import-Module -Name SQLPS -Verbose -ErrorAction Stop
			$GetDBSettings = Run-DBQuery -Database $ServerInstance -QueryString "select 
    InstanceDefaultDataPath = serverproperty('InstanceDefaultDataPath'),
    InstanceDefaultLogPath = serverproperty('InstanceDefaultLogPath')"
			$SQLDataPath = $GetDBSettings.InstanceDefaultDataPath
			$SQLLogPath = $GetDBSettings.InstanceDefaultLogPath
			
			Write-Verbose "Default Data Path is: $SQLDataPath"
			Write-Verbose "Default Log Path is: $SQLLogPath"
			
			$RelocateData = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($Database, "$($SQLDataPath)$Database.mdf")
			$RelocateLog = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("$($Database)_log", "$($SQLLogPath + $Database)_log.ldf")
			
			$params = @{
				'Serverinstance' = $ServerInstance
				'Database' = $Database
				'Backupfile' = $BackupFile
				'RelocateFile' = @($RelocateData, $RelocateLog)
			}
			
			
			if ($DatabaseObject)
			{
				$params.Add('DatabaseObject', $true)
			}
			if ($PSBoundParameters['Verbose'])
			{
				$params.Add('verbose', $true)
			}
			
			if ($ReplaceDatabase)
			{
				$params.Add('ReplaceDatabase', $true)
			}
			if ($Confirm)
			{
				$params.Add('Confirm', $true)
			}
					
			Restore-SqlDatabase @params
		}
		else
		{
			Write-warning "There is no powershell module for SQL exist in $env:COMPUTERNAME. Aborting..." -ForegroundColor Magenta
			return
		}
		
	}
	catch [System.Net.WebException], [System.Exception]
	{
		$errorMessage = $_.Exception.Message
		Write-Warning -Message "$errorMessage"
	}
	finally
	{
		
	}
	
}

Powershell ‘Write-Log’ function

Filed under: Tips — donrsh @ 11:41 am

Powershell function to write log.

<#	
	.NOTES
	===========================================================================
	 Created on:   	2015-10-05 
	 Created by:   	Arash Nabi
	 Email: 	arash@nabi.nu 
	===========================================================================
	.DESCRIPTION
		Write-Log function creates a log file in the $PSScrtipRoot. It's usefull
                to call it in your script and output some message or verbose output. 
	==========================================================================
	.EX
		Write-Log -Message "Email report send"
#> 
function Write-Log
{
	[CmdletBinding()]
	[OutputType([int])]
	Param
	(
		[Parameter(Mandatory = $true,
				   ValueFromPipelineByPropertyName = $False,
				   HelpMessage = "Please enter Message for the log",
				   Position = 0,
				   ValueFromPipeline = $True)]
		[ValidateNotNullorEmpty()]
		$Message
		
		
	)
	try
	{
		$LogFolder = "$PSScriptRoot\log"
		$d = get-date
		$M = $d.Month.ToString()
		$Y = $d.Year.ToString()
		$LogFile = "$LogFolder\$("Script" + "-" + $Y + "-" + $M + ".log")"
		if (!(Test-Path -Path $LogFile))
		{
			$Dir = New-Item -Path $LogFolder -ItemType Directory -Force
			$Log = New-Item -Path "$Dir\$("Script" + "-" + $Y + "-" + $M + ".log")" -ItemTyp File -Force
			
		}
		$DateTime = (get-date -Format "yyyy-MM-dd HH:mm:ss")
		if ($PSBoundParameters['Message'])
		{
			"[$DateTime] " + $Message | out-file $LogFile -ErrorAction SilentlyContinue -append
		}
		
	}
	catch [System.Net.WebException], [System.Exception]
	{
		$errorMessage = $_.Exception.Message
		Write-Warning -Message $errorMessage
	}
	finally
	{
	}
}

September 21, 2015

Get Dilbert Cartoon with Powershell

Filed under: Tips — donrsh @ 8:17 am

Get Today’s or even yesterdays Dilbert Cartoon with Powershell with Invoke-WebRequest from http://www.dilbert.com and send it as email with embedded picture.

   <#
   .NOTES 
   ===========================================================================

      Created on:   	2015-09-21 09:05
      Created by:   	Arash Nabi
      Email:            arash@nabi.nu

  ===========================================================================

   .Synopsis
      Send Daily Dilbert Cartoon from www.dilbert.com
   .EXAMPLE
      Todays Dilbert: Get-Dilbert
   .EXAMPLE
      Yestardays Dilbert: Get-Dilbert -last -1
   #>
function Get-Dilbert
{
	[CmdletBinding()]
	[OutputType([int])]
	Param
	(
		[Parameter(ValueFromPipelineByPropertyName = $true,
				   HelpMessage = "Choose last date",
				   Position = 0)]
		[ValidateSet ('-1',
		'-2',
		'-3',
		'-4')]
		[int]$Last
		
		
	)
	
	
	Process
	{
		$VerbosePreference = "Continue"
		if ($Last)
		{
			$lastDate = (get-date).AddDays($Last) | get-date -Format "yyyy-MM-dd"
			Write-Verbose 'Invoking website'
			$dil = Invoke-WebRequest -Uri "http://dilbert.com/strip/$LastDate" -UseBasicParsing
			$bild = $dil.Images | Where-Object { $_.class -eq 'img-responsive img-comic' } | Select-Object src
			Invoke-WebRequest $bild.src -OutFile "$PSScriptRoot\$LastDate.gif"
			Write-Verbose 'Sending email'
			Send-MailMessage -to Email.To@domain.com -From Email.From@domain.com -SmtpServer smtp.domain.com -Subject "Dilbert $LastDate" -BodyAsHtml "<br/><img src='$LastDate.gif' />" -Attachments "$PSScriptRoot\$LastDate.gif"
		}
		else
		{
			$today = (get-date -Format "yyyy-MM-dd")
			Write-Verbose 'Invoking website'
			$dil = Invoke-WebRequest -Uri "http://dilbert.com/strip/$today" -UseBasicParsing
			$bild = $dil.Images | Where-Object { $_.class -eq 'img-responsive img-comic' } | Select-Object src
			Invoke-WebRequest $bild.src -OutFile "$PSScriptRoot\$today.gif"
			Write-Verbose 'Sending email'
			Send-MailMessage -to Email.To@domain.com -From Email.From@domain.com -SmtpServer smtp.domain.com -Subject "Dilbert $today" -BodyAsHtml "<br/><img src='$today.gif' />" -Attachments "$PSScriptRoot\$today.gif"
			
		}
		
		
	}
	
}

June 25, 2015

SQL – First and Last day of previous month

Filed under: Tips — donrsh @ 1:36 pm
	select (dateadd(month,datediff(month,(0),getdate())-1,(0))),
	'first day of previous month'
	union
	select (dateadd(month,datediff(month,(0),getdate())-1,(30))),
	'Last day of previous month' 

June 17, 2015

unZip with Powershell

Filed under: Tips — donrsh @ 1:37 pm

Server 2012 comes with Dot.NET 4.5 which has System.IO.Compression.ZipFile which has a ExtractToDirectory method. You should be able to use this from PowerShell.

Here is an example.

First you need to load the assembly ZipFile is in:

[System.Reflection.Assembly]::LoadWithPartialName("System.IO.Compression.FileSystem") | Out-Null
Then extract the contents
[System.IO.Compression.ZipFile]::ExtractToDirectory($pathToZip, $targetDir)

If you have updated to PowerShell 5 (Windows Management Framework 5.0) you finally have native cmdlets:

Expand-Archive $pathToZip $targetDir
« Newer PostsOlder Posts »

Create a free website or blog at WordPress.com.