Export DHCP leases to html using powershell

So I was in a need of having the active leases in my DHCP server exported to an web (html) page:

On the surface this would not present a problem, however as there is only one way to read the DHCP leases, netsh.exe, and the output format of this app is somewhat … I also got the chance to to play with regular expressions (love those) and <string> –replace

Just so everyone can share the pain, this is the netsh command:

netsh dhcp server 1.1.1.1 scope 1.1.1.0 show clients 1

And this is the output:

Changed the current scope context to 1.1.1.0 scope.

Type : N - NONE, D - DHCP B - BOOTP, U - UNSPECIFIED, R - RESERVATION IP
============================================================================================
IP Address      - Subnet Mask    - Unique ID           - Lease Expires        -Type -Name
============================================================================================

1.1.1.5     - 255.255.0.0    - 00-ff-ff-ff-ff-ff   -10/13/2009 12:33:16 AM  -D-  host01.domain.com
:::
::: Lot of lines here
:::
1.1.1.227   - 255.255.0.0    - 00-ff-ff-ff-ff-ff   -10/13/2009 12:42:06 AM  -D-  host10.domain.com

No of Clients(version 4): 355 in the Scope : 1.1.1.0.

Command completed successfully.

So is there any logic here? Double tabs, single tabs from which I can split? No. only spaces…. and hyphens.. and useless data

As I only needed the IP and hostnames in a html table, this is what I ended up with in powershell:

$a = (netsh dhcp server 1.1.1.1 scope 1.1.1.0 show clients 1)

$lines = @()
#start by looking for lines where there is both IP and MAC present:
foreach ($i in $a){
    if ($i -match "\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}"){
        If ($i -match "[0-9a-f]{2}[:-][0-9a-f]{2}[:-][0-9a-f]{2}[:-][0-9a-f]{2}[:-][0-9a-f]{2}[:-][0-9a-f]{2}"){
            $lines += $i.Trim()
        }
    }
}
$csvfile = @()
#Trim the lines for uneeded stuff, leaving only IP, Subnet mask and hostname.
foreach ($l in $lines){
    $Row = "" | select Hostname,IP
    $l = $l -replace '[0-9a-f]{2}[:-][0-9a-f]{2}[:-][0-9a-f]{2}[:-][0-9a-f]{2}[:-][0-9a-f]{2}[:-][0-9a-f]{2}', ''
    $l = $l -replace ' - ',','
    $l = $l -replace '\s{4,}',''
    $l = $l -replace '--','-'
    $l = $l -replace '-D-','-'
    $l = $l -replace '[-]{1}\d{2}[/]\d{2}[/]\d{4}',''
    $l = $l -replace '\d{1,2}[:]\d{2}[:]\d{2}',''
    $l = $l -replace 'AM',''
    $l = $l -replace 'PM',''
    $l = $l -replace '\s{1}',''
    $l = $l + "`n"
    $l = $l -replace '[,][-]',','
    $Row.IP = ($l.Split(","))[0]
    #Subnet mask not used, but maybe in a later version, so let's leave it in there:
    #$Row.SubNetMask = ($l.Split(","))[1]
    $Row.Hostname = ($l.Split(","))[2]
    $csvfile += $Row
}

#let create a csv file, in case we need i later..
$csvfile | sort-object Hostname | Export-Csv "Out_List.csv"

#Create the HTML formating
$a = "<style>"
$a = $a + "body {margin: 10px; width: 600px; font-family:arial; font-size: 12px;}"
$a = $a + "TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}"
$a = $a + "TH{border-width: 1px;padding: 2px;border-style: solid;border-color: black;background-color: rgb(179,179,179);align='left';}"
$a = $a + "TD{border-width: 1px;padding: 2px;border-style: solid;border-color: black;background-color: white;}"
$a = $a + "</style>"

#And create HTML file...
Write-Host "Please contact theadmin@void.null for support" | Out-File "DHCPLeases.html"
$csvfile | sort-object Hostname | ConvertTo-HTML -head $a | Out-File -Append "DHCPLeases.html"

The resulting output, then looks like this:

HostName	IP
host01.domain.com	1.1.1.5
host10.domain.com	1.1.1.227

which is what I wanted. Another great job by powershell.

I hope that this might prove useful to someone else, and a my thanks goes out to the powershell community and all the people who might recognize some of the code, no one mentioned, no one forgotten.

Please do drop a line if you have comments or suggestions to how the script could be optimized. Life is a learning curve and I love climbing

Powershell port ping function

As a variation of my previously posted Portscan with powershell i wrote the following function

