Wednesday, July 22, 2015

Executing Encoded Commands in Powershell

Today, we will take a look how we can avoid displaying sensitive information to users in form of text. This approach does not apply to a hacker or cracker, but it will just avoid flashing secure info to common users.

I had a requirement to connect SQL Server instance using SQL Login. If you connect to any server using Windows authentication, passwords are never revealed, but using SQL Logins in your script reveals everything as plain text. Although the login to used will have limited permission on SQL Server, but still disaplying passwords as plain text was going difficult to me. In fact, it applies to any case, we should never put passwords directly to the script as plain text.
Now, problem is clear that we will go with encryption route or we will do something else.
In Powershell, we have feature to run commands as Base-64 string. We can use this feature since Powershell v1.0.

Please follow below steps to achieve it :

1. Convert the command line to a Base-64 string.

$CodeLine = 'Write-Host "Hidden secret code!"'
$UniCodeLine = [System.Text.Encoding]::Unicode.GetBytes($CodeLine) 
$HexCodeLine = [Convert]::ToBase64String($UniCodeLine) 
echo "$HexCodeLine"

Line #1 : It will get the command to be executed
Line #2 : It will convert your code into Unicode string
Line #3 : It will convert Unicode string to Base-64 string
Line #5 : It will display the string. Now, you can copy paste the output to your actual code.

2. Run the command as below :

& sqlcmd -E -S ABC\ABC -U "User1" -P $Passwd

Line #1 : Get the output from encoded command line using -EncodedCommand parameter. Store results to $Passwd. 
Line #2 : Run the sqlcmd to SQL Server instance and pass password as $Passwd
I repeat, this does not solve the security problem completely as Base-64 string can be converted to string, but it is secure upto certain extent.

Thanks for reading.

Saturday, July 11, 2015

Book Review : June 2015 Powershell Tips

Get all the tips provided in June 2015 in a form of book. I have uploaded that at Scribd so that it could reach all of you.

I will create a monthly release in a form of book so that all you can at one place.

Hope you would like this effort. Thanks to everyone who suggested writing book on Powershell. This is beginning of my dream project....

Sunday, June 14, 2015

#86 : Mastering Powershell

A free ebook of powershell is 'Mastering Powershell' written by Dr. Tobias. The book is written in a very simple language and gives a full glimpse of Powershell Programming.


(Download link might change anytime, please search in google/bing if required)

I like the way of expression of this book. But I feel there are some portions where you feel like the book is unorganized and it looks like writer has so much to say but still so much is left over. It covers most of the portions of Powershell programming especially if you have started learning Powershell.

This book can also help you learning scripting skills. Whether you at advaced, intemediate or novice level of scripting, this book might be very useful to you. As this book is free of cost, you don't have to bother, do have a look.

Powershell-Tips Rating : 3/5

Thursday, June 11, 2015

#85 : Find ODBC Drivers installed on the host with Powershell

ODBC Drivers are generally installed on servers to allow communication with different database engines. You can use Powershell to get the list of ODBC drivers installed on the host.
You need to run Get-ODBCDriver cmdlet which will give you all the details.


Name      : Microsoft Access-Treiber (*.mdb)
Platform  : 32-bit
Attribute : {Driver, APILevel, FileExtns, FileUsage...}
Name      : Driver do Microsoft Paradox (*.db )
Platform  : 32-bit
Attribute : {Driver, APILevel, FileExtns, FileUsage...}
Name      : Driver do Microsoft Excel(*.xls)
Platform  : 32-bit


The output might not be pleasant. If you think so, you can try the line below:
Get-OdbcDriver | ft Name

Microsoft Access-Treiber (*.mdb)                                                                                                                                          
Driver do Microsoft Paradox (*.db )                                                                                                                                       
Driver do Microsoft Excel(*.xls)                                                                                                                                          
Microsoft Text Driver (*.txt; *.csv)                                                                                                                                      
Driver da Microsoft para arquivos texto (*.txt; *.csv)                                                                                                                    

Thanks for reading!


Wednesday, June 10, 2015

#84 : Difference between redirection operator and Out-File

