Share via

SharePoint: Generate and report AD account metrics in SharePoint Part 1: How to get basic AD metrics

  • Article

Background

In this series of postings, you will learn how to generate metrics and reports on your domain accounts and present them in SharePoint-based dashboard. No expensive commercial tools are needed. Just PowerShell, Active Directory and SharePoint. This is a low-cost approach to generating and reporting on user metrics for your environment when budgets are tight. Active Directory has an excellent API, the ActiveDirectory module, that exposes all of Active Directory data and methods through .NET in simple and easy-to-understand language. The SharePoint Server codebase rests on the .NET framework and it also has an API that exposes remarkably simple methods and properties, in simple language, that you can use to interact with SharePoint. Integrating these two is PowerShell, which interacts directly with the .NET framework and thus provides one method for integrating software tools exposed to .NET, such as Active Directory, with tools based upon the .NET framework, such as SharePoint Server. Throughout this series, there will be nothing to buy, and nothing extraordinary to learn or assemble: just your time and effort is all that's needed.

One note: this was originally developed on SharePoint 2010 but the SharePoint API commands presented here remain the same for SharePoint 2013 and 2016.

Article Series:

Introduction

In this particular posting, the first of the series, you will learn how to use the following key objects, methods and properties:

  • ActiveDirectory module: the .NET API that exposes Active Directory data;
  • Get-ADUser: the method that extracts data from Active Directory;
  • Set-Content: the method that creates a text file for storing analysis results;
  • Add-Content: the method that appends data to the text file;
  • Where-Object: a property/method object that enables arrays to be parsed;
  • $env: the system variable containing current computer and user information;
  • Arrays: PowerShell arrays for interim results storage.

See the references for more detailed information on these items. You will use these objects, methods and properties to accomplish the following tasks:

  • Connect to Active Directory on the domain controller and extract all account data into an array;
  • Analyze that array and extract counts for:
    • Total number of domain accounts
    • Total number of domain accounts having mail boxes
    • Total number of Admin accounts
    • Total number of Service accounts
    • Total number of Testing accounts
    • Total number of User accounts

You will need to coordinate with your management and network system administrators and their leads in order to identify the following:

  • All domains and their relative trust relationships
  • Names of all hosts functioning as domain controllers and their ranking
  • Replication frequency among the DCs (default is 15s for Windows 2k8)
  • ActiveDirectory module availability on DCs
  • Your access permissions to these DCs (remoting, scripting, etc)
  • Names of all Active Directory groups
  • All AD account properties currently employed
  • User account policies
  • Method used to distinguish domain account types (Description text, username modifications, etc) and their level of diligence in applying this method

In this particular posting, it is assumed that you will run the script below locally on a domain controller through remote desktop session, but this can just as easily be done through remote PowerShell scripting sessions, which will be explored later. Latency among your domain controller replication will determine the accuracy of the report generated by this script.

The approach used here rests upon text parsing of the Description field and is the simplest approach of all. Other, perhaps more sophisticated approaches are possible. For example, using the Get-ADGroup method you can immediately obtain groups of accounts by their memberships. However, while this approach will obtain that group of users who are admins, it will not work for finding testing and service accounts, unless your sys admins have created special AD groups for these types of accounts. Additionally, some care is needed with regard to this method in that individual accounts may be members of more than one group and thus skew the resultant metrics. Coordinate with your system administrators to gain an understanding of how they categorize and group accounts, and then build a list of AD groups. If they have diligently implementing a grouping scheme, then this approach is viable; however more complex coding will be needed.

To use the script below, just copy and past the different parts into a single text file with file ending ps1, then edit it to suit your needs and run on the domain controller or remotely from your workstation via remote session.

I. Prepare the Report

Below is the script used to create a new report container (text file) and add a header to it. Note that the new text file is referenced at any time by its path and name. Note too that the Add-Content method automatically writes to a new line - there's no need to add a line feed to your string. This script assumes that your different user types (admin, service, test, user, etc) are distinguished through appropriate keywords in their Description fields and that the Description field is diligently and regularly maintained. It parses the Description field for keywords. Other approaches to distinguishing among user types can also be scripted, such as prepending or appending a string to account names ("svc", "adm", etc), or other such method, such as the Get-ADGroup approach discussed above. Anyway, to customize this report to your needs, just review the code below and explore different strings to output what you want.

#####################################
# 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 ""

II. Get the Data

Below is the script you'll need to get the data from Active Directory.

# 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

III. Parse the Data for Totals

The last bit of script below extracts metrics from the data:

# 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 filter 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 ""

Summary

In this posting, you have learned how to pull account data from Active Directory and load it into an array; analyze the data in that array, and then write the analysis results to a file. The next posting builds upon the work performed thus far, by exploring how to generate counts on other important user metrics, such as Enabled, LockedOut, LastLogonDate, etc, and adding it to the current script.

References

Notes

  • This approach began as a challenge by a customer with a very limited budget to build a user metrics dashboard that used nothing more than the customer's existing IT resources and capabilities.