SCCM Inactive Systems Report

I have wanted a PowerShell script that would generate an excel report listing all of the inactive systems, if they are in active directory, and the last time someone logged onto the system if it is in AD. As we all know, it is easy to overlook these systems and they can accumulate, even if the cleanup is setup in SCCM. I chose to use SCCM as the definitive report because it pulls it's initial listing of systems from AD. This script has to be executed on the SCCM server.

I have the script require two parameters: 1) OutputFile and 2) Path. The output file is the name of the CSV file and the Path is the location for the script to write the CSV file to. I have prepopulated the OutputFile parameter, but you can change that if necessary.

The next thing will be that you will need to find your collection ID of your Client Activity: Inactive collection and update that on line 40. If you do not have this collection, you will need to create a collection listing all inactive systems and use the collection ID assigned to it.

Now find out where ConfigurationManager.psd1 is located on your SCCM server and update line 94 with the full path to the module.

The SCCM server needs the PowerShell active directory module feature enabled for this script to function. It is located under RSAT in the Windows Features.

Finally, you will need to execute this script on the SCCM server. I have not found any way around this. I have this script setup in Microsoft Orchestrator that uses PSEXEC.EXE to execute the PowerShell script locally on the SCCM server on a weekly basis. It works flawlessly. Again, this script is running in my company's environment, which will largely differ from others. You will need to modify this script to get it to work in your environment.

You can download the script from here.

1:  <#  
2:            ===========================================================================  
3:            Created with:     SAPIEN Technologies, Inc., PowerShell Studio 2015 v4.2.91  
4:            Created on:       8/13/2015 1:36 PM  
5:            Created by:       Mick Pletcher  
6:            Organization:  
7:            Filename:         InactiveSCCMSystemsReport.ps1  
8:            Description:      This script will retrieve the SCCM inactive systems  
9:                              collection and search active directory to see if it   
10:                             exists there. If so, it will retrieve the last   
11:                             logon date and generate a UTF-8 formatted csv file.  
12:    
13:                             The powershell active directory module will need to be  
14:                             enabled on the SCCM server in order for this script to   
15:                             work correctly. This script will also need to be executed  
16:                             on the SCCM server. You will also need to find the location  
17:                             of ConfigurationManager.psd1 module to import.   
18:            ===========================================================================  
19:  #>  
20:  param  
21:  (  
22:       [string]  
23:       $OutputFile = 'InactiveSCCMSystemsReport.csv',  
24:       [string]  
25:       $Path  
26:  )  
27:  Import-Module ActiveDirectory  
28:    
29:    
30:  function ProcessTextFile {  
31:       If ((Test-Path -Path $OutputFile) -eq $true) {  
32:            Remove-Item -Path $OutputFile -Force  
33:       }  
34:  }  
35:    
36:  function Get-SCCMInactiveSystems {  
37:       #Declare Local Variables  
38:       Set-Variable -Name Systems -Scope Local -Force  
39:         
40:       $Systems = get-cmdevice -collectionid "BNA00093" | select name | Sort-Object Name  
41:       Return $Systems  
42:         
43:       #Cleanup Local Variables  
44:       Remove-Variable -Name Systems -Scope Local -Force  
45:  }  
46:    
47:  function Find-SCCMInactiveSystemInAD {  
48:       param ([string]  
49:            $System)  
50:         
51:       #Declare Local Variables  
52:       Set-Variable -Name AD -Scope Local -Force  
53:       $ErrorActionPreference = 'SilentlyContinue'  
54:       $AD = Get-ADComputer $System  
55:       $ErrorActionPreference = 'Continue'  
56:       if ($AD -ne $null) {  
57:            Return "X"  
58:       } else {  
59:            Return " "       
60:       }  
61:         
62:       #Cleanup Local Variables  
63:       Remove-Variable -Name AD -Scope Local -Force  
64:  }  
65:    
66:  function Get-LastLogonDate {  
67:       param ([string]  
68:            $System)  
69:         
70:       #Declare Local Variables  
71:       Set-Variable -Name AD -Scope Local -Force  
72:         
73:       $AD = Get-ADComputer $System -ErrorAction SilentlyContinue  
74:       $AD = $AD.SamAccountName  
75:       $AD = $AD.Substring(0, $AD.Length - 1)  
76:       $AD = Get-ADComputer -Identity $AD -Properties *  
77:       $AD = $AD.LastLogonDate  
78:       Return $AD  
79:              
80:       #Cleanup Local Variables  
81:       Remove-Variable -Name AD -Scope Local -Force  
82:  }  
83:    
84:  #Declare Variables  
85:  Set-Variable -Name ADEntry -Scope Local -Force  
86:  Set-Variable -Name Counter -Value 1 -Scope Local -Force  
87:  Set-Variable -Name LastLogon -Scope Local -Force  
88:  Set-Variable -Name Output -Scope Local -Force  
89:  Set-Variable -Name SCCMInactiveSystems -Scope Local -Force  
90:  Set-Variable -Name System -Scope Local -Force  
91:    
92:  cls  
93:  Import-Module -Name ActiveDirectory  
94:  Import-Module "D:\Program Files\Microsoft Configuration Manager\AdminConsole\bin\ConfigurationManager.psd1" -Force -Scope Global  
95:  Set-Location BNA:  
96:  $SCCMInactiveSystems = Get-SCCMInactiveSystems  
97:  Set-Location c:  
98:  $OutputFile = $Path + "\" + $OutputFile  
99:  ProcessTextFile  
100:  $Output = "Computer Name" + [char]44+"Active Directory"+[char]44+"Last Logon"  
101:  Out-File -FilePath $OutputFile -InputObject $Output -Force -Encoding UTF8  
102:  foreach ($System in $SCCMInactiveSystems) {  
103:       cls  
104:       $Output = "Processing "+$System.Name+" -- "+$Counter+" of "+$SCCMInactiveSystems.Count  
105:       Write-Host $Output  
106:       $Counter++  
107:       $ADEntry = Find-SCCMInactiveSystemInAD -System $System.Name  
108:       If ($ADEntry -ne " ") {  
109:            $LastLogon = Get-LastLogonDate -System $System.Name  
110:       }  
111:       $Output = $System.Name+[char]44+$ADEntry+[char]44+$LastLogon  
112:       Out-File -FilePath $Global:OutputFile -InputObject $Output -Append -Force -Encoding UTF8  
113:       $ADEntry = $null  
114:       $LastLogon = $null  
115:       $Output = $null  
116:  }  
117:    
118:  #Cleanup Variables  
119:  Remove-Variable -Name ADEntry -Scope Local -Force  
120:  Remove-Variable -Name Counter -Scope Local -Force  
121:  Remove-Variable -Name LastLogon -Scope Local -Force  
122:  Remove-Variable -Name Output -Scope Local -Force  
123:  Remove-Variable -Name SCCMInactiveSystems -Scope Local -Force  
124:  Remove-Variable -Name System -Scope Local -Force  
125: