Monday, August 31, 2015

PowerShell - The Call Operator "&"

The call operator "&" is a powerful feature in PowerShell. If you place this operator in front of a string, or a string variable, the string will be interpreted as a command and executed just as if you had input it directly into the console. Let put a command (Dir) in a variable and if you output the contents of the variable, only string will be output:

$myCommand = "Dir"

If you type the call operator "&" in front of it, the command will be executed:
& $myCommand

The call operator will not run an entire instruction line but only single command. If you
had also specified arguments for the command, you will get an error:
$myCommand = "Dir C:\"
& $

Event if you put an space in command name, you will get same error. For example:
$myCommand = "Dir "
& $
 Also generates the error.

Another way to assign a command to a variable is using Get-Command cmdlet, and then use the same call operator to invoke this command.
$myCommand = Get-Command Dir
& $

But keep in mind that & $myCommand calls the actual command in $myCommand. You may specify any arguments after it, but you can't put the arguments directly in $command because the call operator always executes only one single command without arguments. So following call will work:
$myCommand = "Dir"
& $
myCommand "c:\"

Sunday, August 30, 2015

PowerShell - Workflow

Workflow functionality with PowerShell gives you the benefits of automation capabilities.  For example, you need to perform a long-running task that combines multiple steps in a sequence, wrokflows will do the job.

