How to generate complex AD account metrics part 2

PowerShell Metrics Series: Active Directory Reporting

How to generate and report basic AD account metrics

How to generate complex AD account metrics part 1

How to generate complex AD account metrics part 2

How to store AD metrics in SharePoint 2010

How to present AD metrics in a SharePoint 2010 dashboard

Introduction

In the first posting of this series, we explored the basic process for generating AD metrics and saving them to a file.  In the second posting, we explored generating somewhat more complex metrics.  In this posting, we will conclude this exploration by extracting and categorizing AD account last logons again using the simplest approach.

Obtaining user account last logons provides immediate business value to system administrators, management and customers, as they show infrastructure usage.  Capturing and archiving these values supports trending analysis and more accurate budget planning.

The AD account attribute that will be used is LastLogonDate.  This attribute is a conversion of the LastLogonTimeStamp long integer value into a friendly time format that is formatted according to your local time zone and settings.  The LastLogonTimeStamp attribute is replicated (by default) between 9 to 14 days among the domain controllers and thus LastLogonDate as well.

Review

Here's the script generated thus far:

#####################################
# Name:        AD Report Generator
# Author:      [your name]
# Date:        [date]
# Description: 
#####################################
Import-module ActiveDirectory

# Create the report container
# ---------------------------

# Save the data and time.  This date and time
# will be used also for generating the report
# filename.
$DateTime = Get-Date
# Use this date/time to generate the report file name
$DateTimeFileString = $DateTime.ToString("yyyyMMddHHmmss")
# Now generate the path/file string
$FilePathString = "\\[YourPath]\ADReport_" + $DateTimeFileString + ".txt"
# Create a new text file
New-Item $FilePathString -Type File

# Write the report date/time
# --------------------------

# Add the date/time to the top of the file
[string]$StringToWrite = $DateTime
Set-Content $FilePathString $StringToWrite
# and then add a couple of lines after
Add-Content $FilePathString ""
Add-Content $FilePathString ""

# Add a header to the report
# --------------------------

$StringToWrite = "AD Domain Accounts Report"
Add-Content $FilePathString $StringToWrite
$StringToWrite = "Generated on host " + $env:Computername + " by " + $env:UserName
Add-Content $FilePathString $StringToWrite
Add-Content $FilePathString "====================================================="
Add-Content $FilePathString ""
Add-Content $FilePathString ""
Add-Content $FilePathString ""  

# Build the report
# ----------------

# This next line of code interrogates AD and builds the array 
# that contains all accounts in AD and the desired properties.  
# It effectively generates a list of accounts and their 
# properties that you can parse and filter as needed.  This only 
# needs to be performed once, for the entire report, as all of 
# the rest of the attention will be focused on this array.
[array]$AllAccounts = Get-ADUser -Filter * -Properties Name, Givenname, Surname, DistinguishedName, Enabled, LastLogonDate, LastLogonTimeStamp, LockedOut, msExchHomeServerName, SAMAccountName, CreateTimeStamp, Created, PasswordLastSet, Description # This line gets the total number of accounts and report it.  

# It effectively counts all of the rows in the array.
$StringToWrite = "Total number of AD domain accounts of all types: " + $AllAccounts.Count
Add-Content $FilePathString $StringToWrite
Add-Content $FilePathString ""
 
# This line gets the total number of accounts having Exchange 
# mailboxes. The Where-Object performs all of the complex 
# interaction necessary for filtering the array - you simply 
# need to provide it with the filter parameters.  Note the use 
# of "$_", which is a shorthand reference to the object being 
# filtered.  Note too how Boolean equations are written.  
# Boolean operators are denoted by a hyphen "-".  See the
# References for additional discussion on this notation.
[array]$AllMailboxUsers = $AllAccounts | Where-Object {$_.msExchHomeServerName -NotLike $NULL}
$StringToWrite = "Total number of users who have email accounts: " + $AllMailboxUsers.Count
Add-Content $FilePathString $StringToWrite
Add-Content $FilePathString ""
 
# This line gets the total number of administrative accounts 
# using the # same approach as previous.  As discussed earlier, 
# it assumes that admin accounts are distinguished by having 
# the word "Admin" in their Description field.  Other fields 
# may also be used - if so, use them instead.
[array]$AllAdminAccounts = $AllAccounts | Where-Object {$_.Description -Like '*Admin*'}
$StringToWrite = "Total number of Administrative accounts: " + $AllAdminAccounts.Count
Add-Content $FilePathString $StringToWrite
Add-Content $FilePathString ""

# Get total number of service accounts.  Same approach as 
# previous. Assumes that service accounts are distinguished 
# by having the word "Service" in their Description field.
[array]$AllServiceAccounts = $AllAccounts | Where-Object {$_.Description -Like '*Service*'}
$StringToWrite = "Total number of Service accounts: " + $AllServiceAccounts.Count
Add-Content $FilePathString $StringToWrite
Add-Content $FilePathString ""

# Get total number of accounts used for testing.  Same approach 
# as previous. Assumes that testing accounts are distinguished by 
# having the word "Testing" in their Description field.
[array]$AllTestingAccounts = $AllAccounts | Where-Object {$_.Description -Like '*Testing*'}
$StringToWrite = "Total number of Testing accounts: " + $AllTestingAccounts.Count
Add-Content $FilePathString $StringToWrite
Add-Content $FilePathString ""

# Get total number of end user accounts.  Same approach as 
# previous, but this time the array is filtered for NOT having 
# certain keywords in their their Description field.  
[array]$AllUserAccounts = $AllAccounts | Where-Object {($_.Description -NotLike '*Admin*') -and ($_.Description -NotLike '*Service*') -and ($_.Description -NotLike '*Testing*')}
$StringToWrite = "Total number of User accounts: " + $AllUserAccounts.Count
Add-Content $FilePathString $StringToWrite
Add-Content $FilePathString "" 