Redirection operator can be used most of the scripting languages. If you use single redirection operator (>), it means create a file and write or overwrite the file with the string.
In Powershell, you can use them, but we have advantage of Out-File cmdlet. Let us try to see what is major difference. The difference is that redirection operator will always go with unicode character.

For example:
echo "Line - 1" > c:\temp\file.ps1 
echo "Line - 2" >> c:\temp\file.ps1 
It will generate a file with Unicode character set.

Repeating the same with Out-File :
echo "Line - 1" | Out-File c:\temp\file.ps1 -Encoding "ASCII" 
echo "Line - 1" | Out-File c:\temp\file.ps1 -Encoding "ASCII" -Append 
It will generate a file with ASCII character set.

Mind that we have use -Append in the second line. This will allow appending. Check yourself that what will happen if you don't use -Append switch.

There are below character sets possible with -Encoding option:

If you don't mention Encoding switch, Unicode will be taken as default.

Don't mix character set if you don't want to see unexpected graphics in file. For example, if you run below lines, you will understand what I mean.

echo "Som" > file.txt
echo "Som" | out-file .\file.txt -Encoding "ASCII" -Append

When you open file.txt, you would see first line written correctly and second line written in chinese. I have faced such situations where output is coming from multiple command line apps like osql and sqlplus and mixing them together created a real mess. The difficult part is that you can barely troubleshoot where problem is coming from. Try to stick to ASCII in my opinion.

Thanks for reading this article!

Tuesday, June 9, 2015

#83 : Save File Dialog with Powershell

This is continuation of previous article. In fact, there is no difference in save dialog and open file dialog. The difference if that here you can get message of replacing file. Save File dialog does not save anything, you have to save it yourself.

Below is the code for the same :

#-- Function Declaration 

function Save-File([string] $initialDirectory ) 


    [System.Reflection.Assembly]::LoadWithPartialName("") | Out-Null

    $OpenFileDialog = New-Object System.Windows.Forms.SaveFileDialog
    $OpenFileDialog.initialDirectory = $initialDirectory
    $OpenFileDialog.filter = "All files (*.*)| *.*"
    $OpenFileDialog.ShowDialog() |  Out-Null

    return $OpenFileDialog.filename

#Usage : 

$File=Save-File "C:\temp\tips" 

if ( $File -ne "" ) 
    echo "You choose FileName: $File" 
    echo "No File was chosen"

Hope this article was useful for you. Thanks for reading!

Monday, June 8, 2015

#82 : Open File Dialog with Powershell

This is interesting that all GUI operations can be done with Powershell without any problem. Basically, most of the resources available to any .NET application applied to Powershell. This make Powershell more vast and limitless (in my opinion).

So, let's see the code which will be used to open a dialog. The requirement is simple, the script will show a dialog to open any file, if user has open a file then script will display file name or show if user has cancelled the dialog. I would suggest not to use this script for any scheduling or automated job as this will stuck your job till host reboot (I think).

#-- Function Declaration 

function Open-File([string] $initialDirectory ) 


    [System.Reflection.Assembly]::LoadWithPartialName("") | Out-Null

    $OpenFileDialog = New-Object System.Windows.Forms.OpenFileDialog
    $OpenFileDialog.initialDirectory = $initialDirectory
    $OpenFileDialog.filter = "All files (*.*)| *.*"
    $OpenFileDialog.ShowDialog() |  Out-Null

    return $OpenFileDialog.filename

#Usage : 

$File=Open-File "C:\temp\tips" 

if ( $File -ne "" ) 
    echo "You choose FileName: $File" 
    echo "No File was chosen"


Friday, June 5, 2015

#81 : String handling with Substring function

Substring function is available in nearly all the programming and scripting languages. This function is best suited for traversing each character or set of characters of string based on length and rank of character to start with. In fact, you can do chopping of certain string from the complete string. I have used substring a lot with T-SQL Programming. Let us take a look how substring can be used in Powershell.

1. Chop last three characters from a string using substring. If string is less than 3 character, return whole string.

#--Function to find last three characters of a string --# 