For creating and calling, it is same like a function, lets define our first workflow
workflow Workflow1 {
"My workflow is started"
"Task1 is completed"
"Task2 will take some time - 5 seconds"
Start-Sleep -Seconds 5
"Task3 completed"
"Workflow completed"

We can run the workflow just like any other PowerShell function. It looks and acts just like a normal function. Simply call workflow as:

Output of our workflow is:
My workflow is started
Task1 is completed
Task2 will take some time - 5 seconds
Task3 completed
Workflow completed

We can use the PowerShell Get-Command to get details about the workflow we created.
Get-Command Workflow1

Adding -syntax parameter, will display the syntax tempalte for calling workflow.
Workflow1 [<WorkflowCommonParameters>] [<CommonParameters>]

If you have a large no of activities within a workflow, and at some point we want to save a particular state of the workflow. For this we need to simply call this cmdlet within the workflow:

Workflow is an extensive topic in PowerShell. Here I created a simplest workflow just for starting purpose. Hopefully will try to post more on this topic as I get time. If you want to read more on this topic please tell me by your valuable comments, that will help for future posts.

PowerShell - Create / Import / Remove Module

You can write PowerShell script and save in separate file for reuse in different situations. Lets first create the module.

Create a file with extension .psm1, and place a sample function like this:

function Get-TestMessage()
    Write-Host("Get-TestMessage function is involed")

Now we have a TestModule.psm1 file with one function Get-TestMessage(). Place this module either in one of the PowerShell default module folders that you can query by this special variable $env:PSModulePath. Or also you can place anywhere on your disk, but in this case you have to define the complete file path for the module while importing.

Now, lets create another module, lets say WorkingModule.psm1. Here we want to use a function from the TestModule. We have to import TestModule by this cmdlet:
Import-Module "C:\PATH_TO_YOUR_MODULES_FOLDER\TestModulePSM.psm1"

And now we can simply call our function and it works.

You can find the available functions defined within a module by passing the module name in -module parameter for Get-Command cmdlet.
Get-Command -Module TestModule

In our case it will only return one function.

Once you finished work with a module, you can remove that module by simply call this cmdlet.
Remove-Module TestModulePSM

If this article helps you then please post your valuable comments.

PowerShell - Strings

We can define a literal string surrounded with single quotes and that does not interpret variable expansion or escape characters.
$myString = 'Hello World'

Also we have strings with double quotes called as expanding strings, here PowerShelll expands variable names (such as $myVar) and escape sequences (such as `n) with their values.
$myString = "Hello World"

We have to place two of that string’s quote characters together to add the quote character itself

$myString = "This string includes ""double quotes"" because it combined quote characters."
$myString = 'This string includes ''single quotes'' because it combined quote characters.'

Lets create a variable that holds text with newlines or other explicit formatting.
$myString = @"
This is the first line.
            Now expand it to second line.
    Still continues with string text including new lines and tabs.

PowerShell uses a backtick (`) character as escape sequences. In this example `n will start a new line.
$myString = "My heading text`n----------------"

Strings dynamically accepts another variable's value.
$myVar = "PowerShell"
$myString = "We are working with $myVar"
We are working with PowerShell

If you want to prevent PowerShell from interpreting special characters or variable names inside a string. A nonexpanding string uses the single quote character around its text. Now it will not place the variable's content but place the same as we typed.
$myVar = "PowerShell"
$myString = 'I have just defined a variable named $myVar'

You can repeat a string multiple times. Lets say, we want to append "0" to some other value. So for this just put the character inside quotes and multiply it with the number of times you want to repeat. For example, here I want to append ten 0s on the left side with my value "1".
$('0' * 10) + "1"

Like we have string.Format() method in C#, we can format our strings in the same way also. We have to use -f after at the end of string quotes, then put the variables we want to set in placeholders. For example:
$var1= "Strings"
$var2= "PowerShell"
$myString = "We are working with {0} in {1}" -f $var1,$var2

Note that, same like C#, first variable in the sequence will be placed in the first placeholder, second variable in second index and so on.

Saturday, August 29, 2015

PowerShell - Event Log

You can use the Get-Eventlog cmdlet to access log entries. You can use this cmdlet for two purposes, one is to list the event logs, second you can use it to extract all the events within a specific event log.

-List paramter is used with this cmdlet to list down only event logs.
Get-EventLog -List

If you wanted to get a display of all the entries in the System log, you can just put the log name ('System' in this case) with cmdlet:
Get-EventLog System

But depending on the number of records in the event log, you might get a long scrolling in shell. So its better to use the PowerShell filters. Use Where-Object to pass the information retrieved by Get-Eventlog through the pipeline while allowing only those entries through that meet your criteria. For example the following command will list down all the event in the system events log, that are recorded today.
Get-Eventlog System | Where-Object {($_.TimeWritten).Date -eq (Get-Date).Date}

We also have the -newest parameter. You can simply get only the last x number of events recorded in the log. For example, this command retrieves the last three events written to the System event log:
Get-EventLog System -newest 3

Or you can put the -Format-List parameter for better view:
Get-EventLog System -newest 3 | Format-List

You can use the methods of the .NET framework, to make event entries:
[Diagnostics.EventLog]::WriteEntry("Application","My test event","Information")

To see if this event is successfully recorded, you can check the Event Viewer:

Or you can use the same -newset parameter with cmdlet to view this test event.
Get-EventLog Application -newest 3 | Format-List

PowerShell - Services

PowerShell provides good support for managing windows services. You can list down, start, stop the services from shell. Lets start:

This command will give you the avialable cmdlets for managing services:
Get-Command *service* | Where-Object {$_.CommandType -eq 'cmdlet'}


List all services beginning with "sql":
Get-service sql*

This command will give you only running services beginning with "sql":
Get-Service sql* | Where-Object { $_.status -eq 'Running' }

Lets try to start 'SQL Server Browser' service
Get-Service | Where-Object { $_.DisplayName -eq 'SQL Server Browser' } | Start-Service

You need administrator rights to run this command.

In-case you get this error:
SQL Server Browser (SQLBrowser)' cannot be started  due to the following error: Cannot start service SQLBrowser on computer '.'.
Please refer to this post:

All these cmdlets needs you to have administrator rights.
  • New-Service                                                                          
  • Restart-Service                                                                      
  • Resume-Service                                                                       
  • Set-Service                                                                          
  • Start-Service                                                                        
  • Stop-Service                                                                         
  • Suspend-Service                                                                      

PowerShell - Start-Service : Service 'x' cannot be started due to the following error: Cannot open x service on computer '.'

If you want to start a service through PowerShell you might get this error:


Start-Service : Service 'x' cannot be started due to the following error: Cannot open
x service on computer '.'.
At line:1 char:72
+ ... here-Object {$_.DisplayName -eq "x"} | Start-Service
+                                                             ~~~~~~~~~~~~~
    + CategoryInfo          : OpenError: (System.ServiceProcess.ServiceController:ServiceController) [Start-Service],
    + FullyQualifiedErrorId : CouldNotStartService,Microsoft.PowerShell.Commands.StartServiceCommand

We get this error because we are not running the PowerShell with Administrative privileges. Just right click on PowerShell and then click 'Run as administrator'. You will get your cmdlet worked.

Similarly you might have to do the same thing for following service commands:
  • New-Service   
  • Restart-Service
  • Resume-Service
  • Set-Service   
  • Start-Service 
  • Stop-Service  
  • Suspend-Service

Thursday, August 27, 2015

PowerShell - Processes

Processes can be controlled directly by the objects and methods of the .NET framework.

You can launch any executable program in a directory named in the PATH environment variable simply by typing its name:


PowerShell can't directly access these processes once they've started. Direct control of a process is only possible if you start the process using the Start() .NET method, which enables you to check whether a process still responds or is terminated. You can also stop running process:
$process = [System.Diagnostics.Process]::Start("notepad")

To view all properties, send the result to a formatting cmdlet like Format-List and append with an asterisk:
Get-Process powershell | Format-List *

If you want to rectreive processes that have been running for less than 60 minutes, you could find them like this:
Get-Process | Where-Object { $_.StartTime -gt (Get-Date).AddMinutes(-60) } | Format-Table

Handles  NPM(K)    PM(K)      WS(K) VM(M)   CPU(s)     Id ProcessName               
-------  ------    -----      ----- -----   ------     -- -----------               
    129      11     4656      11516 ...40     0.06   6416 conhost                   
    161      12     2212      14004 ...29     0.33   6440 InstallAgent              
    128      10     1644      10068 ...54     0.81   8068 notepad                   
    550      30    80468      92368 ...91     1.41   4852 powershell                

If you want to display time elapsed since the process started, then you can add a property to Format-Table with expression like this:

Get-Process | Where-Object { $_.StartTime -gt (Get-Date).AddMinutes(-60) } | Format-Table Name, Id, StartTime, @{expression={ [int](New-TimeSpan $_.StartTime (get-date) ).TotalMinutes }; label="Minutes" } -autosize

Name             Id         StartTime                         Minutes
----                 --          ---------                             -------
conhost          6416    27-Aug-15 2:57:24 PM         5
InstallAgent    6440    27-Aug-15 2:52:34 PM        10
notepad         8068    27-Aug-15 2:57:33 PM          5
powershell     4852    27-Aug-15 2:57:24 PM          5

Since Get-Process is an array you can count the rows easily.
 @(Get-Process notepad).Count

Each Process object contains methods and properties, some of the properties may be read as well as modified, and methods can be executed like commands. For example, you can set the priority of a process. This statement lowers the priority of all Notepads:

Get-Process notepad | ForEach-Object { $_.PriorityClass = "BelowNormal" }

You can stop a process by specifing its name.
Stop-Process -name Notepad


PowerShell - Number Conversions

To convert a decimal number to its binary representation, supply a base of 2 to the [Convert]:: ToString() method:

[Convert]::ToString(16, 2)

Similarly for the reverse purpose, to convert a binary number back into its decimal representation, supply a base of 2 to the [Convert]::ToInt32() method:

[Convert]::ToInt32("10000", 2)

To convert a decimal number to its octal representation, supply a base of 8 to the [Convert]::ToString() method:

[Convert]::ToString(16, 8)

And for the reverse purpose, to convert an octal number back into its decimal representation, supply a base of 8 to the [Convert]::ToInt32() method:

[Convert]::ToInt32("20", 8)

To convert a decimal number to its hexa-decimal representation, supply a base of 16 to the [Convert]::ToString() method:

[Convert]::ToString(16, 16)

And for the reverse purpose, to convert a hexa-decimal number back into its decimal representation, supply a base of 16 to the [Convert]::ToInt32() method:

[Convert]::ToInt32("10", 16)

To directly enter a hexadecimal number, use the 0x prefix:

$hexNumber = 0x1111


PowerShell - Math

PowerShell supports mathematical tasks primarily through its support for the System.Math class in the .NET Framework.

To explore what methods we have in Math class, lets run this command:
[Math] | Get-Member -Static -MemberType Method

System.MathAbsMethodstatic sbyte Abs(sbyte value), static int16 Abs(int16 value), static int Abs(int value), static long Abs(long value), static float Abs(float value), static double Abs(double value), static decimal Abs(de cimal value)
System.MathAcosMethodstatic double Acos(double d)</ td>
System.MathAsinMethodstatic double Asin(double d)</ td>
System.MathAtanMethodstatic double Atan(double d)</ td>
System.MathAtan2Methodstatic double Atan2(double y, double x)
System.MathBigMulMethodstatic long BigMul(int a, in t b)
System.MathCeilingMethodstatic decimal Ceiling(deci mal d), static double Ceiling(double a)
System.MathCosMethodstatic double Cos(double d)
System.MathCoshMethodstatic double Cosh(double valu e)
System.MathDivRemMethodstatic int DivRem(int a, int b, [ref] int result), static long DivRem(long a, long b, [ref] long result)
System.MathEqualsMethodstatic bool Equals(System.Ob ject objA, System.Object objB)
System.MathExpMethodstatic double Exp(double d)
System.MathFloorMethodstatic decimal Floor(decimal d), static double Floor(double d)
System.MathIEEERemainderMethodstatic double IEEERem ainder(double x, double y)
System.MathLogMethodstatic double Log(double d), st atic double Log(double a, double newBase)
System.MathLog10Methodstatic double Log10(double d)
System.MathMaxMethodstatic sbyte Max(sbyte val1, sb yte val2), static byte Max(byte val1, byte val2), static int16 Max(int16 val1, int16 v al2), static uint16 Max(uint16 val1, uint16 val2), static int Max(int val1, int val2), static uint32 Max(uint32 val1, uint32 val2), static long Max(long val1, long val2), s tatic uint64 Max(uint64 val1, uint64 val2), static float Max(float val1, float val2), static double Max(double val1, double val2), static decimal Max(decimal val1, decimal val2)
System.MathMinMethodstatic sbyte Min(sbyte val1, sb yte val2), static byte Min(byte val1, byte val2), static int16 Min(int16 val1, int16 v al2), static uint16 Min(uint16 val1, uint16 val2), static int Min(int val1, int val2), static uint32 Min(uint32 val1, uint32 val2), static long Min(long val1, long val2), s tatic uint64 Min(uint64 val1, uint64 val2), static float Min(float val1, float val2), static double Min(double val1, double val2), static decimal Min(decimal val1, decimal val2)
System.MathPowMethodstatic double Pow(double x, dou ble y)
System.MathReferenceEqualsMethodstatic bool Referen ceEquals(System.Object objA, System.Object objB)
System.MathRoundMethodstatic double Round(double a) , static double Round(double value, int digits), static double Round(double value, Sys tem.MidpointRounding mode), static double Round(double value, int digits, System.Midpo intRounding mode), static decimal Round(decimal d), static decimal Round(decimal d, in t decimals), static decimal Round(decimal d, System.MidpointRounding mode), static dec imal Round(decimal d, int decimals, System.MidpointRounding mode)
System.MathSignMethodstatic int Sign(sbyte value), static int Sign(int16 value), static int Sign(int value), static int Sign(long value), static int Sign(float value), static int Sign(double value), static int Sign(decimal value)
System.MathSinMethodstatic double Sin(double a)
System.MathSinhMethodstatic double Sinh(double valu e)
System.MathSqrtMethodstatic double Sqrt(double d)</ td>
System.MathTanMethodstatic double Tan(double a)
System.MathTanhMethodstatic double Tanh(double valu e)
System.MathTruncateMethodstatic decimal Truncate(de cimal d), static double Truncate(double d)

And also get properties:
[Math] | Get-Member -Static -MemberType Property

   TypeName: System.Math

Name MemberType Definition            
---- ---------- ----------            
E    Property   static double E {get;}
PI   Property   static double PI {get;}

These methods are as simple to use as it seems. Lets test few methods.

function trunc($number) { [Math]::Truncate($number) }
$result = 3/2
trunc $result

Math.Truncate method will converts the decimal number to integral value. We have placed this method in a script block to define our own PowerShell function and then we called it in PowerShell way.

For absolute value of a number, [Math]::Abs() method:

To find the power of a number, use the [Math]::Pow() method.
[Math]::Pow(5, 2)

For Square root of a number, use the [Math]::Sqrt() method:

To find the sine, cosine, or tangent of an angle (given in radians), use the [Math]::Sin(),
[Math]::Cos(), or [Math]::Tan() method:
[Math]::Sin( [Math]::PI / 2 )

Similarly to find the angle of a sine, cosine, or tangent value, use the [Math]::ASin(), [Math]::ACos(), or [Math]::ATan() methods

Use the Measure-Object cmdlet to measure these statistical properties of a list. Just pipe the objects to the Measure-Object cmdlet:
1..10 | Measure-Object -Average -Sum -Maximum -Minimum
Count    : 10
Average  : 5.5
Sum      : 55
Maximum  : 10
Minimum  : 1
Property :

To measure the numeric features of a specific property in a stream of objects, supply that property name to the -Property parameter of the Measure-Object cmdlet. For example, in a directory with files:
Get-ChildItem | Measure-Object -Property Length -Max -Min -Average -Sum
Count    : 16
Average  : 831.25
Sum      : 13300
Maximum  : 2756
Minimum  : 67
Property : Length

Windows PowerShell Cookbook

Wednesday, August 26, 2015

PowerShell - Create Custom Objects

PowerShell allows you to return structured results from a command so that users can easily sort, group, and filter them. You can create Custom Objects with required properties or methods. Lets explore how to create the objects.

There are different methods you can create Custom Objects.

1. Add-Member cmdlet, a cmdlet that allows us to add properties to an object. In this case we’re adding a NoteProperty, giving our property the name Name and a value that represents the Name property.

$object = New-Object System.Object

$object | Add-Member –MemberType NoteProperty –Name Id –Value 1
$object | Add-Member –MemberType NoteProperty –Name Code –Value "I001"
$object | Add-Member –MemberType NoteProperty –Name Name –Value "Laptop"

Write-Output $object

2. You can assign a hash table to the object to quickly create the objects properties. This can be very useful if you have a list of name/value pairs.

$props = @{
Id = 1
Code = 'I001'
Name = 'Laptop'
$object = new-object psobject -Property $props

Write-Output $object

3. Or a shorter alternative like this:

$object = [PSCustomObject]@{
Id = 1
Code = 'I001'
Name = 'Laptop'

Write-Output $object

4. New-Module with the AsCustomObject parameter creates a custom object.

$object = New-Module -AsCustomObject -ScriptBlock {
Export-ModuleMember -Variable *

Write-Output $object

5. You can using Add-Type to compile C# or other .NET languages, and in this way you can make a real class definition.

Add-Type @"
    using System;
    public class myClass{

        public int Id=0;
        public string Code="";
        public string Name="";


$object = New-Object myClass
$object.Id = 1
$object.Code = "I001"
$object.Name = "Laptop"

Write-Output $object

6. This method is a quick way of gathering data to be manipulated.

$object = "" | select Id, Code, Name
$object.Id = 1
$object.Code = "I001"
$object.Name = "Laptop"

Write-Output $object


PowerShell - Pipeline

One of the fundamental concepts in PowerShell is pipeline. It is a series of commands where the output of one becomes the input of the next, like a production assembly in a factory. It allows you to make refinements on output while being passed between the stages. For this you have to put a pipe '|' sign in-between the commands.

Lets start using it:
Get-Process | Sort-Object Name
Here the output of Get-Process command is become input for Sort-Object command which will sort the items by Name.

Lets add one more command Select-Object to the chain and select only desired columns.
Get-Process | Sort-Object Name | Select-Object Id,Name,Desciption

Add command Group-Object for grouping by name
Get-Process | Sort-Object Name | Select-Object Id,Name,Desciption | Group-Object Name | Select-Object Count, Name
Now add ConvertTo-Html to get output in HTML format.
Get-Process | Sort-Object Name | Select-Object Id,Name,Desciption | Group-Object Name | Select-Object Count, Name | ConvertTo-Html

Add Out-File to put all this output in HTML file.
Get-Process | Sort-Object Name | Select-Object Id,Name,Desciption | Group-Object Name | Select-Object Count, Name | ConvertTo-Html | Out-File "C:\Test\test.html"

Open file in browser to see the content.
Invoke-Expression "C:\Test\test.html"

You can find a no of commands which can be used with pipeline to produce the results more meaningful.

PowerShell - Functions

The basic structure of a PowerShell function is : the Function keyword follows the name of the function, and after that the PowerShell script in braces.

Lets create a simple function to say 'hello' :
function myFunction { write-host("Hello") }

Call function:

Function has default variable $args, which may contains array of values:
function myFunction { write-host("Hello " + $args) }

These both calls work fine
myFunction 'world'
myFunction 'world' ' from Power Shell'

This function looks for if we have any values supplied in $args when called
function CheckArgs {
    If ($args -ne $null)
        "Args passed : $args"
        "No Args passed."

CheckArgs 'test' 'test3'

Caculate the sum of all numbers specified in $args
function sum
    $sum = 0;

    foreach ($i in $args)
        $sum += $i

    "Total : $sum"
sum 1 2 3

We can also use named variables as parameters.

function Add($p1, $p2)
    $p1 + $p2
Add 1 7

Functions can have default values of parameters, in case if the function is being called without parameter then the default value will be used.

function Add($p1 = 5, $p2 = 10)
    $p1 + $p2

Add 2  # you will get 12, i.e. for parameter $p2, the default value 10 will be used.
Add 2 50 # you will get 52

We can use explicitly data types with functions parameters. If we put the wrong type of argument we will get error message.
function Add([int]$p1, [int]$p2)
    $p1 + $p2
Add 2 10
Add 2 'test'

 Here the first call works as expected, while the second call give you the error:
Add : Cannot process argument transformation on parameter 'p2'. Cannot convert value "test" to type "System.Int32". Error: "Input string was not in a correct format."

Tuesday, August 25, 2015

PowerShell - Arrays and Hash Tables

In PowerShell script, the easiest way to create an array is to use comma separator:
$array = 1,2,3,4,5

For sequential numbers you can use a short syntax:
$array = 1..15

In PowerShell arrays, even you can place values with multiple data types.
$array = "PowerShell", "Version", 3.0

Another sysntax to create array is to use @() construct
$array = @(1, "PowerShell", "Version", 3.0)

You can access array member with zero-based index

Even we can use multiple indices separated by comma, in order to get multiple values.

Add a new item in array:
$array += "new item"

Hash Tables
To create a hash table, we use @{} instead of @(). Key-value pairs will be stored in hash table separated by semi-colons:
$hashTable = @{Id=1; Code="I001"; Name="Laptop"}

Access a key in Hash Table.

We can also specify multiple keys to extract multiple values.
$hashTable["Id", "Code"]

Also you can use the key with dot notation

To get all keys/values present in the HashTable

Add new key in Hash Table
$hashTable["Category"] = "Office Supplies"
$hashTable.Add("Category", "Office Supplies")

Remove a key from Hash Table

Look for a key in Hash Table

PowerShell - Working with XML

PowerShell supports many of the .Net data types, one of the intesresting type is XML document. Lets see how we can use this type to process XML.

We have a string variable with items list xml:
$items = "<items>" +
    "<item code='I001' name='Laptop'/>" +
    "<item code='I002' name='Tablet'/>" +
    "<item code='I003' name='CellPhone'/>" +

Now define a new variable with xml data type and assign this string to that variable
[xml]$itemsXml = $items

We can check the Xml back in string format.

This will show the items in table format

It will also allow you even to modify the xml content.
$itemsXml.items.ChildNodes[0].Attributes["name"].InnerText = "Notebook"

Again display the child nodes to see the effect.

Lets create a new item:

$xmlElement =  $itemsXml.CreateElement("item")
$xmlNodeNew =  $itemsXml.CreateTextNode("item")
$xmlNodeTemp = $xmlElement.AppendChild($xmlNodeNew)

$xmlAttCode = $itemsXml.CreateAttribute("code")
$xmlAttCode.Value = "I004"
$xmlNodeTemp = $xmlElement.Attributes.Append($xmlAttCode)

$xmlAttName = $itemsXml.CreateAttribute("name")
$xmlAttName.Value = "New Item"
$xmlNodeTemp = $xmlElement.Attributes.Append($xmlAttName)


Query child nodes to see the effect.

Monday, August 24, 2015

PowerShell - How to Register, Start and Unregister a ScheduledJob

In Windows PowerShell 3.0, Job Scheduling cmdlets are available in the PSScheduledJob module, which let you run commands asynchronously in the background. This allows you to continue running commands at the command line while the background job runs. 

For the scope of this post, I will demonstrate how to register a scheduled job, start and then unregister it.

Register Scheduled Job:
$triggerTimerJob1 = New-ScheduledTaskTrigger -Once -At (Get-Date) -RepetitionInterval (New-TimeSpan -minute 1) -RepetitionDuration ([TimeSpan]::MaxValue)

Register-ScheduledJob -Name TimerJob1 -Trigger $triggerTimerJob1 -ScriptBlock {
    Get-Date | Out-File -FilePath "C:\Test\myfile.txt" -Append

The variable $triggerTimerJob1 is the trigger we need to define how often our job will be executed. For exmaple, I specified it as Once with starting time as current time. Other valid values for this parameter are : AtLogon, AtStartup, Daily, Weekly.
-RepetitionDuration specifies how long the repetition pattern repeats after the task starts.
-RepetitionInterval Specifies an amount of time between each restart of the task. The task will run, wait for the time interval specified, and then run again. For the demo purpose I used 1 minute.

Register-ScheduledJob is the cmdlet to register job. We define the job name in the -Name parameter, and tirgger with our defined variable.
The -ScriptBlock parameter is the actual job script which we want to execute. Here I simply put the current Date in a text file with -append parameter, so we could test the the file how often the job is actually executed.

Start Scheduled Job:
Start-Job -DefinitionName TimerJob1

Start-Job cmdlet is the command used to start job. We need to place our same job name here in DefinitionName parameter.

Unregister Scheduled Job:
To Unregister a job we use the Unregister cmdlet with job name:
UnRegister-ScheduledJob -Name TimerJob1


Sunday, August 23, 2015

PowerShell - Convert Output to HTML table

In this post we will see how to convert the output to HTML table. Lets review the script:
$headStyle = '
<style type="text/css">

    table th{
        background: #b131f3;
        border-width: 1px;
    table td{
        border:#5625f4 1px solid;

    table tr:nth-child(odd){
        background: #c2e1d4;
    table tr:nth-child(even){
        background: #cab2e2;

$filePath = "C:\Test\Test.htm"

Get-Process | Select-Object Id,Name,CPU,Description -first 10 | ConvertTo-HTML -head $headStyle | Out-File $filePath

Invoke-Expression $filePath

  • First define a variable $headStyle with some CSS for our target HTML table.
  • $filePath is the file path where we will palce our HTML.
  • Then comes the key cmdlet. Get-Process will get current processes, then we include the pipeline with Select-Object cmdlet and only select our desired columns, i.e. Id, Name, CPU and Description.
  • -First 10 parameter will take top 10 rows, same like TOP keyword in SQL Server.
  • Again use pipeline to place the cmdlet for HTML conversion. i.e. ConvertTo-Html
    parameter contains the variable we defined so we could use our custom CSS.
  • Third pipeline added the Out-File cmdlet to save our HTML in file system.
  • Invoke-Expression will open the target HTML file in browser.

Saturday, August 22, 2015

Connect SQL Server from PowerShell

In this post we will see how to connect local SQL server and select data from it. Lets review the script:

$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = “Server=MACHINE_NAME\SQL_INSTANCE_NAME;Database=DB_NAME;User ID=USERNAME;Password=PASSWORD;”

$sqlcmd = New-Object System.Data.SqlClient.SqlCommand
$sqlcmd.Connection = $conn
$sqlcmd.CommandText = "select top 10 * from dbo.Items"

$sqladapter = New-Object System.Data.SqlClient.SqlDataAdapter
$sqladapter.SelectCommand = $sqlcmd

$dataset = New-Object System.Data.DataSet


$dataset.Tables[0] | format-table -AutoSize

For C# programmers, this script is self-explanatory. First we defined our SQL connection string, you can place your actual Machine Name, SQL Instance Name, Database Name, User Name and Password. Then create SQLCommand and SQLAdapter objects with required values and get the result in DataSet variable.

To output our data table to console we use the command:
$DataSet.Tables[0] | format-table -AutoSize

The Format-Table cmdlet formats the output of a command as a table.
The Autosize parameter adjusts the column widths to minimize truncation

Here is sample output:

For the purpose of this demo, I created a test SQL table:

CREATE TABLE [dbo].[Items](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ItemCode] [varchar](10) NOT NULL,
    [ItemName] [varchar](50) NOT NULL,
    [Id] ASC


File x.ps1 cannot be loaded because running scripts is disabled on this system.

When you load a script file in PowerShell ISE and try to run that script, probably you will get this error:

File x.ps1 cannot be loaded because running scripts is
disabled on this system. For more information, see about_Execution_Policies at + CategoryInfo: SecurityError: (:) [], ParentContainsErrorRecordExcepti on + FullyQualifiedErrorId : UnauthorizedAccess

This is because the script is blocked from execution due to the execution policy. So you need to set it according to your requirements. For Example I set it as :
Set-ExecutionPolicy -Scope Process -ExecutionPolicy RemoteSigned
And I get my script worked, it allows the execution of script only for current process.

The Scope parameter could have following valid values:
  • Process: The execution policy affects only the current Windows PowerShell process.
  • CurrentUser: The execution policy affects only the current user.
  • LocalMachine: The execution policy affects all users of the computer.
The default is LocalMachine.


Thursday, August 20, 2015

Call inline C# from PowerShell

Windows PowerShell is a shell for task automation and configuration management. The shell is based on the .NET framework and it includes a command-line shell and a scripting language. Features include built-in commands (cmdlets) to manage computers in your network. Cmdlets perform common system administration tasks such as managing the registry; managing event logs; controlling services and processes; and using Windows Management Instrumentation.

Apart from shell's cmdlets, there is also flexibility to use C# code, because the shell is based on .Net framework. In this post I demonstrate a way to call inline C# code from the PowerShell script. Following is the script to do so.

$Source = @"
namespace TestNamespace
    public static class MyClass
        public static string GetMessage()
            return "Text from C#";
        public static int Add(int a, int b)
            return a + b;

Add-Type -TypeDefinition $Source -Language CSharp


[TestNamespace.MyClass]::Add(3, 5)

Let review this script:
  • The first statement here you see that we put our C# code in a variable named $source (in PowerShell we define variable by placing $ sign with it).
  • Next we call PowerShell cmdlet Add-Type, here we define -TypeDefinition is our C# class code, and telling the shell that this script is actually C# code defined by the argument -Language CSharp
    These two steps are required, lets say, to register our C# code, so PowerShell can understand it.
    Now its the time to actually call our code and that is pretty straight forward.
  • [TestNamespace.MyClass]::GetMessage() will call our Method which only returns a string.
  • [TestNamespace.MyClass]::Add(3, 5) will accept two integer parameters and return the output as integer.
So here we get C# action in PowerShell.

Tuesday, August 18, 2015

System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation'

While restoring SQL backup file from one machine to another, I got this error message:

System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.x\MSSQL\DATA\x.ldf'. (Microsoft.SqlServer.Smo)

Here is what solution I found for this problem.
Carefully check the path showing in error, while backup file is created at first machine it stores the file path of that machine. Now when you are restoring on new machine if the path is same (i.e. server name, and also version etc.) then you get the file restore successfully otherwise you will get this error. So simply change the path according to new machine in "Restore As" option, and you will get fix to this error.