#Powershell and Windows Update

So I have been using this for a while, but can’t remember if I shared it, so here it is.  I have two functions to get Installed Updated and Pending Updates.

They use winrm, so if it doesn’t work against a remote server you just need to run winrm qc

function Get-InstalledSoftwareUpdates {
  param($ComputerName=(HOSTNAME), $Credential)

  $code = {
    $Session = New-Object -ComObject Microsoft.Update.Session
    $Searcher = $Session.CreateUpdateSearcher()
    $HistoryCount = $Searcher.GetTotalHistoryCount()

  Invoke-Command $code @psboundparameters | Select @{Expression={$ComputerName};Label="Machine"},  Date, Title, Description, ResultCode


function Get-PendingSoftwareUpdates {
  param($ComputerName=(HOSTNAME), $Credential)

  $code = {
    $Session = New-Object -ComObject Microsoft.Update.Session
    $Searcher = $Session.CreateUpdateSearcher()
    $HistoryCount = $Searcher.GetTotalHistoryCount()
    $SearchResult = $Searcher.Search("IsInstalled=0 and Type='Software'")
    For ($X = 0; $X -lt $SearchResult.Updates.Count; $X++){$SearchResult.Updates.Item($X)}

  Invoke-Command $code @psboundparameters | Select @{Expression={$ComputerName};Label="Machine"}, Title, Description, IsHidden, IsMandatory

You can run it against your windows desktop too, just paste in the functions and run them.  Prob best to put them in to a variable so:

$installed = Get-InstalledSoftwareUpdates
$pending = Get-PendingSoftwareUpdates

I also do some funky html output too, but I will let you play with that ;-)


#Powershell WhoAmI

This is a funky function I have in my profile.ps1

Function WhoAmI([switch]$All=$False){
  If($All){$xUser = [System.Security.Principal.WindowsIdentity]::GetCurrent() }ELSE{ $xUser = ([System.Security.Principal.WindowsIdentity]::GetCurrent()).Name}
  Write-Host $xUser -Foregroundcolor Green

You can run:


which give provide your user name -or-

WhoAmI -All

will give you extra goodness

#Powershell Check if Running

So I have a number of scripts that run as scheduled tasks.  The issue is that some scripts take longer to run and at times I have multiple copies of the same script running.  I wanted to stop this.  So I knocked up this.  Essentially it checks to see if the script that has started is already running, and then it kills the old process, and the script continues.

#AppName is the name of the script
$AppName = "Get-PerformanceInfo.ps1"

Write-Host "Checking if script is already running"
#Get the PID of this script
$myPID = ([System.Diagnostics.Process]::GetCurrentProcess()).id
Write-Host $myPID

#Who is the user running the script
$cu = ([System.Security.Principal.WindowsIdentity]::GetCurrent()).Name.split("\")[-1];

#Check to see if the script is already running
$xRunning=get-wmiobject win32_process|where{$_.name -eq "powershell.exe"}|select name,processid,commandline,@{n="owner"; e={$_.getowner().user}} | where {($_.owner -eq $cu) -AND ($_.commandline -like "*$AppName*")  -AND ($_.processid -ne $myPID)};

#If it's running kill it 
IF(-NOT [string]::IsNullOrEmpty($xRunning)){Write-Host "Running! Terminating!";$xRunning | FL; $xRunning | forEach{$xpid = $_.processid; $xpid;Stop-Process -id $xpid -force -confirm:$false}}ELSE{Write-Host "Not Running!"}

Powershell Flip-Array Function

You know when you get some output from say Get-ExchangeServer that has load of values and when you output it to HTML it goes off the page and looks pants?  I do quite a bit of this, and thought, can I flip the array, so around.  Yeah baby you can! So here you go.  This v2 with some bugs ironed out.  Enjoy

function Flip-Array([Array]$Array, [String]$key){
  $matrix =@()
  $KeyValue = @()
  $Array | ForEach{$KeyValue += $_.$key}
  $cols = @(); $cols += "Property" ; $cols += $keyValue
  $rows = @(); $array | Get-Member | Where {($_.MemberType -like "*Property") -AND ($_.Name -ne $key)} | ForEach{$Rows += $_.Name}
  $Rows = $Rows | sort unique
  $rows | ForEach{
    $tmpMatrix = "" | Select $cols
    $tmpMatrix.Property = $_
    $Matrix += $tmpMatrix

  ForEach($item in $Array){
    $ItemKey = $item.$key
    ForEach($Row in $Rows){
      $itemvalue = $Item.$Row
      if($itemvalue.GetType.Name -like "MultiValued*"){$itemvalue = $Item.$Row -Join "!#!"}
      ($Matrix | Where {$_.Property -eq $row}).$ItemKey = $itemvalue 
  $matrix = $matrix | sort property -Unique
Return $Matrix

$e = get-exchangeserver
$h = Flip-Array $e Name | ConvertTo-Html
$h | out-file .\exchange.html

#MsExchange Versions


So if you want to find out the version of Exchange you are running you can always run:

Get-ExchangeServer | Select Name, ServerRole, Edition, AdminDisplayVersion

Now this is cool, but it only shows the major version and not the CU.

You can find exchange build information from two main places:

I have dropped the information into a CSV file [ExchangeBuilds].   I found that updating a CSV file with new builds was easier than editing code all the time.

Now code below to create a summary matrix.  Enjoy

#Import the csv and covert to a hash table
$ExchangeBuilds = import-csv .\ExchangeBuilds.csv
$BuildMatrix = @{}
ForEach($Item in $ExchangeBuilds){
  $BuildMatrix.Add($item.Build, $item.version)

#Get exchange servers
Get-ExchangeServer | Select Name, ServerRole, Edition, AdminDisplayVersion

#Loop the servers to create the matrix
$matrix =@()
$ExchangeServers = Get-ExchangeServer | Sort Name
ForEach($Item in $ExchangeServers){
  $tmpServer = $item.Name + "." + $item.domain
  Write-Host $tmpServer 
  $tmpMatrix = "" | Select Name, Roles, Edition, FileVersion,UpdateRollup
  $tmpMatrix.Name = $tmpServer 
  $tmpMatrix.Roles = $Item.ServerRole
  $tmpMatrix.Edition = $Item.Edition

     6 {$key="SOFTWARE\Microsoft\Exchange\Setup"}
     8 {$key="SOFTWARE\Microsoft\Exchange\Setup"}
    14 {$Key="SOFTWARE\Microsoft\ExchangeServer\v14\Setup"}

  #Find the exchange binary path
  $type = [Microsoft.Win32.RegistryHive]::LocalMachine
  $regKey = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey($type, $tmpServer )
  $regKey = $regKey.OpenSubKey($key)

  #work out the file we need to use
     6      {$tmpPath = $regKey.GetValue("Services");      $tmpFile = "mad.exe";    $tmpPath += "\bin\"}
    Default {$tmpPath = $regKey.GetValue("MsiInstallPath");$tmpFile = "ExSetup.exe";$tmpPath += "bin\"}

  #Get the file verision of mad.exe or exsetup.exe
  $tmpPath = $tmpPath.replace(":", "$")
  $tmpunc  = "\\" + $tmpServer + "\" + $tmpPath + $tmpFile

  IF (test-path $tmpunc){
    $tmpVer = (dir "$tmpunc").VersionInfo.FileVersion
    $tmpMatrix.FileVersion = $tmpVer
    $tmpMatrix.UpdateRollup = $BuildMatrix.$tmpver
  $Matrix += $tmpMatrix

Using #Powershell Get a List of #Exchange InSite DCs from Eventlog [v2]

So I posted this http://blog.flaphead.com/2013/03/13/get-a-list-of-insite-dcs-from-eventlog/ a month or so ago, and found some stuff in that didn’t quite work for me.

So here is the update code.  Essentially I have now split out the server and domain so they can be referenced in the array

$Evt2080 = Get-EventLog Application -Source “MSExchange ADAccess” | where {($_.Category -eq “Topology”) -AND ($_.EventId -eq 2080)} | Select -first 1
$InSiteMatrix = @()
$Fields = “Server”,”Roles”, “Enabled”, “Reachability”, “Synchronized”, “GCcapable”, “PDC”, “SACLright”, “CriticalData”, “Netlogon”, “OSVersion”, “HostName”, “Domain”
$InSite = ($Evt2080.ReplacementStrings[-2]).Split(“`n”) | Where {$_}
ForEach($Item in $InSite){
$tmpMatrix = “” | Select $Fields
$tmpSplit = $item.Split(“`t”)
$tmpMatrix.Server = $tmpSplit[0]
$tmpMatrix.HostName = $tmpMatrix.Server.Split(“.”)[0]
$tmpMatrix.Domain = $tmpMatrix.Server.Substring($tmpMatrix.HostName.Length +1)
If($tmpMatrix.Server -ne “”){
$tmpValues = $tmpSplit[-1].split(” “)
ForEach($thing in $tmpValues ){
$tmpMatrix.($Fields[$i]) = $thing
} #ForEach
$InSiteMatrix += $tmpMatrix
} #If
} #ForEach

SQL query to get #BlackBerry User information

So following on from my last post (http://blog.flaphead.com/2013/04/23/using-powershell-to-run-a-sql-command/) I use this SQL query to dump BlackBerry User information in to a #Powershell array so I can export it out.

SELECT [ServerConfig]. [ServiceName]
      , [UserConfig]. [DisplayName]
      , [UserConfig]. [UserName]
      , [userconfig]. [MailboxSMTPAddr] as [SMTPAddress]
      , [SyncDeviceMgmtSummary]. [ModelName]
      , [SyncDeviceMgmtSummary]. [PhoneNumber]
      , [SyncDeviceMgmtSummary]. [PlatformVer]
      , [SyncDeviceMgmtSummary]. [IMEI]
      , [SyncDeviceMgmtSummary]. [HomeNetwork]
      , [SyncDeviceMgmtSummary]. [AppsVer]
      , [UserConfig]. [PIN]
      , [ITPolicy2]. [PolicyName]
      , [UserConfig]. [MailboxSMTPAddr]
      , [UserConfig]. [MailboxDN]
      , [UserConfig]. [ServerDN] as [ExchangeServer]
      , [UserConfig]. [AgentId]
      , [UserConfig]. [RoutingInfo]
      , [UserStats]. [MsgsPending]
      , [UserStats]. [LastFwdTime]
      , [UserStats]. [LastSentTime]
      , CASE [UserStats] . [Status]
            WHEN 13 THEN ‘Stopped’
            WHEN 12 THEN ‘Running’
            WHEN 9  THEN ‘Redirection disabled’
            WHEN 0  THEN ‘Initializing’
            ELSE ‘Unknown [‘ + CONVERT (varchar , [UserStats]. [Status] ) + ‘]’
        END AS UserStatus
  FROM [dbo] . [UserConfig]
   LEFT OUTER JOIN [dbo] .[UserStats]
    ON [UserConfig]. [Id] =[UserStats] . [UserConfigId]
   LEFT OUTER JOIN [dbo] .[ITPolicy2]
    ON [UserConfig]. [ITPolicy2Id] =[ITPolicy2] . [Id]
   LEFT OUTER JOIN [dbo] .[ServerConfig]
    ON [UserConfig]. [ServerConfigId] =[ServerConfig] . [Id]
   LEFT OUTER JOIN [dbo] .[SyncDeviceMgmtSummary]
    ON [UserConfig]. [Id] =[SyncDeviceMgmtSummary] . [UserConfigId]

So setting the above at $SqlQuery and then running

$SqlServer = “BlackBerry SQL Server”
$SqlDb = “BlackBerry Database Name”
RunSqlQuery  $SqlServer $SqlDb $SqlQuery


Using #Powershell to run a SQL Command

So I have a hell of a lot of stuff where I am pushing or pulling data from SQL and wanted to share the function I use:

Function RunSqlQuery([string]$SqlServer, [string]$SqlDb, [string]$SqlCmd){
Write-Host “SQL Server…: ” $SqlServer
Write-Host “Sql Database.: ” $SqlDb

$Connection = New-Object System.Data.SQLClient.SQLConnection

$Connection.ConnectionString =”server=$SqlServer;database=$SqlDb;trusted_connection=true;”

$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.CommandType = [System.Data.CommandType]”Text”
$Command.Connection = $Connection
If($Error.Count -gt 0){Exit}
$Command.CommandText = $SQLCmd

$tmpDT = New-Object “System.Data.DataTable”
$Reader = $Command.ExecuteReader()

$i=0;$tmpDT | ForEach{$i++}
Write-Host “Query Result size: ” $i
Write-Host “`n”

Return $tmpDT

The function assumes the account using the function has rights on the SQL database.

Typically I set the following:

$SqlServer = “SQLServer”
$SqlDB = “SQLDatabase”
$SqlQuery = “Select * from something”

RunSqlQuery $SqlServer $sqldb $SQLQuery


$SqlData = RunSqlQuery $SqlServer $sqldb $SQLQuery


Finding a computer in the AD using #Powershell

So I need to search the AD today for computer objects to see if a description has been set.

$Domain = ([System.DirectoryServices.ActiveDirectory.Domain]::GetCurrentDomain()).Name
$Dom  = “LDAP://DC=” + $Domain.Replace(“.”,”,DC=”)
$strComputer = “<computer2search4>”
$strFilter  = “(cn=$strComputer)”
$Root       = New-Object DirectoryServices.DirectoryEntry $Dom
$selector   = New-Object DirectoryServices.DirectorySearcher
$selector.PageSize    = 1000
$selector.Filter      = $strFilter
$selector.SearchRoot  = $root
$selector.SearchScope = “Subtree”
$Objs = $selector.findall()


The results are in the variable $Objs

BlackBerry “Domain Prep”

#BlackBerry, #Powershell

So I had a bit of brain freeze yesterday, and then it hit me.  BlackBerry needs AD SendAs permissions, but I thought I had done that!

Doh! The current place I am working has multiple AD Domains, and I done the root!  What I needed was an Exchange like domainprep to apply the permissions ..

So knocked up this.  I basically this assigns the permission to a group, so I can add and remove service accounts to it ;-)

$domains = ([System.DirectoryServices.ActiveDirectory.forest]::getcurrentforest()).domains | select name | sort name
ForEach($domain in $Domains){
$dom = $domain.name
Write-Host $dom
Add-ADPermission $dom -user “domain\Group” -AccessRights extendedright -ExtendedRight Send-As