Function Port-Ping {
    param([Array]$hostlist,[Array]$ports,[Int]$timeout = "50")
    $ErrorActionPreference = "SilentlyContinue"
    $ping = new-object System.Net.NetworkInformation.Ping
    foreach ($ip in $hostlist) {
        $rslt = $ping.send($ip,$timeout)
        if (! $?){
            Write-Host "Host: $ip - not found" -ForegroundColor Red
        }
        else {
            if ($rslt.status.tostring() –eq “Success”) {
                write-host "Host: $ip - Ports: " -foregroundColor Green -NoNewline
                foreach ($port in $ports){
                    $socket = new-object System.Net.Sockets.TcpClient($ip, $port)
                    if ($socket –eq $null) {
                        write-host "$port," -ForegroundColor Red -NoNewline
                    }
                    else {
                        write-host "$port,"-foregroundcolor Green -NoNewline
                        $socket = $null
                    }
                }
            Write-Host
            }
            else {
                write-host "Host: $ip - down" -ForegroundColor Red
            }
        }
    }
    Write-Host ""
    $ping = $null
}

This function is great when you need a fast overview of servers and the ports they have open. In my case I needed to check 3 ports on 100+ servers. The output of this function is color-coded  as the previous script (hence not suitable for piping)

The functions usage is like this:

PS> .\PortPing.ps1 <server> <port> <timeout>

Alternatively an array of servers can be created and used with the function:

Say you need to find whether all AD computer objects in a particular OU are alive and responding to port 3389 (Remote Desktop)

(I’m using Quest Download ActiveRoles Management Shell for Active Directory in this example)

PS> Get-QADComputer -service <Domain> -SearchRoot ‘<Domain/OU/OU>’ | %{.\PortPing.ps1 $_.Name 3389 100}

Inspired by Jeffery Hicks, I added the function to a dot source file, which is loaded with my PowerShell profile, which would then have this usage:

PS> Get-QADComputer -service <Domain> -SearchRoot ‘<Domain/OU/OU>’ | %{Port-Ping $_.Name 3389 100}

As a server administrator, this function gives me the rapid overview that I need to check multiple servers and their ports.

 

Regards

The Admin Guy

Run MBSA against all machines in WSUS

As a WSUS administrator, I often find the various views and reports frustrating.

Say you have a number of machines, which you want to see the status of from a OS security perspective, but you are not interested in the various other MS products (office, Exchange etc.) – No joy.

WSUS will show the machines overall status including everything. Handing report based on that, over to others will surely guarantee that all sorts of time will have to spent explaining all sorts of stuff.

Now, I’m sure that some may say that all the information can be gathered from the SQL database in one form or another, in that case, please let me know

From another perspective, the WSUS also acts like a good database of machines in scope. It provides various key information about the environment, which can surely be used for something..

The result of the above was a powershell script to get all the machines in WSUS and perform an MBSA scan against all of them.

The MBSA scan’s have the nice, self-explanatory reports and as a bonus they are also able to check for other things than patch status.

The script:

#We need a date to append to the log files
$date = (Get-Date).toshortdatestring()

#Connect to WSUS, if needed load assembly
$wsusrv = [Microsoft.UpdateServices.Administration.AdminProxy]::GetUpdateServer()
If (! $?){
    [Reflection.Assembly]::LoadWithPartialName("Microsoft.UpdateServices.Administration")
    $wsusrv = [Microsoft.UpdateServices.Administration.AdminProxy]::GetUpdateServer()
    }

#Get all machines in WSUS
$wsusrv.getcomputertargets() | select FullDomainName,IPAddress,RequestedTargetGroupName `
    | tee -Variable wsustargets | Export-Csv -NoTypeInformation "$pwd\WSUSTargets-$date.csv"

#Part to remove certain machines which we cannot scan
#(like machines with different permissions than the bulk)
foreach ($target in $wsustargets){
    if ($target.RequestedTargetGroupName -eq "SomeFolder"){
        }
    ElseIf ($target.RequestedTargetGroupName -like "*SomeOtherFolder*"){
        }
    Else {
        #create array of machines to be scanned
        [Array]$MBSAList += $target.IPAddress
        $MBSAList | Out-File "$pwd\MBSATargets-$date.csv"
        }
}

#execute MBSA against the array created. This array must have been loaded to a file
$mbsacli = "C:\Program Files\Microsoft Baseline Security Analyzer 2\mbsacli.exe"
$cmdline = "/listfile " + '"' + "$pwd\MBSATargets-$date.csv" + '"' + `
    " /n OS+SQL+IIS+Password /nvc /wa /o " + '"%d% - %c% - %IP% (%t%)"' + `
    " /qe /qr /rd D:\MBSA_scan_auto"
$cmdline
$process = [System.Diagnostics.Process]::Start($mbsacli,$cmdline)
$process.WaitForExit()

Now, very off character for me, I have added some comments in the script, but it is fairly straight forward.

A list of machines is gathered from the WSUS server and stuffed in a file. The MBSAcli.exe utility is then executed using that file. It is of course possible to execute the MBSAcli.exe using the powershell array:

$mbsacli = "C:\Program Files\Microsoft Baseline Security Analyzer 2\mbsacli.exe"
foreach ($target in $MBSAList){
    $cmdline = "/target $target /n OS+SQL+IIS+Password /nvc /wa /o " + `
        '"%d% - %c% - %IP% (%t%)"' + " /qe /qr /rd D:\MBSA_scan_auto"
    $process = [System.Diagnostics.Process]::Start($mbsacli,$cmdline)
    $process.WaitForExit()
}

