Still Alive and Kicking

It has been a while since I last posted. I have by no means abandoned my blog for greener pastures. I have been deeply entrenched with some side projects (PoshWSUS and WSUS Patch/Audit GUI), work, the 2011 Scripting Games and the PowerShell Deep Dive that was held recently in Las Vegas. I am working on a blog post regarding both the Deep Dive and the Scripting Games and working on some more PowerShell/WSUS articles as well as working on a guest blog posting for the Hey Scripting Guys blog as well. My personal blog post will be in the next week and I will let you know when the guest blog post will be later on when I find out.

Posted in News | Tagged | Leave a comment

Retrieving contents of Hosts file using PowerShell

I decided that I wanted a way to view all of the hosts file on a bunch of servers and see just what is listed.  Most of the code is fairly straight-forward where I use Test-Connection and Test-Path to validate whether a system is online and then whether the Hosts file is under a WinNT folder or Windows folder. If a system was upgraded, there is potential that the Hosts file resides under WinNT.

Once the validation is done and everything is still checking out OK, I can then grab the contents of the file using the Switch command and the –file and –regex switches. By doing this, I am going to read each line of the file one line at a time. By supplying the –regex switch, I can define a regular expression to look for a specific item, in this case, the initial IPV4 IP address using this line:

"^\d\w+"

Yes, this is a weak regular expression for finding the IPV4 address, but I felt it was all I needed because there would only be a few things in the hosts file to find.

Also, since everything in that line is one big string, I have to perform a split in order to grab exactly what I need to include the IP address, hostname associated with the IP and any notes that happen to be with that information. I also clear out any extra spaces so the array created is always accurate when I add the items into the collection.

$new = $_.Split("") | ? {$_ -ne ""}

