The Surly Admin

Getting User Information

There are about a hundred different scripts out there to get user information, and I wanted to add a new one to the list.  The “quirk” or difference for this one is that I pull in information from Spiceworks.  The idea is you will run this script to get a “snapshot” of a user at that time.

Pulling from other scripts

This script is continuing the theme of pulling snippets of code from other scripts I’ve talked about here.  The final product will be an HTML report, pulling data from Active Directory, Exchange (2007 or 2010) and Spiceworks.  We’re also going to give it the ability to accept input from the pipeline in case you wanted to make some kind of regular report out of this.

First let’s set some initial parameters we’re going to need:

Param (
   [CmdletBinding()]
   [Parameter(
      Mandatory=$true,
      ValueFromPipeline=$true,
      ValueFromPipelineByPropertyName=$true)]
   [Alias("Name","Users")]
   [string[]]$SamAccountName,
   [string]$ExchangeServer = "exchangeserver",
   [ValidateSet("CSV","HTML","GRID")]
   [string]$ReportType = "HTML",
   [string]$PathToSWdb = "<a href="file://\\mfutil1\c$\Program">\\spiceworksserver\c$\Program</a> Files (x86)\Spiceworks\db",
   [string]$TicketAge = "30",
   [string]$ReportPath = "c:\scripts"
)

A lot going on here!  First parameter is required and can come from the pipeline as well as taking 2 alias’ and can be an array of strings!  Next is where we define the Exchange server name, than comes the report type.  This script will actually be able to produce 3 kinds of output, CSV, HTML and an interactive grid window (using Out-Gridview).  Than we define where the Spiceworks database is located and how far back in the database to look for closed tickets.  Last we define where we want to save our reports (either the CSV or HTML reports).

Since we’re  using pipeline we have to have at least one of the Begin/Process/End block (read here) and in this case I’ll be using all three.  First I’ll define the AlternatingCSSClass function from the HTML reporting post, and then have to do some setup work to make sure everything will work.

Exchange Implicit Remoting

One thing I want to do is get some Exchange mailbox information and that means talking to my Exchange 2007 or 2010 server (in my case, it’s Exchange 2010).  Now you can definitely install the Exchange 2010 tools on that workstation and run the commands directly but I like to write my scripts so that they can run on any workstation/server with the minimum of installs/configuration needed.  Since installing Exchange 2010 tools is not an easy task we need another way, and implicit remoting is the answer.  This is similar to using Invoke-Command and running the commands on the remote computer, but instead you import the commands from the remote session into your session.  Than when you execute an Exchange Powershell cmdlet it gets redirected over the remote session and runs on the Exchange server, all without installing anything on your workstation!

To do this you need to first create a PSSession with the Exchange server, which is a little different from doing remoting with most other Windows servers.

$ExchangeSession = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri <a href="http://$ExchangeServer/PowerShell/">http://$ExchangeServer/PowerShell/</a> -ErrorAction Stop

We use New-PSSession to create the remote session and assign it to the variable $ExchangeSession.  Now we need to import the commands:

Import-PSSession $ExchangeSession -ErrorAction Stop

That’s it, all of the specific Exchange cmdlets will now be imported into your machine and you’ll be able to use them in your scripts!  Next I wanted to get the password age in this report, so it was time to borrow from the Password Expiration Report to pull that code out–I should probably write something up about that report, it was kinda cool!  I’ll also want to pull some data out of Spiceworks, which I did write about here.

And that’s the Begin{} block of this script, all setup for what comes in the Process{} block.  To start I want to loop through all the users designated–now if user names are coming from the pipeline it will only come one at a time, but if a string of user names was designated in the parameter than we have to be prepared for that, which is why I setup a ForEach loop here.

ForEach ($User in $SamAccountName)
{  Write-Verbose "`nNow working on $User"
   Write-Verbose "Getting Mailbox information..."
   $MBStats = $null
   $MBStats = Get-MailboxStatistics $User
   If ($MBStats)
   {  $MBServer = $MBStats.Servername
      $MBDB = $MBStats.DatabaseName
      $MBSize = $MBStats.TotalItemSize
   }
   Else
   {  $MBServer = "Unknown"
      $MBDB = "Unknown"
      $MBSize = "0 KB"
   }

Fairly straight-forward after that.  Use Get-MailboxStatistics to get the information I want out of Exchange and store everything into variables, even do a little error checking so if I can’t get information from Exchange for some reason than I’ll report on that, too.  I’ll be assigning the information into an object later once I have everything I need.

The next part is getting some simple Active Directory information, including the age of the password.  Well, that part’s not so simple and too long to post it here but essentially I check to find out the age or status of the password considering the password could be “never set”, blank, set to never expire, etc.  This script is also aware of fine-grained password policies so we have to deal with that.

$TicketStats = $RawTicketData | Where { $_.Email -like "$User*" }
If ($TicketStats)
{  $OpenTickets = $TicketStats.TicketsOpen
   $ClosedTickets = $TicketStats.TicketsClosed
}
Else
{  $OpenTickets = 0
   $ClosedTickets = 0
}

Here I take a look at all of the ticket data I pulled out of Spiceworks, locate the current user in the ForEach loop and store the ticketing information (if any) into more variables.  After that it’s a simple matter of creating our PSObject and storing the data into it.

$Report += New-Object PSObject -Property @{
   'Last Name' = $LastName
   'First Name' = $ADStats.GivenName
   UserName = $ADStats.SamAccountName
   'Last Logon' = $ADStats.LastLogonDate
   'Password Expires' = $Expired
   'User Created' = $ADStats.Created
   'Mailbox Server' = $MBServer
   'Mailbox DB' = $MBDB
   'Mailbox Size' = $MBSize
   'Open Tickets' = $OpenTickets
   'Closed Tickets' = $ClosedTickets
}

Now we’ve finished all of our loops, the pipeline is exhausted and Powershell moves on to the End{} block of our script.  This is where we determine what kind of report was requested, and produce that report:

Switch ($ReportType)
{  "CSV"
   {  $Report | Export-Csv $ReportPath\ADUserInfo.csv -NoTypeInformation
   }
   "HTML"
   {  $Report | ConvertTo-Html -Head $Header -PreContent $Pre -PostContent $Post | Set-AlternatingRows -CSSEvenClass "even" -CSSOddClass "odd" | Out-File "$ReportPath\ADUserInfo.html"
      Invoke-Item "$ReportPath\ADUserInfo.html"
   }
   "GRID"
   {  $Report | Out-GridView
   }
}

Nothing too exciting here, just use the Switch statement to take a look at our $ReportType variable and create the corresponding report/output.  Big scripts like this are much more difficult to break down into great detail without writing entire books but I hope you are at least able to follow the high points in this one.  It’s not the fastest report in the world (by a long shot) and you need to have Exchange and Spiceworks to really take advantage of it, but if you do have those things I think you’ll find it useful.

Download Report-UserInformation Here

April 1, 2013 - Posted by Martin9700 | PowerShell | Active Directory, Exchange, Exchange Implicit Remoting, spiceworks, User Information