Computer Login Count AD script

[supportman]

I've just been asked to find the least most used computers in school, thought it was a cool PowerShell 1 liner to try on the domain controller:

 

Get-ADComputer -Filter * -Properties logonCount -Server YOURDCNAME | Select sAMAccountName, logonCount  | Sort-Object -Property logonCount

 

If you have more than one domain controller it might be worth also running it on the other ones for an accurate count.

 

Essentially this should output a sorted list of domain computers by logincount.

 

Enjoy!

[machy]

Its Worth noting that 'logonCount' has a max Value of just over 65k per Domain Controller.

Which is not that many in a trust, but if you only have 1/2 domain controllers its feasible for that to be hit in a year or two.

 

I Also believe the number isn't counting how many times (a) user is logging in, but its been a while since i have read the docs to know what its actually counting.

 

 

That being said, helpful to see if that computer hidden in the corner of the office in the dregs of the school ever gets used.

[Rob_D]

The thing we had to consider when we did this exercise (we just has a line in the logon script write the username, pc name and date/time to a CSV), is that number of times logged into doesn't always equate to usage, as some of the "most used" computers are only logged into once a day because that user is on it all day.

[jthompson]

You could also pipe that to "Select -First 10" for an exciting top 10!

 

I'm not aware of any PowerShell cmdlet that plays the Top of the Pops theme tune, though, so this solution is not a complete one.

[Sephiroth]

The thing we had to consider when we did this exercise (we just has a line in the logon script write the username, pc name and date/time to a CSV), is that number of times logged into doesn't always equate to usage, as some of the "most used" computers are only logged into once a day because that user is on it all day.

 

Depends on what you want the data for, but this is why I have a script that runs at startup; logon; logoff; and shutdown to log the action to a database. I can then parse the database to get fairly accurate usage stats for users and computers.

[altecsole]

Depends on what you want the data for, but this is why I have a script that runs at startup; logon; logoff; and shutdown to log the action to a database. I can then parse the database to get fairly accurate usage stats for users and computers.

 

Same. We use a Powershell script at logon and logoff to record to a SQL Express database. We also have a similar startup script to record hardware info.

[lmgtfy]

Same. We use a Powershell script at logon and logoff to record to a SQL Express database. We also have a similar startup script to record hardware info.

This sounds really interesting. Would you mind sharing any of it please?

[altecsole]

This sounds really interesting. Would you mind sharing any of it please?

 

Install SQL Server Express and SQL Server Mangement Studio

 

 

Create databases - change details as required:

 

-- Create the database - SystemInfo
CREATE DATABASE SystemInfoDB;
GO

-- Use the database
USE SystemInfoDB;
GO

-- Create the table
CREATE TABLE SystemInfo (
   Id INT IDENTITY(1,1) PRIMARY KEY,
   Hostname NVARCHAR(255),
   CPU NVARCHAR(255),
   MemoryGB INT,
   Make NVARCHAR(255),
   Model NVARCHAR(255),
   SerialNo NVARCHAR(255),
   Motherboard NVARCHAR(255),
   NetworkCard NVARCHAR(255),
   IPAddress NVARCHAR(50),
   MACAddress NVARCHAR(50),
   DriveCSizeGB INT,
   DriveCFreeSpaceGB INT,
   WindowsVersion NVARCHAR(255),
   LastBootTime DATETIME
);
GO

 

Add a user:

CREATE LOGIN SystemInfoUser WITH PASSWORD = 'Y99tV**********';

USE SystemInfoDB;
CREATE USER SystemInfoUser FOR LOGIN SystemInfoUser;

ALTER ROLE db_datawriter ADD MEMBER SystemInfoUser;

GRANT SELECT ON dbo.SystemInfo TO SystemInfoUser;
GO

-- Create the database
CREATE DATABASE LogonRecordDB;
GO

-- Use the database
USE LogonRecordDB;
GO

-- Create the TABLE for logon/logoff events
CREATE TABLE HistoryInfo (
Id INT IDENTITY(1,1) PRIMARY KEY,
Computer NVARCHAR(30),
Username NVARCHAR(30),
Event NVARCHAR(10),
EventDate DATE,
EventTime TIME(3)
);
GO

 

You can use the same a user for both databases, or create a separate one.

 

PowerShell needs the SqlServer module, so I copy this from a network share. This is done in the startup script that logs system information:

 

# Define paths
$networkModulePath = "\\******\Packages\Installers\Modules\SqlServer"
$localModulePath_x64 = "C:\Program Files\WindowsPowerShell\Modules\SqlServer"
$logFile = "C:\Windows\Logs\LogComputerHardware.txt"

# Function to log messages
function Write-Log {
   param (
       [string]$message
   )
   $timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
   $logMessage = "$timestamp - $message"
   Add-Content -Path $logFile -Value $logMessage
}

# Check for existing log file and delete
If (Test-Path -Path $logFile){
   # Delete log file
   Remove-Item -Path $logFile -Force
}

