29 March 2017

SCCM Active Directory Old and Corrupt System Reporting Tool

We wanted a comprehensive report of systems to be automatically generated on a monthly basis with the following information:

  • System Name
  • IP Address
  • Last Logon Time Stamp
  • Is it pingable?
  • Is the SCCM client installed
  • Is the SCCM client active
  • Last active time stamp of the SCCM client
I decided to write this script that combines DNS, SCCM, and AD information into one comprehensive report that can be filtered in Excel. 

The script requires three PowerShell modules to work:
  • ConfigurationManager.psd1
  • DNSClient.psd1
  • ActiveDirectory.psd1
On the system you will be executing this script from, RSAT will be required to execute it. 

The script will take a while to execute, depending on how many systems are in your environment. Here is a sample screenshot I took after running the report:

As you can see in the screenshot, the data can be very helpful in finding systems that may be gone, but were not deleted in AD and/or SCCM. It can also show you systems were the client may be busted. The report is generated and placed in the same location as the script. 

While writing this script, I used SAPIEN's PowerShell Studio and must say that it made writing this a breeze. It helps in documenting and making the code much more efficient.

You can download the script from my GitHub site

           System Status Reporting Tool  
           This script will generate a report that pulls from the SCCM All Systems collection, active directory, and the DNS. The script will first get a list of All Systems that is typically populated by active directory. It will then iterate through the list getting the system name from SCCM, IP address from DNS, last logon time stamp from AD, if it is pingable from PowerShell, if the SCCM client is installed, if the client is active, and the last active time of the client. This information is put in a report form and written both to a CSV file and to the display. The report will show systems without the SCCM client, systems that have not been online for a long time, and systems that may have a corrupt client.  
      .PARAMETER SiteCode  
           SCCM site code needed to execute the configuration manager cmdlets.  
      .PARAMETER SCCMModule  
           Path to the configuration manager PowerShell module  
           Created with:     SAPIEN Technologies, Inc., PowerShell Studio 2017 v5.4.136  
           Created on:       3/28/2017 9:54 AM  
           Created by:       Mick Pletcher  
           Filename:         SCCMADReport.ps1  
 function Get-RelativePath {  
           Get the relative path  
           Returns the location of the currently running PowerShell script  
           Additional information about the function.  
      param ()  
      $Path = (split-path $SCRIPT:MyInvocation.MyCommand.Path -parent) + "\"  
      Return $Path  
 Import-Module DnsClient  
 Import-Module ActiveDirectory  
 Import-Module -Name $SCCMModule  
 #Format sitecode  
 If ($SiteCode[$SiteCode.Length - 1] -ne ":") {  
      $SiteCode = $SiteCode + ":"  
 #Get location of current powershell connection  
 $Location = (get-location).Path.Split("\")[0]  
 #Change connection to configuration manager  
 Set-Location $SiteCode  
 #Get list of all systems in SCCM  
 $Systems = Get-CMDevice -CollectionName "All Systems" | Where-Object { $_.Name -notlike "*Unknown Computer*" }  
 #Create Reports array  
 $Report = @()  
 foreach ($System in $Systems) {  
      #Get SCCM info for $System  
      $SCCMSystemInfo = $Systems | Where-Object { $_.Name -eq $System.Name }  
      #Get the last logon timestamp from active directory  
      Try {  
           $LLTS = [datetime]::FromFileTime((get-adcomputer $System.Name -properties LastLogonTimeStamp -ErrorAction Stop).LastLogonTimeStamp).ToString('d MMMM yyyy')  
      } Catch {  
           $Output = $System.Name + " is not in active directory"  
           Write-Output $Output  
      #Test if the system is pingable  
      $Pingable = Test-Connection -ComputerName $System.Name -Count 2 -Quiet  
      #Get the ipaddress for the system  
      Try {  
           $IPAddress = (Resolve-DnsName -Name $System.Name -ErrorAction Stop).IPAddress  
      } Catch {  
           $Output = $System.Name + " IP address cannot be resolved"  
           Write-Output $Output  
      $Object = New-Object -TypeName System.Management.Automation.PSObject  
      $Object | Add-Member -MemberType NoteProperty -Name Name -Value $System.Name  
      $Object | Add-Member -MemberType NoteProperty -Name IPAddress -Value $IPAddress  
      $Object | Add-Member -MemberType NoteProperty -Name ADLastLogon -Value $LLTS  
      $Object | Add-Member -MemberType NoteProperty -Name Pingable -Value $Pingable  
      $Object | Add-Member -MemberType NoteProperty -Name SCCMClient -Value $SCCMSystemInfo.IsClient  
      $Object | Add-Member -MemberType NoteProperty -Name SCCMActive -Value $SCCMSystemInfo.IsActive  
      $Object | Add-Member -MemberType NoteProperty -Name SCCMLastActiveTime -Value $SCCMSystemInfo.LastActiveTime  
      $Report += $Object  
      #Clear variables if they exist so previous data is not used for systems that have null values  
      If ($IPAddress) {  
           Remove-Variable -Name IPAddress -Force  
      If ($LLTS) {  
           Remove-Variable -Name LLTS -Force  
      If ($Pingable) {  
           Remove-Variable -Name Pingable -Force  
      If ($SCCMInfo) {  
           Remove-Variable -Name SCCMInfo -Force  
 #Change connection to local system  
 Set-Location $Location  
 #Sort report by computer name  
 $Report = $Report | Sort-Object -Property Name  
 #Get the path this script is being executed from  
 $RelativePath = Get-RelativePath  
 #Path and filename to write the report to  
 $File = $RelativePath + "SCCMReport.csv"  
 #Delete old report file  
 If ((Test-Path $File) -eq $true) {  
      Remove-Item -Path $File -Force  
 #Write report to CSV file  
 $Report | Export-Csv -Path $File -Encoding UTF8 -Force  
 #Write Report to screen  
 $Report | Format-Table  


Post a Comment