#62 : Playing with Windows COM

This script is nothing more than just playing with Windows COM shell. I don't see any purpose of it. But this is good for playing, because as much you play, you learn a lot. If you cannot play with something, you will never understand things completely.

	$winshell = new-object -com Shell.Application
	$winshell.explore("C:\tmp")
	#--Cascade all windows --#
	start-sleep -s 2
	$winshell.cascadewindows()
	#--Tile vertically --#
	start-sleep -s 2
	$winshell.tilevertically()
	#--Minimize all --#
	start-sleep -s 2
	$winshell.minimizeall()

view raw playing_COM.ps1 hosted with ❤ by GitHub

Hope you like this post, send your comments to me!
Enjoy!

#61 : How to connect SQL Server with Powershell?

Powershell can be used to run queries against SQL Server. In fact, what C# and other .NET Framework based languages can do, you can do all of them wit h Powershell. You can connect SQL Server and perform all operations which you do with Powershell.

Here is a simple code for this :

	#--------------------------------------------------------------------------------------------------------------
	#-- Script to run query and get recordset
	#--------------------------------------------------------------------------------------------------------------
	function Run-Query( [string]$InstanceName , [string]$DatabaseName, [string]$Query)
	{
	$SQLInstance = $InstanceName
	$DBName = $DatabaseName
	$SQLQry = $Query
	$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
	$SqlConnection.ConnectionString = "Server = $SQLInstance; Database = $DBName; Integrated Security = True"
	$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
	$SqlCmd.CommandText = $SQLQry
	$SqlCmd.Connection = $SqlConnection
	$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
	$SqlAdapter.SelectCommand = $SqlCmd
	$DataSet = New-Object System.Data.DataSet
	$SqlAdapter.Fill($DataSet)
	$SqlConnection.Close()
	#--Show data in table --#
	$DataSet.Tables[0]
	}
	#--Check the running processes --#
	Run-Query "SQLTEST" "master" "select top 1 * from master..sysprocesses"
	#--Find the blocked SPID --#
	Run-Query "SQLTEST" "master" "select * from master..sysprocesses where blocked > 1 "
	#--------------------------------------------------------------------------------------------------------------

view raw connect-sql.ps1 hosted with ❤ by GitHub

Hope you like this article! It's too late night (2:30 AM) and I have to go to bed.
Enjoy!

#60 : Script to write into Excel Sheet

SUMMARY

In some cases, we need to create excel and upload as attachment to mail. This tasks is so simple when done manually, but we cannot be doing the same work everyday if we need to send Excel sheet everyday. We can try to automate it. But the automation should be as accurate as manual. It sounds very good, but sending a formatted excel document is not so easy.

We can simply attach a CSV which will automatically open in Excel, but CSV is not good for below reasons :

1. CSV does not show columns in correct length.
2. CSV does not color certain fields.
3. CSV will not show bold or italics.

So, the purpose will not be solved with CSV. So here I will use Microsoft ACE to generated the formatted report.

CODE

	#------------------------------------------------------------------------------------------------------------------------
	# Write to Excel Sheet
	#------------------------------------------------------------------------------------------------------------------------
	$strFileName = "C:\Temp\Files.xlsx"
	$strSheetName = 'Info$'
	$strProvider = "Provider=Microsoft.ACE.OLEDB.12.0"
	$strDataSource = "Data Source = $strFileName"
	$strExtend = "Extended Properties=Excel 12.0;"
	$strQuery = "UPDATE [$strSheetName] set [C1]='CHECK1' where [B1]='CHECK2' "
	$objConn = New-Object System.Data.OleDb.OleDbConnection ("$strProvider;$strDataSource;$strExtend")
	$objConn.Open()
	$sqlCommand = New-Object System.Data.OleDb.OleDbCommand
	$sqlCommand.Connection = $objConn
	$sqlCommand.CommandText = $strQuery
	$sqlCommand.ExecuteNonQuery()
	$sqlCommand.Connection.Close()

view raw write_to_excel.ps1 hosted with ❤ by GitHub

CONCLUSION

This article is still being written.
 

#59 : Get Folder Size with Powershell

Sometimes, we need to check the folder size. Although, ls can give your results of certain files, but still you need to struggle to get the folder (directory) size. I wrote one small script to get this with Powershell. You may use this recipe if you are writing something where folder size is required to be checked.

	#------------------------------------------------------------------------------------------
	# Script : Get_FolderSize.ps1
	# Author : Som DT.
	# Purpose : Script to find the folder size
	#------------------------------------------------------------------------------------------
	#--Function Declaration --#
	function Get-FolderSize ([string] $FolderName)
	{
	$value = "{0:N2}" -f ((Get-ChildItem -recurse $FolderName | Measure-Object -property length -sum).Sum / 1MB)
	echo "Directory Name : ${FolderName} : Size : ${value} MB"
	}
	#--Calling the function--#
	Get-FolderSize "C:\Temp"
	#------------------------------------------------------------------------------------------

