Automated Reporting with Powershell

Automated Reporting with Powershell

One of my favorite uses of Powershell is automating things so that I don't have to remember to do them. This includes checking up on my infrastructure to make sure that things are running smoothly. I have a few things I've automated Powershell to do:

Check my VMware environment - I get an email every morning with this report. I'm not going to go through the code on this one, because it's way too long. Basically, I downloaded a superb Powershell script called vCheck and modified it to suit my environment and report on what I need to know. These topics include:

• Hosts and VMs that are down
• VMs that have outdated tools
• VMs that have CD-ROMs or Floppies still attached
• Any other Host issues or alarms
• VM alarms
• vCenter service issues
• Warnings and errors in the event log

You can find vCheck here.

Run a query on a SQL database - This pulls the version information out of a database for a commonly updated application monthly. We have a test environment and a production environment for this particular application. This report helps us keep them both in line.

Filename: Report-VersionSQLQuery.ps1
Author: Charles Stemaly

add-pssnapin SqlServerCmdletSnapin100
add-pssnapin SqlServerProviderSnapin100
 
#Adds the snapins to Powershell for working with SQL. You must have the SQL Management Studio installed on the machine from #which you run this script.

$DividerLine = "`r`n --------------------------------------------------------------- `r`n"
$ReportDate = Get-Date
$ReportDate = $ReportDate.toshortdatestring()
 
#This sections creates some variables, namely the date and a divider line I'll use later to construct my email.

$ProdHeading = "Production Version:"
$ProdVersion = (Invoke-Sqlcmd -Query "SELECT [version], [description], [create_timestamp] FROM [Prod].[dbo].[version] NGDB JOIN (SELECT [product_id], `
MAX([create_timestamp]) as create_date FROM [Prod].[dbo].[version] GROUP BY [product_id]) TBL ON TBL.[product_id] = NGDB.[product_id] `
AND TBL.[create_date] = NGDB.[create_timestamp]" -ServerInstance "ProdServerName" | Out-String)
 
#Prodheading is the heading for my email.
#Now, I'm not a SQL guy, so I needed some help from our DBA to construct the Query above. Once I had the query, I simply had to #use the 'Invoke-SqlCmd' commandlet to send the query off to the SQL server and return the results. One neat thing is that it #returns an object which I can then further manipulate; I can only show the top 2 results or sort by any of its properties using #Powershell, for instance. In this case, I convert it to a string so I can email it later.

$TestHeading = "Test Version:"
$TestVersion = (Invoke-Sqlcmd -Query "SELECT [version], [description], [create_timestamp] FROM [Test].[dbo].[version] NGDB JOIN (SELECT [product_id], `
MAX([create_timestamp]) as create_date FROM [Test].[dbo].[version] GROUP BY [product_id]) TBL ON TBL.[product_id] = NGDB.[product_id] `
AND TBL.[create_date] = NGDB.[create_timestamp]" -ServerInstance "TestServerName" | Out-String)
 
#This is the same command and query as above, modified slightly to pull the information from our test server/database.

$VersionListing = ($ProdHeading + $ProdVersion + $DividerLine + $TestHeading + $TestVersion)
#Here, I am simply "adding" the different strings together to form one big string ($VersionListing)
Send-Mailmessage -from administrator@foo.com -to administrator@foo.com -subject "Versions as of $ReportDate" -smtpserver mailserver -body ($VersionListing)
 
#The subject of my email incorporates the date string I created at the beginning of the script, and uses the $VersionListing #super-string as the body of the email.


Shows me computers in Active Directory not seen on our network in over 90 days - This helps me clean up anything that might not have gotten deleted when we decommission computers.

Filename: Report-ComputerNotSeenIn90Days.ps1
Author: Charles Stemaly

add-pssnapin Quest.ActiveRoles.ADManagement
#Adds the Quest ActiveRoles Snap-in

get-qadcomputer -inactivefor 90 | select name | sort name | ConvertTo-html | out-file c:\olderthan90days.html
$body = get-content c:\olderthan90days.html | Out-String
$body1 = "These computers have not been seen in 90 Days by Active Directory"
 
#Runs a query of computers inactive for 90 days, specifies that I want to see only the name, and sorts them alphabetically. It #then writes the output to an html file, which I convert to a string to use in my email body. I then create another peice of the #body ($body1).

Send-MailMessage -To "administrator@foo.com" -Subject "Computers not seen by AD in 90 Days" -Body ($body1 + $body) -BodyAsHtml -From "administrator@foo.com" -SmtpServer "mailserver.foo.com"
Remove-Item c:\olderthan90days.html
 
#Here I construct my email, and then I delete the html file I created earlier.


Shows me the last date that users logged in - I have this list sorted chronologically so I can see if users aren't using the generic accounts we have floating around, and it also guards against situations where HR has forgotten to tell me that so-and-so isn't working here anymore.

Filename: Report-LastLogon.ps1
Author: Charles Stemaly

Add-PSSnapin Quest.ActiveRoles.ADManagement
$now=get-date
$daysSinceLastLogon=1
Get-QADUser -sizeLimit 0 | where {
  $_.lastlogontimestamp -and
    (($now-$_.lastlogontimestamp).days -gt $daysSinceLastLogon)
} | select-object Name, LastLogonTimeStamp | sort-object LastLogonTimeStamp | convertto-html | out-file c:\report.html
 
#First, I'm loading the Quest ActiveRoles Snap-in, then running a query of my Active Directory users. I'm piping that to 
#'Where-Object' to comb through the result and limit it to only users who have a "lastlogontimestamp" AND where the difference #in days between now and their last logon is greater than 1. I'm then selecting only the name and the last logon timestamp, #sorting it by date so I get the oldest first, converting the output to html and saving the output to an html file.

$body = get-content c:\report.html | out-string
$body2 = "Last time each user's account logged in to Active Directory `r`n`r`n"
Send-Mailmessage -from administrator@foo.com -to administrator@foo.com -subject "Account Info - Last Login" -smtpserver mailserver -body ($body2 + $body) -bodyasHTML
del c:\report.html

#I've constructed the body of my email, sent it out, and then deleted the temp file I created earlier. The `r`n characters you #see are escaped characters within the string. Escaped characters perform some specific function; these create a carriage return #and a new line, breaking up my string so it looks better in the email.


You may have noticed that I'm not consistent in working with strings and you might know that if I looked into things deeper I probably wouldn't have to juggle between creating html files and then converting them to strings later. Formatting output is pretty tricky for me in Powershell, I must say. Some things I tried that should have worked made a mess of the final product (the email) so I just played with different things until they worked. If it was important, I could look up the "best way" to do this, but I have users to help and a network to maintain. As in most things in IT, there are multiple ways to get to the information you want.

Lest ye think that this is a Powershell only blog, on Monday I'm going to talk about my computer setup at home and how I back it up, which is kind of non-conventional.