But the load of MBSA before each scan will lengthen the scan and will consume more resources (it’s not friendly as it is )

But of course there will be machines which cannot be scanned for whatever reason. Above I excluded the once I was sure would fail (due to other permissions) , but some machines may be offline, some with special permissions may hide somewhere etc.

So in order to get an overview of which machines that do not have a report, I did the following script:

param($mbsareportstore = "SomeFolder")

#Connect to WSUS, if needed load assembly
$wsusrv = [Microsoft.UpdateServices.Administration.AdminProxy]::GetUpdateServer()
If (! $?){
    [Reflection.Assembly]::LoadWithPartialName("Microsoft.UpdateServices.Administration")
    $wsusrv = [Microsoft.UpdateServices.Administration.AdminProxy]::GetUpdateServer()
}

#Get all machines in WSUS
$wsusrv.getcomputertargets() | select FullDomainName,IPAddress |tee -Variable wsustargets `
    | Export-Csv -NoTypeInformation "$pwd\NewWSUSTargets-$date.csv"

#Get all reports in the scanfile store
(Get-ChildItem $mbsareportstore | select name | tee -Variable reports)

#Create the status csv file
"Machine,Status,Reportname" | out-file "$PWD\FindReportStatus.txt"

#Do something...
foreach ($target in $wsustargets){

        #because the targets are reurned from WSUS in FQDN format, but reports are stored
        #with hostname the domain name is cut of.
        $b = ($target.FullDomainName.ToString()).Split(".")
        $c = $b[0]

        #Using Select-string to look for the hostname ($c) in each of the filenames
        $reports | % {select-String -pattern $c -inputobject $_ | tee -variable slct}

        #If the hostname is not found in any filename, this is logged in the status file
        #and added to a new targetfile for MBSA to use
        #Write-Host lines can be removed if script is not executed interactively
        if ($slct -eq $null){
            Write-Host "Report Not found for " $target.FullDomainName -foregroundcolor red
            "$c,ReportNotFound,N/A" | out-file -append "$PWD\FindReportStatus.txt"
            $target.FullDomainName | Out-File -Append "$PWD\MBSAScanMissing.txt"
        }
        Else {
            Write-Host "Report found for " $target.FullDomainName -foregroundcolor green
            "$c,ReportFound,$slct" | out-file -append "$PWD\FindReportStatus.txt"
            $slct = $Null
        }
}

This script simply gets the list from WSUS and checks whether a report of each machine exists in the report store.

The 2 scripts could be fused into one, but for my purposes, where I need to run script 1 on a regular basis and script 2 on demand, this is the best way.

Portscan with Powershell

[Update 16-05-2009 – Format update]

So I found myself in need of a tool which could check whether or not one or more ports are open on a large amount of servers.

I ended up with this in powershell:

param([string]$list1,[string]$list2)
if ($list1 -eq ""){
    Write-Host "Please supply Host-list!!" -ForegroundColor Red
    break
    }
If ($list2 -eq ""){
    Write-Host "Please supply Port-List!!" -ForegroundColor Red
    break
    }
[Array]$hostlist = Get-Content $list1
[Array]$ports = Get-Content $list2
$ErrorActionPreference = "SilentlyContinue"
$ping = new-object System.Net.NetworkInformation.Ping
foreach ($ip in $hostlist) {
    $rslt = $ping.send($ip)
    if (! $?){
        Write-Host "Host: $ip - not found" -ForegroundColor Red
    }
    else {
        if ($rslt.status.tostring() –eq “Success”) {
            write-host "Host: $ip - Ports: " -foregroundColor Green -NoNewline
            foreach ($port in $ports){
                $socket = new-object System.Net.Sockets.TcpClient($ip, $port)
                if ($socket –eq $null) {
                    write-host "$port," -ForegroundColor Red -NoNewline
                }
                else {
                    write-host "$port,"-foregroundcolor Green -NoNewline
                    $socket = $null
                }
            }
        }
        else {
            write-host "Host: $ip - down" -ForegroundColor Red
        }
    }
Write-Host ""
}
$ping = $null

The script is executed in the following manner:

[ ] PS> .\script.ps1 hostlist.txt portlist.txt

In this version, the output of the script is not suited to be piped to a file, as port status is indicated with color.

Hello world!

Welcome to a blog dedicated to the impatient System Administrators – the admins who want it all done yesterday, so we can move on to the interesting stuff

Scripts are the key to the game, but all shortcuts, tips and tricks which can raise the speed of the game will also be here