view raw Get_FolderSize.ps1 hosted with ❤ by GitHub

Thanks for reading this article!

#58 : Find the disk volumes in host

WMI gives a lot of such important information which makes windows administration more manageable. Scripting becomes more and more robust than ever before. 

Powershell can be used to call WMI objects and we can get so much information out of it. 

CODE : 

get-wmiobject win32_logicaldisk | select DeviceId,Size, FreeSpace

You might get a result in proper tabular format. There are lot many scripts possible with this. 

1. Script to report low disk space

2. Script to display disk inventory in multiple hosts 

If you have trouble writing scripts mentioned above, just add your request in comment,  I will pick from there.

#57 : Find running Services with Powershell

Find the List of Running Services:

You can get list of all Processes with command like Get-Service. This CmdLet gives you list of all services which are running and stopped.
But sometimes, you might need to know a list of Service which is currently running.

Below Statements can be used for the same -

#--Get the Names of Running Services --#  
Get-Service | where { $_.Status -eq "Running" } | format-table Name  

Stopping a Service with Powershell :
 

Stopping a service is fairly simple with Stop-Service command. Let's do it with a service named "Spooler".

#--Get the Names of Running Services --#  
Stop-Service "Spooler"  

#56 : Compression with Powershell or Creating zip file with Powershell

File compression is not at all straight forward in Powershell. Using Windows compression is error-prone. It gives unexpected results and fails silently. The problem is basically with file size larger than 3 Gigs. If file-size is larger than 3 Gigs, Windows 2003 based server will not complete the task and fail silently. If you have delivered it for any critical task, you will find yourself in a real trouble.

Windows native compression method:

Below is the code which can be used : 

#------------------------------------------------------------------------------------------
#     Script : compress_file.ps1
#     Author : Som DT.
#    Purpose : Compress file script
#------------------------------------------------------------------------------------------

function compress-file ([string]$file, [string]$zipfilename)
{
 echo "Compressing [$file] to [$zipfilename]."
 
 if(-not (test-path($zipfilename)))
 {
  set-content $zipfilename ("PK" + [char]5 + [char]6 + ("$([char]0)" * 18))
  (dir $zipfilename).IsReadOnly = $false
  
  $shellApplication = new-object -com shell.application
  $zipPackage = $shellApplication.NameSpace($zipfilename)
  
  $zipPackage.CopyHere($file)
 
  #--Add some delay to wait -# 
  do {
   $zipCount = $zipPackage.Items().count
   echo "Waiting for compression to complete ..."
   Start-sleep -Seconds 1
  }
  while ($zippackage.Items().count -lt 1)
 
  echo "Finished zipping successfully"
 
 }
} 

#------------------------------------------------------------------------------------------

#--Starting comressing the file --# 
compress-file "D:\new.txt" "D:\new.zip"

#------------------------------------------------------------------------------------------

This works fine with Windows 2008 and above operating system. But still there are two problems -

1. CopyHere function is a real mess. It does not stick to the point where this function is called. It moves ahead, so you need to put a logic to make the script wait till compression is over.
2. This is hard to predict if compression failed. Script might run infinite in some cases. This is genuine problem and above script is not recommended.

#55 : Process Handling with Powershell

You can get list of running Processes with command like Get-Process. 

Below command be used to get Names of all processes running - 

CODE: 
 #==[ Get List of Processes Running ]==# Get-Process | Format-Table ProcessName

Stop a Process if the process is running :

I am not sure why somebody would need this. But anyways, just for learning - you can use below -

Below statement would stop each occurence of Notepad running.

CODE:
 #==[ Find if Notepad is running, if running - Stop it! ]==# Get-Process | Where {$_.ProcessName -eq "Notepad"} | Stop-process

Find the Process which is using maximum CPU :

Yeah, this might be a good example.

CODE:
 #==[ Find the Process Name with Maximum CPU ]==# Get-Process | Sort-Object "CPU" | Select-Object -Last 1

Find the Process which is using minumum CPU :

Below code is just one word different from above. Select-Object -First 1.

CODE:
#==[ Find the Process Name with Maximum CPU ]==# Get-Process | Sort-Object "CPU" | Select-Object -First 1

Find the Process with more than one Occurences : 

