Multiple Machine Logon Reporting

This tool is designed to report if a user profile has been logged into a defined number of machines or greater over a specified period of time. The purpose of the tool is to:

• Detect a cybercriminal who may have gotten one user credential and is now using it to probe machines across the domain
• Detect a disgruntled employee who may be logging into multiple machines for destructive reasons

The tool works by querying the ConfigMgr SQL server for a list of machines users have logged into. This list comes from the primary devices association. Every user that logs into a machine is added to this list. The list that appears in the GUI of ConfigMgr does not display every login that is stored in the SQL database. I am not sure of the timeframe in which systems disappear from the GUI list. 

If you are interested in just the SQL Query, here it is. The only things that should need to be changed are the -14 and 4. The -14 is the number of days back you want the report to be run and the 4 is the number of machines a user can log in to without appearing on the report.

 SELECT UniqueUserName, COUNT(UniqueUserName) as Logins FROM dbo.v_UserMachineRelationship WHERE dbo.v_UserMachineRelationship.CreationTime >= DATEADD(day,-14, GETDATE()) GROUP BY UniqueUserName HAVING (COUNT(UniqueUserName) > 4) ORDER BY Logins DESC  

This is the PowerShell script that connects to the SQL server to retrieve the list. The script creates a simple list of users and the number of machines they logged into and outputs that in pure text. This can be used in Microsoft Orchestrator or Azure Automation to automatically generate a report. It also generates a detailed report in a CSV file that can be attached to the same automatic email if the recipients want to look at the details that show every machine logged into along with the date stamp. If there are no users to report, the script deletes the last saved CSV file and exits with an error code 1. The purpose of error code one was for the Orchestrator link. The link checks the return code and only proceeds to the email activity if there was an error code 0 returned. As a note for Orchestrator, I have the script run on the SQL server so the PowerShell cmdlets are present. 

 <#  
      .SYNOPSIS  
           SCCM Endpoint Report  
      .DESCRIPTION  
           This script is intended to be used to detect if a user logs into multiple machines within a designated time period, which can be a sign of an intruder.This script will query Configuration Manager using the designated Number of machines and how many days back for a list of machines that have logged into more than the designated number. It will return a list of machines using Write-Output so the output can also be used in Orchestrator or SMA. The list it returns is a simple list that displays the username and the number of machines it was logged into. There is also a more detailed list generated that contains the list of all machines it was logged into. This list can be attached to an email to be sent with the simple list if desired.  
      .PARAMETER SQLServer  
           Name of the SQL server  
      .PARAMETER SQLDatabase  
           Name of the SQL database  
      .PARAMETER NumberOfMachines  
           Maximum number of machines a user can login to before appearing on this report  
      .PARAMETER NumberOfDays  
           This is a string value because it requires a negative value for the number of days you want to report to go back.  
      .PARAMETER Report  
           This is the specified name of the detailed user login report to be generated. This must include both the full path and .csv as part of the name.  
      .NOTES  
           ===========================================================================  
           Created with:     SAPIEN Technologies, Inc., PowerShell Studio 2017 v5.4.142  
           Created on:       8/19/2020 3:58 PM  
           Created by:       Mick Pletcher  
           Filename:         UserLoginReport.ps1  
           ===========================================================================  
 #>  
 [CmdletBinding()]  
 param  
 (  
      [ValidateNotNullOrEmpty()]  
      [string]$SQLServer,  
      [ValidateNotNullOrEmpty()]  
      [string]$SQLDatabase,  
      [ValidateNotNullOrEmpty()]  
      [int]$NumberOfMachines,  
      [ValidateNotNullOrEmpty()]  
      [string]$NumberOfDays,  
      [ValidateNotNullOrEmpty()]  
      [string]$Report = 'c:\UserLoginDetailedReport.csv'  
 )  
 $SimpleReport = Invoke-Sqlcmd -ServerInstance $SQLServer -Database $SQLDatabase -Query ("SELECT UniqueUserName, COUNT(UniqueUserName) as Logins FROM dbo.v_UserMachineRelationship WHERE dbo.v_UserMachineRelationship.CreationTime >= DATEADD(day," + $NumberOfDays + ", GETDATE()) GROUP BY UniqueUserName HAVING (COUNT(UniqueUserName) > " + $NumberOfMachines + ") ORDER BY Logins DESC")  
 If ($SimpleReport -ne $null) {  
      $DetailedReport = @()  
      foreach ($User in $SimpleReport.UniqueUserName) {  
           $DetailedReport += Invoke-Sqlcmd -ServerInstance $SQLServer -Database $SQLDatabase -Query ("SELECT UniqueUserName, MachineResourceName, CreationTime FROM dbo.v_UserMachineRelationship WHERE UniqueUserName = " + [char]39 + $User.Trim() + [char]39 + " AND dbo.v_UserMachineRelationship.CreationTime >= DATEADD(day," + $NumberOfDays + ", GETDATE()) ORDER BY CreationTime ASC")  
      }  
      $DetailedReport | Export-Csv -Path $Report -Force -Encoding UTF8 -NoTypeInformation  
      Write-Output $SimpleReport  
 } else {  
      Remove-Item -Path $Report -Force -ErrorAction SilentlyContinue  
      Exit 1  
 }