PowerShell –SQL Automate Tutorial-8

Disclaimer:Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.

Scenario: PS is most powerful and very helpful tool for windows and SQL server administrator and automation. So its today need to learn at least basic of some PS cmdlets.

In this series of tutorial, we will only walk through some of the basis and need to know cmdlets which will helps us in future in order to manage our SQL server.

For more details, you can google and look for good site.

Note:- Please click here to get 7th part of PowerShell –SQL Automate tutorial.

To get more information about PowerShell cmdlets, you can refer this link.

In this tutorial, we will walk through PowerShell ForEach-Object cmdlets.

PowerShell ForEach-Object:-

The ForEach-Object in PowerShell executes a block of statements against each item in a collection of input objects. These objects are passed through the pipeline or specified by using the -InputObject parameter.

Syntax
    ForEach-Object [-process] ScriptBlock[] [-inputObject psobject]
       [-begin scriptblock] [-end scriptblock] [-Parallel] [-ThrottleLimit n] [CommonParameters]


key
   -process ScriptBlock[]
       A block of PowerShell script that is applied to each incoming object.
        
   -inputObject psobject
       Accepts an object that the script block specified in the process parameter
       will act upon. Enter a variable that contains the objects or type a
       command or expression that gets the objects.
        
   -begin ScriptBlock
       A script block to run before processing any input objects.
        
   -end ScriptBlock
       A script block to run after processing all input objects.
 
   -Parallel
       Run all script blocks in parallel for each piped input object. PowerShell 7.0+
       Parallel will not always speed up script execution. And can significantly slow down script
       execution if used heedlessly. The overhead for a trivial script can make -parallel much slower.

   -ThrottleLimit
       Limit the number of script blocks running in parallel at a time, default = 5. PowerShell 7.0+

Standard Aliases for Foreach-Object: the ‘%‘ symbol, ForEach

For the fastest performance: use the ForEach statement (or method) when the collection of objects is small enough that it can be loaded into memory. (eg an array of 20 string values)
Use the ForEach-Object cmdlet when you want to pass only one object at a time through the pipeline, minimising memory usage. (e.g. a directory containing 10,000 files)

Examples

Retrieve the files (and folders) from the C: drive and display the size of each:

PS C:> get-childitem C:\ | foreach-object -process { $_.length / 1024 }

(The $_ variable holds a reference to the current item being processed.)

Retrieve the 1000 most recent events from the system event log and store them in the $events variable:

PS C:> $events = get-eventlog -logname system -newest 1000

Then pipe the $events variable into the ForEach-Object cmdlet.

PS C:> $events | foreach-object -begin {Get-Date} -process {out-file -filepath event_log.txt -append -inputobject $_.message} -end {get-date}

Sort-Object

Sort objects by property value.

Syntax
      Sort-Object [[-property] Object[]] [-inputObject psobject] [-culture string]
            [-caseSensitive] [-unique] [-descending] [CommonParameters]

Key
   -Property Object
       A property or properties to use when sorting. Wildcards are permitted. 
       Objects are sorted based on the values of these properties.

       Multiple properties may be specified, the objects will be sorted by 
       each property in turn. i.e Surname and then FirstName

       If not property is specified, sort-object will sort using the default
       property for the object type.

   -InputObject psobject
       The objects to be sorted.

       When the -InputObject parameter is used to submit a collection of items,
       Sort-Object receives one object that represents the collection.
       Because one object cannot be sorted, Sort-Object returns the entire collection unchanged.

       To sort objects, pipe them to Sort-Object.

   -Culture string
       The cultural/country configuration to use when sorting.

   -CaseSensitive
       Sort UPPER and lower case letters separately.

   -Unique 
       Return only the unique values.

   -Descending 
       Sort in descending order. The Descending parameter applies to all properties.
       To sort by some properties in ascending order and others in descending order, 
       specify their property values with a hash table.
The -Descending parameter applies to all properties. To sort some properties in ascending order and others in decending order, specify the property values with a hashtable like this:
-property @{expression={$_.PropName};Descending=$true}
Examples
List the files in the current directory and sort using the default order (alphabetical by Name):
PS C:\> get-childitem | sort-object

List the files in the current directory and sort by date/time:
PS C:\> get-childitem | sort -property LastWriteTime
List the files in the current directory and sort by file length (size):
PS C:\> get-childitem | sort -property length
List the files in the current directory and sort in descending order by the time span between CreationTime and LastWriteTime:
PS C:\> get-childitem *.* | sort @{Expression={$_.LastWriteTime-$_.CreationTime}; Ascending=$false} | select-object LastWriteTime, CreationTime
Display the services on the computer in descending Status order and ascending DisplayName order:
PS C:\> get-service | sort-object -property `
@{Expression="Status";Descending=$true}, `
@{Expression="DisplayName";Descending=$false}