function chop-last-three([string]$str)
 if ( $str.length -ge 3 ) 
  echo $str.substring($str.length - 3, 3) 
  echo $str

chop-last-three "ABCDEFGHIJKLMNOP" 

2. Chop first three characters of a string using substring. If string is less than 5 characters, return whole string.

#--Function to find first three characters of a string --# 

function chop-first-three([string]$str)
 if ( $str.length -ge 3 ) 
  echo $str.substring(0, 3) 
  echo $str

chop-first-three "ABCDEFGHIJKLMNOP"

That's all for today. You may ask questions if you want.
Thanks for reading this article!

Thursday, June 4, 2015

#80 : Find difference in two dates with Powershell?

Sometimes we need to get this information like total time taken by script or total time a program ran. This is quite simple to get the difference between two dates if the data type is set to DateTime.

Below is a sample script for the same :
This script will show the difference in Seconds, Hours and Minutes. You can also go to even deeper such as milliseconds.

Function Get-DateDifference( [DateTime]$FromDate, [DateTime] $ToDate , [String]$OutputType ) 
 if ( $FromDate -gt $ToDate ) 
  echo "Error: [From date] should not be greater than [To Date]."
  Switch ( $OutputType )
   "Seconds"  {  $Output=$($ToDate - $FromDate).TotalSeconds } 
   "Hours"  {  $Output=$($ToDate - $FromDate).TotalHours } 
   "Minutes"  {  $Output=$($ToDate - $FromDate).TotalMinutes } 
  if ( $Output -ne -1 ) 
   echo "$Output $OutputType"

$Date1=Read-Host "Enter the From Date"
$Date2=Read-Host "Enter the To Date"

Get-DateDifference -FromDate $Date1 -ToDate $Date2 -OutputType 'Seconds'

Hope this was helpful tip.
Enjoy learning Powershell!

Wednesday, June 3, 2015

#79 : Find Free Memory Status on a Computer

Today's tip is about getting the free space in memory on a computer. This can easily done with WMI. Let's take a look in a one-liner.

Get-WmiObject win32_operatingsystem  | select csname, FreePhysicalMemory, FreeSpaceInPagingFiles, FreeVirtualMemory

This can play a good role when you are writing a script which might consume too much of memory, you can plan the execution when memory usage is normal.


Tuesday, June 2, 2015

#78 : Find who is logged in list of machines using Powershell

Recently, there were list of workstations assigned to our team. As there are many people working on those workstations, it was difficult to find who is logged in on which machine without asking. Finally, I ended with below script which can tell the same easily.

Below are steps for testing the script :

1. Create a directory, say WhoLoggedIn.
2. Create a file with name list.txt.
3. Use Notepad and edit list.txt and add all servers with one line for each server.
4. Create a Powershell script say WhoLoggedIn.ps1

Paste the code from below :

$SCRIPT_LOC=Split-Path -parent ${Script:THIS_SCRIPT_NAME}

$VALS=gc $SCRIPT_LOC\List.txt | where { $_ -match "^[1-9]" } | foreach { $_ + $(& query user /server:"$_")  }

$FINAL_VAL=$VALS | foreach { $_.replace("USERNAME              SESSIONNAME        ID  STATE   IDLE TIME  LOGON TIME  ","") } | foreach { $_.replace("  ", " ") }


Run the same and you will see results in proper format. I made a script which will log the same info into SQL Server using the same script. Choice is all yours what you do with it. My job is to give direction ...


Monday, June 1, 2015

#77 : How to get directory size using Powershell?

A quick tip, to get the size of the folder, you can use below line :

"{0:N2}" -f ($(ls "c:\temp" -recurse | Measure-Object -property length -sum).sum /1MB)  + " MB"

Replace MB with KB if you want in KB.

"{0:N2}" -f ($(ls "c:\temp" -recurse | Measure-Object -property length -sum).sum /1MB)  + " MB"
Hope this was easy, right?

Saturday, May 30, 2015

#76 : Using Choice Box in Powershell

I got an interesting requirement to allow users choose from a list. The list will be dynamic and will display all items of the file in a drop down list.