CODE:
#==[ Find the Process with more than one occurence ]==# Get-Process | Group-Object ProcessName | Where-Object { $_.Count -gt 1 }

#54 : How to upload file to SharePoint with Powershell?

Uploading to SharePoint is fairly simple with the below snippet. I am sharing the Gist script which might be useful for you. The only carefulness required here is to make sure that credentials are properly granted to the executing account.

	#--------------------------------------------------------------------------------------------
	#-- Script to upload file to sharepoint
	#--------------------------------------------------------------------------------------------
	function Upload-To-SharePoint([string]$FileName , [string]$SharePointLocation)
	{
	$wc=new-object System.Net.WebClient
	#--Use the default credential, make sure the executing account must have access.
	$wc.UseDefaultCredentials = $true
	#--Upload the file --#
	$wc.UploadFile($SharePointLocation, "PUT", $FileName )
	$wc.dispose()
	}
	Upload-To-SharePoint "C:\temp\abcx.txt" "http://sp.fool.com/test/abcx.txt"

view raw upload_to_sp.ps1 hosted with ❤ by GitHub

Try above and let me know how it goes. Please update me in case of any issues.

Thanks for reading this article!

#53 : Error Handling with $Error

$Error is an interesting array which records all error messages from current session. It can be cleared also and looked up for presence of any error message in last command. In most of the cases, you may want to set it clear and then look for $error[0].

Make sure when you check the $error.count right after you run the statement which you think as problematic statement.

Code:

$error.clear()

copy-item “c:\temp\abc.txt” “d:\abc.txt”

if ( $error.count -ne 0 )

{

echo “An error occurred in copy operation. Please review the message below:”

echo $error[0]

}

When you run the above in console and abc.txt does not exist, you will get an error message in red color and then from echo part as well. You might be thinking why we need this if error message is there in console. The reason behind using is that if you run the script from a scheduler and want output to be there in log file, this approach can help you a lot.

Other better methods of catching exceptions are:

Try.. Catch

Trap

We will discuss about them in next article. Test this approach and let know your responses.

Enjoy!

#52 : How to display fields of a Delimited file?

I think that delimited file might be a confusing term, but I simply mean with a file which has fields into it separated by a delimiter such as pipe(|) or tilde (~). In course of programming, you may see several such situations where you will have to read a CSV or something similar to that.

Okay, let's open Powershell command prompt and check the below code which will demonstrate you how to do it. Below is the piece of code which you can use :

	#-- Create a file with delimeter as pipe --#
	echo "Jack|John|Jim" > .\file_tmp.txt
	echo "Som|Ravi|Checking" >> .\file_tmp.txt
	echo "F1|F2|F3" >> .\file_tmp.txt
	#--Display field-1 --#
	cat .\file_tmp.txt | foreach{ $_.split("|")[0] }
	#--Display field-2 --#
	cat .\file_tmp.txt | foreach{ $_.split("|")[1] }

view raw read_delimited.ps1 hosted with ❤ by GitHub

With the above example, you can see that first field will be 0, second field will be 1 and so on.

Hope, you enjoyed reading!

#51 : How to check if Port if open or not?

This is fairly simply to test if certain host is able to connect to a port or not with Telnet. Some of the new SQL Server installation do not have Telnet installed. In those cases, we can use Powershell to test is the port.


I will use System.Net.Sockets.TcpClient class to accomplish it. Below is the simple statement to get the Port status.

$tcp = New-Object System.Net.Sockets.TcpClient

$tcp.connect(“HOST1″ , 1234 )


The above results as blank where connection is successful, otherwise, you get an error message.

#50 : How to check with Powershell that URL is responsive or not?

You can write script in Powershell to check the status of certain URL (Uniform Resource Locator). In some cases, we may need to monitor if certain intranet application is working or not. Sometimes SQL Server Reporting services are required to be checked if URL is responsive.

	function Check-URL([string]$URL)
	{
	#--Trapping any issue during execution --#
	trap { return "NOT_OK" }
	$HTTP_Request = [System.Net.WebRequest]::Create("${URL}") $HTTP_Request.UseDefaultCredentials = $true
	$HTTP_Response = $HTTP_Request.GetResponse() $HTTP_Status = [int]$HTTP_Response.StatusCode
	#--Status must be 200 --#
	if ( $HTTP_Status -eq 200 )
	{
	return "OK"
	}
	else
	{
	return "NOT_OK"
	}
	}

view raw check_url.ps1 hosted with ❤ by GitHub

To use it, you may call the function like below :

Check-URL "http://yourapplicationname.com"


That’s it! You are done and nothing more is required.