#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.

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 :


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

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.



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.