1. Create an empty file called list.txt.
2. Add below entries to the file:
3. Create script and paste below code:

function showDialog([string]$file) 
 [void] [System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms")
 [void] [System.Reflection.Assembly]::LoadWithPartialName("System.Drawing") 

 $objForm = New-Object System.Windows.Forms.Form 
 $objForm.Text = "Choice-Box"
 $objForm.Size = New-Object System.Drawing.Size(300,200) 
 $objForm.StartPosition = "CenterScreen"

 $objForm.KeyPreview = $True
 $objForm.Add_KeyDown({if ($_.KeyCode -eq "Enter") 
 $objForm.Add_KeyDown({if ($_.KeyCode -eq "Escape") 

 $OKButton = New-Object System.Windows.Forms.Button
 $OKButton.Location = New-Object System.Drawing.Size(75,120)
 $OKButton.Size = New-Object System.Drawing.Size(75,23)
 $OKButton.Text = "OK"

 $CancelButton = New-Object System.Windows.Forms.Button
 $CancelButton.Location = New-Object System.Drawing.Size(150,120)
 $CancelButton.Size = New-Object System.Drawing.Size(75,23)
 $CancelButton.Text = "Cancel"

 $objLabel = New-Object System.Windows.Forms.Label
 $objLabel.Location = New-Object System.Drawing.Size(10,20) 
 $objLabel.Size = New-Object System.Drawing.Size(280,20) 
 $objLabel.Text = "Please choose any of the below :"

 $objListBox = New-Object System.Windows.Forms.ListBox 
 $objListBox.Location = New-Object System.Drawing.Size(10,40) 
 $objListBox.Size = New-Object System.Drawing.Size(260,20) 
 $objListBox.Height = 80

 $items = gc $file | where { $_ -ne "" }
 foreach ( $item in $items) 
  [void] $objListBox.Items.Add($item)


 $objForm.Topmost = $True

 [void] $objForm.ShowDialog()



showDialog .\list.txt 

Once your run it, you will get a popup like below:

That's it! You can see the value of $x can be used further in any place of your code.

Hope you liked this article. Send your comments and suggestions to me...

Friday, May 29, 2015

#75 : How to run Powershell scripts from network location (UNC Path)?

In most of the cases, people ask questions if they can run the Powershell scripts from network location which is shared on multiple machines.

At first sight,  I would not suggest to do it as this is not recommended to run unsigned scripts, but requirement is always a requirement and I have seen people debating in a long mail chain when I say that something is not possible. Sometimes whole group is fighting with me one after another and using google search and all options to make me accept. Believe me, I don't believe in too much argument on any topic, but still I don't like to accept something which cannot be done or something which cannot be done perfectly.

I believe in correct usage of any technology, arguments and debates are other areas are not my areas of expertise. Anyways, for those who have been forced to do something and for those who has to do something because it has to be done.

You can use -ExecutionPolicy "Bypass" in the command line. Something like this :

For example, if you have script located at abc server's c:\abc.ps1, you can run statement like this:
Powershell.exe -ExecutionPolicy bypass \\abc\c$\abc.ps1

This will not ask any questions or input. Keep all such shared script at central location, create a share or map a drive, do whatever suits your environment.

Hope, you liked this article. Please do sent your suggestions and advises.


Thursday, May 28, 2015

#74 : How to get IP address from Host Name using Powershell?

There are multiple ways to get it. Some will suggest using ping.exe also. But below is the tested and clean approach to do it.

Replace your host name below and test it at your end :

function GetIPAddress([string] $HostName) 
 trap {
  return 0 
 return $IP_ADDRESS 


echo $(GetIPAddress "ABCHOSTNAME")

Hope you like this tip, do write your comments and suggestions.
Life is good!

Wednesday, May 27, 2015

#73 : How to list all files in current directory and subdirectories using Powershell?

Sometimes, we need to work with numerous files located in certain location. We might need to perform some action or read some lines with those files. When the requirement is all about reading all files in directory and sub-directories, we can simply use ls in Powershell. (Run Get-Alias ls to know which cmdlet is called in background, skipping this so that you learn something more, those who know this can avoid it ;))

Every experiment starts with a small building block, so let's run ls with recursion :

ls -recurse

You see multiple directories, but this is not useful as you don't have much option to perform any task on them.

    Directory: B:\tst

Mode                LastWriteTime     Length Name
----                -------------     ------ ----
d----         5/26/2015   6:08 AM            A
d----         5/26/2015   6:09 AM            B
-a---         5/26/2015   6:09 AM          0 file4.txt
-a---         5/26/2015   6:09 AM          0 file5.txt

    Directory: B:\tst\A

Mode                LastWriteTime     Length Name
----                -------------     ------ ----
-a---         5/26/2015   6:08 AM          0 file1.txt

    Directory: B:\tst\B

Mode                LastWriteTime     Length Name
----                -------------     ------ ----
-a---         5/26/2015   6:09 AM          0 file2.txt
-a---         5/26/2015   6:09 AM          0 file3.txt

Now, run the same command with little twist and then you will be able to much from output :

ls -Recurse | foreach { $_.FullName  }

The result is full list of files and you can perform more precise operations on them.

But, if you see the output above, you can see that directories are also there,  but your requirement might be only related with files or directories. So, let's get separate results for them.

List all Files recursively :
ls -Recurse | where { $_.PSIsContainer -eq $false } |  foreach { $_.FullName }


List all directories recursively :
ls -Recurse | where { $_.PSIsContainer -eq $true } |  foreach { $_.FullName }


So, this concludes today's tip. Always start with small example and keep trying till you get the results. Send your mails and comments and let me know if you have questions.



Tuesday, May 26, 2015

#72 : How to run a program with different credential in Powershell?

There are multiple ways to achieve it. I will suggest using a Get-Credential cmdlet to get the credential and use start-process to run the script or program of your choice. You can also use invoke-command for this purpose to run commands on remote machines of you network, but the approach is not advisable when WinRM is not enabled and I don't want to make it complicated.

So, let's take a look on the script and then we will see how it runs :

Start-Process c:\windows\notepad.exe -Credential $c

When you run the two lines above, you see a popup which asks your credentials :

Enter credential and then you will see the notepad.exe running with that credential.

This may help you if you are making a program which requires special credentials at some point and user can enter the login and password. Good for any interactive script only. We can also save the credentials and use that. We will see that in next article, till then enjoy!

Wednesday, May 13, 2015

#71 : Pause a Powershell Script

Sometimes, we need to pause scripting where any asynchronous operation is in progress or we have to wait for some file to appear. In all such cases, Start-Sleep can help you achieve that. Let's take a look into syntax and then we will use the same in our example :

Start-Sleep -Milliseconds <int> [<CommonParameters>]
Start-Sleep [-Seconds] <int> [<CommonParameters>]

Pause the script for 5 seconds:
Start-Sleep -Seconds 5


Tuesday, May 12, 2015

#70 : Show statistical information using Powershell

Today' we will talk about Measure-Object cmdlet which helps you peforming average, max, min and other operations. Before we perform some tests, we need to create a file with some data. My file is as follows :


Save the above as file.csv. You can use below command to see the statistics :

import-csv C:\engg.demo\file.csv | measure-object age -ave -max -min

Count    : 3
Average  : 38.3333333333333
Sum      :
Maximum  : 60
Minimum  : 25
Property : age

In measure-object, we mentioned the column name against which you want to run certain operation. After that, you can mention from below :
-ave : Average
-min : Min
-max : Max
-sum : Sum

Enjoy scripting!!


Sunday, May 10, 2015

#69 : Formatted output from a number with decimal using Powershell

Sometimes, we need to produce formatted output from a number with decimals. If the output has to be in a designated formatted such as till two places after decimals, we can simply format them using below. It helps specifying the precision correctly :

Below is the function to help you in setting precision till whatever places of decimal you want. If number is limited to only two digits after decimal, it will add zero as suffix, so this might help in some other cases also where you want to show result in money.

Please check the Gist below :

There are several other ways to do it, but I found it more flexible.
Enjoy scripting !!