# Create the local module directory if it doesn't exist
if (-Not (Test-Path -Path $localModulePath_x64)) {
   try {
       Write-Log "SQL Module does not exist. Copying..."
       New-Item -Path $localModulePath_x64 -ItemType Directory -Force
       # Copy the module from the network share to the local path
       Copy-Item -Path "$networkModulePath\*" -Destination $localModulePath_x64 -Recurse -Force
   }
   catch {
       Write-Log "Unable to copy module. Exiting.."
       exit
   }

}


# Load the SQL Server module
try {
   Write-Log "Importing Module"
   Import-Module SqlServer
}
catch {
   Write-Log "Unable to import the SQLServer Module. Exiting.."
   exit
}

# Collect system information
try {
   $hostname = (Get-CimInstance -ClassName Win32_ComputerSystem).Name
   $cpu = Get-CimInstance -ClassName Win32_Processor | Select-Object -ExpandProperty Name
   $memory = [math]::Ceiling((Get-CimInstance -ClassName Win32_ComputerSystem).TotalPhysicalMemory / 1GB)
   $system = Get-CimInstance -ClassName Win32_ComputerSystem
   $make = $system.Manufacturer
   $model = $system.Model
   $serialNo = (Get-CimInstance -ClassName Win32_BIOS).SerialNumber
   $motherboard = Get-CimInstance -ClassName Win32_BaseBoard | Select-Object -ExpandProperty Product
   $networkAdapter = Get-CimInstance -ClassName Win32_NetworkAdapter -Filter "NetEnabled=True" | Select-Object -ExpandProperty Name
   $networkAdapterConfig = Get-CimInstance -ClassName Win32_NetworkAdapterConfiguration -Filter "IPEnabled=True"
   $ipAddress = $networkAdapterConfig.IPAddress[0]
   $macAddress = $networkAdapterConfig.MACAddress
   $drive = Get-CimInstance -ClassName Win32_LogicalDisk -Filter "DeviceID='C:'"
   $driveSize = [math]::Ceiling($drive.Size / 1GB)
   $driveFreeSpace = [math]::Ceiling($drive.FreeSpace / 1GB)
   $windowsName = (Get-CimInstance -ClassName Win32_OperatingSystem).Caption
   $windowsVersion = (Get-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Windows NT\CurrentVersion" -Name "DisplayVersion").DisplayVersion
   $windowsFullVersion = "$windowsName $windowsVersion"
   $lastBootTime = (Get-CimInstance -ClassName Win32_OperatingSystem).LastBootUpTime
}
catch {
   Write-Log "Error getting system information."
}


# SQL Server connection details
$serverName = "*****\SQLEXPRESS"
$databaseName = "SystemInfoDB"
$tableName = "SystemInfo"
$username = "SystemInfoUser"
$password = 'Y99t*******'

<#
# Insert collected information into the database
$insertQuery = @"
INSERT INTO $tableName
   (Hostname, CPU, MemoryGB, Make, Model, SerialNo, Motherboard, NetworkCard, IPAddress, MACAddress, DriveCSizeGB, DriveCFreeSpaceGB, WindowsVersion, LastBootTime)
VALUES
   ('$hostname', '$cpu', $memory, '$make', '$model', '$serialNo', '$motherboard', '$networkAdapter', '$ipAddress', '$macAddress', $driveSize, $driveFreeSpace, '$windowsFullVersion', '$lastBootTime')
"@
#>

# Construct the SQL query
$mergeQuery = @"
MERGE $tableName AS target
USING (SELECT '$hostname' AS Hostname, '$serialNo' AS SerialNo) AS source
ON target.Hostname = source.Hostname AND target.SerialNo = source.SerialNo
WHEN MATCHED THEN
   UPDATE SET
       CPU = '$cpu',
       MemoryGB = $memory,
       Make = '$make',
       Model = '$model',
       Motherboard = '$motherboard',
       NetworkCard = '$networkAdapter',
       IPAddress = '$ipAddress',
       MACAddress = '$macAddress',
       DriveCSizeGB = $driveSize,
       DriveCFreeSpaceGB = $driveFreeSpace,
       WindowsVersion = '$windowsFullVersion',
       LastBootTime = '$lastBootTime'
WHEN NOT MATCHED THEN
   INSERT (Hostname, CPU, MemoryGB, Make, Model, SerialNo, Motherboard, NetworkCard, IPAddress, MACAddress, DriveCSizeGB, DriveCFreeSpaceGB, WindowsVersion, LastBootTime)
   VALUES ('$hostname', '$cpu', $memory, '$make', '$model', '$serialNo', '$motherboard', '$networkAdapter', '$ipAddress', '$macAddress', $driveSize, $driveFreeSpace, '$windowsFullVersion', '$lastBootTime');
"@

# Execute the query with SQL Server authentication
try {
   #Invoke-Sqlcmd -ServerInstance $serverName -Database $databaseName -Query $insertQuery -Username $username -Password $password -Encrypt Optional
   Invoke-Sqlcmd -ServerInstance $serverName -Database $databaseName -Query $mergeQuery -Username $username -Password $password -Encrypt Optional
   Write-Log "System information written to database."
   Write-Log "Script complete."
}
catch {
   Write-Log "Error writing to SQL Server database."
}

 

User logon and logoff event use PowerShell script to populate:

 

Logon:

$logFile = "H:\Logs\userlogon.txt"
$userEvent = "LOGON"

function Write-Log {
   param (
       [string]$message
   )
   $timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
   $logMessage = "$timestamp - $message"
   Add-Content -Path $logFile -Value $logMessage
}

# Check for existing log file and delete
If (Test-Path -Path $logFile) {
   # Delete log file
   Remove-Item -Path $logFile -Force
}

$SQLModuleExists = $false
# Load the SQL Server module
try {
   Write-Log "Importing Module"
   Import-Module SqlServer
   $SQLModuleExists = $true
}
catch {
   Write-Log "Unable to import the SQLServer Module. Try restarting the computer, which should copy the Module to the local computer."
   exit
}

$ComputerName = $env:COMPUTERNAME
$LoggedOnUser = $env:USERNAME
$CurrentDate = Get-Date -Format "yyyy-MM-dd"
$CurrentTime = Get-Date -Format "HH:mm:ss"

Write-Log "Computer Name: $ComputerName"
Write-Log "Logged On User: $LoggedOnUser"
Write-Log "Date: $CurrentDate"
Write-Log "Time: $CurrentTime"

If ($SQLModuleExists) {
   # SQL Server connection details
   $serverName = "******\SQLEXPRESS"
   $databaseName = "LogonRecordDB"
   $tableName = "HistoryInfo"
   $username = "HistoryInfoUser"
   $password = 'NKV20*********'

   # Construct the SQL query
   $insertQuery = @"

INSERT INTO $tableName
   (Computer, Username, Event, EventDate, EventTime)
VALUES
   ('$ComputerName', '$LoggedOnUser', '$userEvent', '$CurrentDate', '$CurrentTime')
"@

   # Execute the query with SQL Server authentication
   try {
       Invoke-Sqlcmd -ServerInstance $serverName -Database $databaseName -Query $insertQuery -Username $username -Password $password -Encrypt Optional
       Write-Log "Logon information written to the database"
   }
   catch {
       Write-Log "Error writing to SQL Server database"
   }
}
Write-Log "End of script."

 

Logoff:

$logFile = "H:\Logs\userlogoff.txt"
$userEvent = "LOGOFF"

function Write-Log {
   param (
       [string]$message
   )
   $timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
   $logMessage = "$timestamp - $message"
   Add-Content -Path $logFile -Value $logMessage
}

# Check for existing log file and delete
If (Test-Path -Path $logFile) {
   # Delete log file
   Remove-Item -Path $logFile -Force
}

$SQLModuleExists = $false
# Load the SQL Server module
try {
   Write-Log "Importing Module"
   Import-Module SqlServer
   $SQLModuleExists = $true
}
catch {
   Write-Log "Unable to import the SQLServer Module. Try restarting the computer, which should copy the Module to the local computer."
   exit
}

$ComputerName = $env:COMPUTERNAME
$LoggedOnUser = $env:USERNAME
$CurrentDate = Get-Date -Format "yyyy-MM-dd"
$CurrentTime = Get-Date -Format "HH:mm:ss"

Write-Log "Computer Name: $ComputerName"
Write-Log "Logged On User: $LoggedOnUser"
Write-Log "Date: $CurrentDate"
Write-Log "Time: $CurrentTime"

If ($SQLModuleExists) {
   # SQL Server connection details
   $serverName = "******\SQLEXPRESS"
   $databaseName = "LogonRecordDB"
   $tableName = "HistoryInfo"
   $username = "HistoryInfoUser"
   $password = 'NKV2*******'

   # Construct the SQL query
   $insertQuery = @"

INSERT INTO $tableName
   (Computer, Username, Event, EventDate, EventTime)
VALUES
   ('$ComputerName', '$LoggedOnUser', '$userEvent', '$CurrentDate', '$CurrentTime')
"@

   # Execute the query with SQL Server authentication
   try {
       Invoke-Sqlcmd -ServerInstance $serverName -Database $databaseName -Query $insertQuery -Username $username -Password $password -Encrypt Optional
       Write-Log "Logoff information written to the database"
   }
   catch {
       Write-Log "Error writing to SQL Server database"
   }
}
Write-Log "End of script."

 

It's a good ideal to delete old logon/logoff records.

 

I've used this in the past to calculate actual computer usage.

[lmgtfy]

Thanks @altecsole for taking the time to write all of this up it's much appreciated. I will look into implementing this ASAP.

 

Cheers