After which then everything else goes into the Default grouping and I then perform an If statement to verify that the is no comment block (#) and no white space on the hosts file using the following line:

 If (!("\s+" -match $_ -OR $_.StartsWith("#"))) {

If it has neither of those, then it is assumed to be an IPV6 address and adds it to the collection. I was going to write a regular expression for IPV6 addresses, but since there are a few different variations of how an IPV6 address can be, I decided to just go with my original idea of looking for everything but the IPV6 and then setting the Default action of the switch to be the IPV6 address. For an idea of what a regular expression would look like on IPV6, check this out.

Here is an example of using the function:

Capture

As you can see, the output shows the Computer, IPV4, IPV6 , the associated hostname for the IP and any notes that might have been left on the Hosts file. Nothing too wild going on here. But running it on your enterprise network may or may not reveal some surprises that you were not expecting.

You can scan your network and then export the results to a CSV report by piping the output to Export-CSV.

 

Code

Poshcode

Script Repository

Function Get-HostsFile { 
<#   
.SYNOPSIS   
   Retrieves the contents of a hosts file on a specified system 
.DESCRIPTION 
   Retrieves the contents of a hosts file on a specified system 
.PARAMETER Computer 
    Computer name to view host file from 
.NOTES   
    Name: Get-HostsFile 
    Author: Boe Prox 
    DateCreated: 15Mar2011  
.LINK  
    https://boeprox.wordpress.com        
.EXAMPLE   
    Get-HostsFile "server1" 
 
Description 
-----------     
Retrieves the contents of the hosts file on 'server1' 
 
 
#>  
[cmdletbinding( 
    DefaultParameterSetName = 'Default', 
    ConfirmImpact = 'low' 
)] 
    Param( 
        [Parameter( 
            ValueFromPipeline = $True)] 
            [string[]]$Computer                                                
                         
        ) 
Begin { 
    $psBoundParameters.GetEnumerator() | % {   
        Write-Verbose "Parameter: $_"  
        } 
        If (!$PSBoundParameters['computer']) { 
        Write-Verbose "No computer name given, using local computername" 
        [string[]]$computer = $Env:Computername 
        } 
    $report = @() 
    } 
Process { 
    Write-Verbose "Starting process of computers" 
    ForEach ($c in $computer) { 
        Write-Verbose "Testing connection of $c" 
        If (Test-Connection -ComputerName $c -Quiet -Count 1) { 
            Write-Verbose "Validating path to hosts file" 
            If (Test-Path "\\$c\C$\Windows\system32\drivers\etc\hosts") { 
                Switch -regex -file ("\\$c\c$\Windows\system32\drivers\etc\hosts") { 
                    "^\d\w+" { 
                        Write-Verbose "Adding IPV4 information to collection" 
                        $temp = "" | Select Computer, IPV4, IPV6, Hostname, Notes 
                        $new = $_.Split("") | ? {$_ -ne ""} 
                        $temp.Computer = $c 
                        $temp.IPV4 = $new[0] 
                        $temp.HostName = $new[1] 
                        If ($new[2] -eq $Null) { 
                            $temp.Notes = "NA" 
                            } 
                        Else { 
                            $temp.Notes = $new[2] 
                            } 
                        $report += $temp 
                        } 
                    Default { 
                        If (!("\s+" -match $_ -OR $_.StartsWith("#"))) { 
                            Write-Verbose "Adding IPV6 information to collection" 
                            $temp = "" | Select Computer, IPV4, IPV6, Hostname, Notes 
                            $new = $_.Split("") | ? {$_ -ne ""} 
                            $temp.Computer = $c 
                            $temp.IPV6 = $new[0] 
                            $temp.HostName = $new[1] 
                            If ($new[2] -eq $Null) { 
                                $temp.Notes = "NA" 
                                } 
                            Else { 
                                $temp.Notes = $new[2] 
                                } 
                            $report += $temp 
                            } 
                        }                         
                    } 
                }#EndIF 
            ElseIf (Test-Path "\\$c\C$\WinNT\system32\drivers\etc\hosts") { 
                Switch -regex -file ("\\$c\c$\WinNT\system32\drivers\etc\hosts") { 
                    "^#\w+" { 
                        } 
                    "^\d\w+" { 
                        Write-Verbose "Adding IPV4 information to collection" 
                        $temp = "" | Select Computer, IPV4,IPV6, Hostname, Notes 
                        $new = $_.Split("") | ? {$_ -ne ""} 
                        $temp.Computer = $c 
                        $temp.IPV4 = $new[0] 
                        $temp.HostName = $new[1] 
                        If ($new[2] -eq $Null) { 
                            $temp.Notes = "NA" 
                            } 
                        Else { 
                            $temp.Notes = $new[2] 
                            } 
                        $report += $temp 
                        } 
                    Default { 
                        If (!("\s+" -match $_ -OR $_.StartsWith("#"))) { 
                            Write-Verbose "Adding IPV6 information to collection" 
                            $temp = "" | Select Computer, IPV4, IPV6, Hostname, Notes 
                            $new = $_.Split("") | ? {$_ -ne ""} 
                            $temp.Computer = $c 
                            $temp.IPV6 = $new[0] 
                            $temp.HostName = $new[1] 
                            If ($new[2] -eq $Null) { 
                                $temp.Notes = "NA" 
                                } 
                            Else { 
                                $temp.Notes = $new[2] 
                                } 
                            $report += $temp 
                            } 
                        }                         
                    }         
                }#End ElseIf 
            Else { 
                Write-Verbose "No host file found" 
                $temp = "" | Select Computer, IPV4, IPV6, Hostname, Notes 
                $temp.Computer = $c 
                $temp.IPV4 = "NA" 
                $temp.IPV6 = "NA"                 
                $temp.Hostname = "NA" 
                $temp.Notes = "Unable to locate host file" 
                $report += $temp 
                }#End Else 
            } 
        Else { 
            Write-Verbose "No computer found" 
            $temp = "" | Select Computer, IPV4, IPV6, Hostname, Notes 
            $temp.Computer = $c 
            $temp.IPV4 = "NA" 
            $temp.IPV6 = "NA"             
            $temp.Hostname = "NA" 
            $temp.Notes = "Unable to locate Computer" 
            $report += $temp             
            } 
        } 
    } 
End { 
    Write-Output $report 
    } 
}
Posted in powershell, scripts | Tagged , , | 6 Comments

Quick-Hits: List 25 newest scripts in Microsoft Script Repository

This is a just a quick script to query the rss feed of the Microsoft Script Repository for the newest 25 scripts that have been uploaded.  Just 4 lines of code to get this accomplished, which is yet another reason why PowerShell is just so awesome. Smile I am once again using the System.Net.WebClient class to make the web site call and download the source code while also using the [XML] accelerator to read the xml data that I downloaded. For more information about what I did with the System.Net.WebClient class, see this post.

Example from March 4,2011

Get-ScriptRepositoryNewest

Capture

 

Function Get-ScriptRepositoryNewest {
<#   
.SYNOPSIS   
    Retrieves the 25 newest scripts from the Microsoft Script Repository 
.DESCRIPTION 
    Retrieves the 25 newest scripts from the Microsoft Script Repository
.NOTES   
    Name: Get-ScriptRepositoryNewest 
    Author: Boe Prox 
    DateCreated: 04Mar2011         
.EXAMPLE   
Get-ScriptRepositoryNewest
 
Description 
----------- 
Retrieves the 25 newest scripts from the Microsoft Script Repository 
#>  
$url = "http://gallery.technet.microsoft.com/scriptcenter/site/feeds/searchRss?sortBy=date"
$web = New-Object System.Net.WebClient
$rss = [xml]$web.downloadString($url)
$rss.rss.channel.item | Select Title, Author, Link, Description
}
Posted in powershell, scripts | Tagged , , , | Leave a comment

Find pending updates on local or remote computers

This is more of an upgrade to a vbscript I found to run on all of our servers at work. Originally I used the vbscript along with PSExec to remotely run the file on each remote machine which would then shoot out a log file on the server that would then be copied to a central repository and then compiled into one main log file that could be reviewed.  I admit I was really procrastinating on making this upgrade as I had other things to work on, but knew that this was going to happen eventually.  Fast forward to now and I have finally completed this upgrade.

The biggest key to this script is the use of the following line of code:

$updatesession =  [activator]::CreateInstance([type]::GetTypeFromProgID("Microsoft.Update.Session",$c)) 

This piece of code allows me to create the remote COM object on a remote computer that then allows me to perform the audit of patches that are available to install on that computer.  Unfortunately, this same trick does not work with the installation of the patches as remote installation via the COM object is forbidden.

The [activator] is best defined as:

Contains methods to create types of objects locally or remotely, or obtain references to existing remote objects. This class cannot be inherited.

I then use the CreateInstance() method of that class to then call the COM object using the [type] class with the GetTypeFromProgID() method to create the remote COM object of the Microsoft.Update.Session on the remote computer ($c).

While I am unable to install the updates, at least I can find out what, if any are waiting to be installed on a remote machine. The following line of code shows the syntax of what needs to be typed out and then the following output.

Get-PendingUpdates -Computer DC1,boe-laptop

 

Capture

This output can also be easily exported to a CSV file using Export-CSV. 

In between updating PoshWSUS to version 1.2 and some other side projects, I am also working on a GUI using WPF that uses this code in the backend to provide a nice user interface to work with for my work, but will also of course share this with everyone else as well when completed.

Code

Script Repository

PoshCode

Function Get-PendingUpdates { 
<#    
  .SYNOPSIS   
    Retrieves the updates waiting to be installed from WSUS   
  .DESCRIPTION   
    Retrieves the updates waiting to be installed from WSUS  
  .PARAMETER Computer 
    Computer or computers to find updates for.   
  .EXAMPLE   
   Get-PendingUpdates 
    
   Description 
   ----------- 
   Retrieves the updates that are available to install on the local system 
  .NOTES 
  Author: Boe Prox                                           
  Date Created: 05Mar2011                                           
#> 
      
#Requires -version 2.0   
[CmdletBinding( 
    DefaultParameterSetName = 'computer' 
    )] 
param( 
    [Parameter( 
        Mandatory = $False, 
        ParameterSetName = '', 
        ValueFromPipeline = $True)] 
        [string[]]$Computer               
    )     
Begin { 
    $scriptdir = { Split-Path $MyInvocation.ScriptName -Parent } 
    Write-Verbose "Location of function is: $(&$scriptdir)" 
    $psBoundParameters.GetEnumerator() | ForEach-Object { Write-Verbose "Parameter: $_" } 
    If (!$PSBoundParameters['computer']) { 
        Write-Verbose "No computer name given, using local computername" 
        [string[]]$computer = $Env:Computername 
        } 
    #Create container for Report 
    Write-Verbose "Creating report collection" 
    $report = @()     
    } 
Process { 
    ForEach ($c in $Computer) { 
        Write-Verbose "Computer: $($c)" 
        If (Test-Connection -ComputerName $c -Count 1 -Quiet) { 
            Try { 
            #Create Session COM object 
                Write-Verbose "Creating COM object for WSUS Session" 
                $updatesession =  [activator]::CreateInstance([type]::GetTypeFromProgID("Microsoft.Update.Session",$c)) 
                } 
            Catch { 
                Write-Warning "$($Error[0])" 
                Break 
                } 
 
            #Configure Session COM Object 
            Write-Verbose "Creating COM object for WSUS update Search" 
            $updatesearcher = $updatesession.CreateUpdateSearcher() 
 
            #Configure Searcher object to look for Updates awaiting installation 
            Write-Verbose "Searching for WSUS updates on client" 
            $searchresult = $updatesearcher.Search("IsInstalled=0")     
             
            #Verify if Updates need installed 
            Write-Verbose "Verifing that updates are available to install" 
            If ($searchresult.Updates.Count -gt 0) { 
                #Updates are waiting to be installed 
                Write-Verbose "Found $($searchresult.Updates.Count) update\s!" 
                #Cache the count to make the For loop run faster 
                $count = $searchresult.Updates.Count 
                 
                #Begin iterating through Updates available for installation 
                Write-Verbose "Iterating through list of updates" 
                For ($i=0; $i -lt $Count; $i++) { 
                    #Create object holding update 
                    $update = $searchresult.Updates.Item($i) 
                     
                    #Verify that update has been downloaded 
                    Write-Verbose "Checking to see that update has been downloaded" 
                    If ($update.IsDownLoaded -eq "True") {  
                        Write-Verbose "Auditing updates"   
                        $temp = "" | Select Computer, Title, KB,IsDownloaded 
                        $temp.Computer = $c 
                        $temp.Title = ($update.Title -split('\('))[0] 
                        $temp.KB = (($update.title -split('\('))[1] -split('\)'))[0] 
                        $temp.IsDownloaded = "True" 
                        $report += $temp                
                        } 
                    Else { 
                        Write-Verbose "Update has not been downloaded yet!" 
                        $temp = "" | Select Computer, Title, KB,IsDownloaded 
                        $temp.Computer = $c 
                        $temp.Title = ($update.Title -split('\('))[0] 
                        $temp.KB = (($update.title -split('\('))[1] -split('\)'))[0] 
                        $temp.IsDownloaded = "False" 
                        $report += $temp 
                        } 
                    } 
                 
                } 
            Else { 
                #Nothing to install at this time 
                Write-Verbose "No updates to install." 
                 
                #Create Temp collection for report 
                $temp = "" | Select Computer, Title, KB,IsDownloaded 
                $temp.Computer = $c 
                $temp.Title = "NA" 
                $temp.KB = "NA" 
                $temp.IsDownloaded = "NA" 
                $report += $temp 
                } 
            } 
        Else { 
            #Nothing to install at this time 
            Write-Warning "$($c): Offline" 
             
            #Create Temp collection for report 
            $temp = "" | Select Computer, Title, KB,IsDownloaded 
            $temp.Computer = $c 
            $temp.Title = "NA" 
            $temp.KB = "NA" 
            $temp.IsDownloaded = "NA" 
            $report += $temp             
            } 
        }  
    } 
End { 
    Write-Output $report 
    }     
}
Posted in powershell, scripts, WSUS | Tagged , , | 27 Comments

Retrieve SQL Database and Transaction Log File Sizes using Get-Counter

This is a nice little script that I used to find the size of all of the SQL server database (MDF) and Transaction Log (LDF) files on each SQL server that we have. This stems from a task I had to write a script that could find out just what the sizes are of each database and each Transaction Log file we had on our network.

At first I thought about using the SMO assemblies into PowerShell and then creating my own objects to query each SQL server for the information. But then I thought that not every client in my office has the SQL Administrator tools loaded on each machine in order to run the script.  Instead, I did some research on using performance counters to possibly pull this information.  And with that, I was able to find exactly what I was looking for.  Using the Get-Counter cmdlet, I was able to to query for a select counter and the parse the information to display the file sizes.  Since I am using performance counters, the actual location of the files will not be shown.  But for me, this was OK as all I needed was the sizes.

The Advanced Function that I wrote is Get-SQLFileSize and has an option to query one or more computers and display the MDF, LDF or both types of files along with their size in Megabytes.

Get-SQLFileSize -comp dc1

Computer                      Database                      FileType                                            Size_MB
——–                      ——–                      ——–                                            ——-
dc1                           susdb                         MDF                                                  391.36
dc1                           tempdb                        MDF                                                   8.192
dc1                           msdb                          MDF                                                   4.992
dc1                           model                         MDF                                                   1.216
dc1                           mssqlsystemresource           MDF                                                   39.36
dc1                           master                        MDF                                                   4.096
dc1                           susdb                         LDF                                                   5.176
dc1                           tempdb                        LDF                                                   7.608
dc1                           msdb                          LDF                                                   3.448
dc1                           model                         LDF                                                   0.504
dc1                           mssqlsystemresource           LDF                                                   0.504
dc1                           master                        LDF                                                   1.016

 

You can easily send the output to a CSV file using the Export-CSV cmdlet and send it via email as a report.

Note that there are possibilities where the counters used are inaccessible for one reason or another.  If this is the case, then the function will not work as designed.

Code

Script Repository

PoshCode

Function Get-SQLFileSize { 
<#   
.SYNOPSIS   
    Retrieves the file size of a MDF or LDF file for a SQL Server 
.DESCRIPTION 
    Retrieves the file size of a MDF or LDF file for a SQL Server 
.PARAMETER Computer 
    Computer hosting a SQL Server 
.NOTES   
    Name: Get-SQLFileSize 
    Author: Boe Prox 
    DateCreated: 17Feb2011         
.EXAMPLE   
Get-SQLFileSize -Computer Server1 
 
Description 
----------- 
This command will return both the MDF and LDF file size for each database on Server1 
.EXAMPLE   
Get-SQLFileSize -Computer Server1 -LDF 
 
Description 
----------- 
This command will return LDF file size for each database on Server1 
Description 
----------- 
This command will return both the MDF and LDF file size for each database on Server1 
.EXAMPLE   
Get-SQLFileSize -Computer Server1 -MDF 
 
Description 
----------- 
This command will return MDF file size for each database on Server1 
 
#>  
[cmdletbinding( 
    DefaultParameterSetName = 'Default', 
    ConfirmImpact = 'low' 
)] 
    Param( 
        [Parameter( 
            Mandatory = $True, 
            Position = 0, 
            ParameterSetName = '', 
            ValueFromPipeline = $True)] 
            [string[]]$Computer, 
        [Parameter( 
            Mandatory = $False, 
            Position = 1, 
            ParameterSetName = '', 
            ValueFromPipeline = $False)] 
            [switch]$Mdf, 
        [Parameter( 
            Mandatory = $False, 
            Position = 2, 
            ParameterSetName = '', 
            ValueFromPipeline = $False)] 
            [switch]$Ldf                         
        ) 
Begin { 
    If (!($PSBoundParameters.ContainsKey('Mdf')) -AND !($PSBoundParameters.ContainsKey('Ldf'))) { 
        Write-Verbose "MDF or LDF not selected, scanning for both file types" 
        $FileFlag = $True
        $Flag = $False 
        } 
    #Create holder for data 
    Write-Verbose "Creating holder for data" 
    $report = @()
    } 
Process {     
    ForEach ($comp in $Computer) { 
        #Check for server connection 
        Write-Verbose "Testing server connection" 
        If (Test-Connection -count 1 -comp $comp -quiet) { 
            If ($PSBoundParameters.ContainsKey('Mdf') -OR $FileFlag) { 
                Write-Verbose "Looking for MDF file sizes"  
                    Try { 
                        Write-Verbose "Attempting to retrieve counters from server" 
                        $DBDataFile = Get-Counter -Counter '\SQLServer:Databases(*)\Data File(s) Size (KB)' -MaxSamples 1 -comp $comp -ea stop 
                        $DBDataFile.CounterSamples | % { 
                            If ($_.InstanceName -ne "_total") { 
                                $temp = "" | Select Computer, Database, FileType, Size_MB 
                                $temp.Computer = $comp 
                                $temp.Database = $_.InstanceName 
                                $temp.FileType = 'MDF' 
                                $temp.Size_MB = $_.CookedValue/1000 
                                $report += $temp 
                                } 
                            } 
                        } 
                    Catch { 
                        $Flag = $True 
                        }
                    If ($Flag) {                 
                        Try { 
                            Write-Verbose "Attempting to retrieve counters from server" 
                            $DBDataFile = Get-Counter -Counter '\MSSQL$MICROSOFT##SSEE:Databases(*)\Data File(s) Size (KB)' -MaxSamples 1 -comp $comp -ea stop 
                            $DBDataFile.CounterSamples | % { 
                                If ($_.InstanceName -ne "_total") { 
                                    $temp = "" | Select Computer, Database, FileType, Size_MB 
                                    $temp.Computer = $comp 
                                    $temp.Database = $_.InstanceName 
                                    $temp.FileType = 'MDF' 
                                    $temp.Size_MB = $_.CookedValue/1000 
                                    $report += $temp 
                                    } 
                                }             
                            } 
                        Catch { 
                            Write-Warning "$($Comp): Unable to locate Database Counters or Database does not exist on this server"
                            Break
                            }
                        }
                    Else {
                        Write-Warning "$($Comp): Unable to locate Database Counters or Database does not exist on this server"
                        Break
                        } 
                } 
            If ($PSBoundParameters.ContainsKey('Ldf') -OR $FileFlag) {  
                Write-Verbose "Looking for LDF file sizes"                
                    Try { 
                        Write-Verbose "Attempting to retrieve counters from server" 
                        $DBDataFile = Get-Counter -Counter '\SQLServer:Databases(*)\Log File(s) Size (KB)' -MaxSamples 1 -comp $comp -ea stop 
                        $DBDataFile.CounterSamples | % { 
                            If ($_.InstanceName -ne "_total") { 
                                $temp = "" | Select Computer, Database, FileType, Size_MB 
                                $temp.Computer = $comp 
                                $temp.Database = $_.InstanceName 
                                $temp.FileType = 'LDF' 
                                $temp.Size_MB = $_.CookedValue/1000 
                                $report += $temp 
                                } 
                            } 
                        } 
                    Catch { 
                        $Flag = $True  
                        }
                    If ($flag) {                 
                        Try { 
                            Write-Verbose "Attempting to retrieve counters from server" 
                            $DBDataFile = Get-Counter -Counter '\MSSQL$MICROSOFT##SSEE:Databases(*)\Log File(s) Size (KB)' -MaxSamples 1 -comp $comp -ea stop 
                            $DBDataFile.CounterSamples | % { 
                                If ($_.InstanceName -ne "_total") { 
                                    $temp = "" | Select Computer, Database, FileType, Size_MB 
                                    $temp.Computer = $comp 
                                    $temp.Database = $_.InstanceName 
                                    $temp.FileType = 'LDF' 
                                    $temp.Size_MB = $_.CookedValue/1000 
                                    $report += $temp 
                                    } 
                                }             
                            } 
                        Catch { 
                            Write-Warning "$($Comp): Unable to locate Database Counters or Database does not exist on this server"
                            Break
                            }
                        }
                    Else {
                        Write-Warning "$($Comp): Unable to locate Transaction Log Counters or Database does not exist on this server"
                        Break
                        } 
                    } 
            } 
        Else { 
            Write-Warning "$($Comp) not found!" 
            }                
        }         
    } 
End { 
    Write-Verbose "Displaying output" 
    $report 
    }                 
}
Posted in powershell, scripts | Tagged , , | 3 Comments