# This line gets the total number of user accounts that
# are enabled.
[array]$AllEnabledUserAccounts = $AllUserAccounts | Where-Object {$_.Enabled -eq $True}
$StringToWrite = "Total number of Enabled User accounts: " + $AllEnabledUserAccounts.Count
Add-Content $FilePathString $StringToWrite
Add-Content $FilePathString ""

# This line gets the total number of user accounts that
# are disabled.
[array]$AllDisabledUserAccounts = $AllUserAccounts | Where-Object {$_.Enabled -eq $False}
$StringToWrite = "Total number of Disabled User accounts: " + $AllDisabledUserAccounts.Count
Add-Content $FilePathString $StringToWrite
Add-Content $FilePathString ""

# This line gets the total number of enabled users
# accounts that have been locked out.
[array]$AllEnabledLockedUserAccounts = $AllEnabledUserAccounts | Where-Object {$_.LockedOut -eq $True}
$StringToWrite = "Total number of Enabled User accounts that are locked out: " + $AllEnabledLockedUserAccounts.Count
Add-Content $FilePathString $StringToWrite
Add-Content $FilePathString ""

# This line gets the total number of enabled users
# that have never logged in.
[array]$AllEnabledUserAccountsNeverlogon = $AllEnabledUserAccounts | Where-Object {$_.LastLogonDate -eq $NULL}

$StringToWrite = "Total number of Enabled User accounts that have never logged on: " + $AllEnabledUserAccountsNeverlogon.Count

Add-Content $FilePathString $StringToWrite
Add-Content $FilePathString ""

LastLogon Metrics

Here is the script to be added.  Let's look at the first metric: users who have logged in within the past 365 days.

 # This line gets the number of enabled user accounts that
# have logged in within the past 365 days.  This time, we
# filter the array using a time value, which is obtained
# by subtracting 365 from the variable holding the time
# value obtained at the start of this script.
$365Days = $DateTime.AddDays(-365)
[array]$LastLogon365EnabledUserAccounts = $AllEnabledUserAccounts | Where-Object {$_.LastLogonDate -ge $365Days}
$StringToWrite = "Total number of enabled users who have logged in within the past 365 days: " + $LastLogon365EnabledUserAccounts.Count
Add-Content $FilePathString $StringToWrite
Add-Content $FilePathString "" test  

It follows the same general approach used previously.  Repeat this approach for the other date categories, including: 180, 90, 45, and 30 days.
$180Days = $DateTime.AddDays(-180)
[array]$LastLogon180DaysEnabledUserAccounts = $AllEnabledUserAccounts | Where-Object {$_.LastLogonDate -ge $180Days}
$StringToWrite = "Total number of enabled users who have logged in within the past 180 days: " + $LastLogon180DaysEnabledUserAccounts.Count
Add-Content $FilePathString $StringToWrite
Add-Content $FilePathString ""

$90Days = $DateTime.AddDays(-90)
[array]$LastLogon90DaysEnabledUserAccounts = $AllEnabledUserAccounts | Where-Object {$_.LastLogonDate -ge $90Days}
$StringToWrite = "Total number of enabled users who have logged in within the past 90 days: " + $LastLogon90DaysEnabledUserAccounts.Count
Add-Content $FilePathString $StringToWrite
Add-Content $FilePathString ""

$45Days = $DateTime.AddDays(-45)
[array]$LastLogon45DaysEnabledUserAccounts = $AllEnabledUserAccounts | Where-Object {$_.LastLogonDate -ge $45Days}
$StringToWrite = "Total number of enabled users who have logged in within the past 45 days: " + $LastLogon45DaysEnabledUserAccounts.Count
Add-Content $FilePathString $StringToWrite
Add-Content $FilePathString ""

$30Days = $DateTime.AddDays(-30)
[array]$LastLogon30DaysEnabledUserAccounts = $AllEnabledUserAccounts | Where-Object {$_.LastLogonDate -ge $30Days}
$StringToWrite = "Total number of enabled users who have logged in within the past 30 days: " + $LastLogon30DaysEnabledUserAccounts.Count
Add-Content $FilePathString $StringToWrite
Add-Content $FilePathString ""

$15Days = $DateTime.AddDays(-15)
[array]$LastLogon15DaysEnabledUserAccounts = $AllEnabledUserAccounts | Where-Object {$_.LastLogonDate -ge $15Days}
$StringToWrite = "Total number of enabled users who have logged in within the past 15 days: " + $LastLogon15DaysEnabledUserAccounts.Count
Add-Content $FilePathString $StringToWrite
Add-Content $FilePathString "" 

Summary

This posting, the third in this series, has presented script for extracting additional Active Directory user account metrics.  It uses the LastLogonDate attribute.  The default replication frequency determines the default window of accuracy of LastLogonDate . The default replication frequency works out to be approximately 9-14 days.  This accuracy is good enough for time windows of 30, 60, 90 days, and so on, but is insufficiently accurate when seeking time windows of 1, 2, or 7 days. In a later posting, we'll explore how to improve this accuracy and thus obtain more immediate usage metrics.

References

• TechNet: Ask the Directory Services Team: “The LastLogonTimeStamp Attribute” – “What it was designed for and how it works”
• Windows Dev Center: Last-Logon-Timestamp attribute
• WindowsITPro: Use Get-ADUser to Find Inactive AD Users
• Dmitry’s PowerBlog: How to Schedule a PowerShell Script
• Teusje: Windows Server: logging users logon and logoff via PowerShell