The new PowerShell cmdlets that are part of the SQLServer PowerShell module that’s distributed as part of SSMS (SQL Server Management Studio) 2016 make it super easy to write the output of PowerShell commands to a SQL Server database.

The ActiveDirectory PowerShell module that’s part of the RSAT (Remote Server Administration Tools) is also required by the code shown in this blog article.

This PowerShell one-liner retrieves a list of Active Directory users who have not logged in within the past 120 days, are enabled, and exist in the Adventure Works OU (Organizational Unit). It disables them and logs the results to a table in a SQL Server database. Note that the LastLogonTimeStamp property can be off by up to 14 days so be sure to take that into account. See this Active Directory team article for more information about that particular property.

Keep in mind that a PowerShell one-liner is one continuous pipeline and not necessarily a command that’s on one physical line. Not all commands that are on one physical line are one-liners.

(Get-Date).AddDays(-120) | ForEach-Object { Get-ADUser -Filter { LastLogonTimeStamp -lt $_ -and enabled -eq $true } -SearchBase 'OU=AdventureWorks Users,OU=Users,OU=Test,DC=mikefrobbins,DC=com' -Properties LastLogonTimeStamp -PipelineVariable UserInfo} | Disable-ADAccount -PassThru | Select-Object -Property Name, SamAccountName, @{label='LastLogon';expression={[DateTime]::FromFileTime($UserInfo.LastLogonTimestamp).ToString('yyyy-MM-dd HH:mm:ss')}}, @{label='DisabledDate';expression={(Get-Date).ToString('yyyy-MM-dd HH:mm:ss')}} | Write-SqlTableData -ServerInstance sql011 -DatabaseName MrOps -SchemaName dbo -TableName DisabledAdUsers -Force 1 2 3 4 5 6 7 8 9 10 ( Get-Date ) . AddDays ( -120 ) | ForEach-Object { Get-ADUser -Filter { LastLogonTimeStamp -lt $_ -and enabled -eq $true } -SearchBase 'OU=AdventureWorks Users,OU=Users,OU=Test,DC=mikefrobbins,DC=com' -Properties LastLogonTimeStamp -PipelineVariable UserInfo } | Disable-ADAccount -PassThru | Select-Object -Property Name , SamAccountName , @ { label = 'LastLogon' ; expression = { [ DateTime ] :: FromFileTime ( $UserInfo . LastLogonTimestamp ) . ToString ( 'yyyy-MM-dd HH:mm:ss' ) } } , @ { label = 'DisabledDate' ; expression = { ( Get-Date ) . ToString ( 'yyyy-MM-dd HH:mm:ss' ) } } | Write -SqlTableData -ServerInstance sql011 -DatabaseName MrOps -SchemaName dbo -TableName DisabledAdUsers -Force

Using Write-SqlTableData with the Force parameter creates the database and table on the SQL Server if they don’t already exist.

Retrieving the results is as simple as querying the SQL Server database:

Invoke-Sqlcmd -ServerInstance sql011 -Database MrOps -Query 'select * from DisabledAdUsers' 1 Invoke-Sqlcmd -ServerInstance sql011 -Database MrOps -Query 'select * from DisabledAdUsers'

Now you know exactly when the accounts were disabled instead of trying to depending on the modified date in Active Directory and not knowing if disabling the account was the last modification that took place or not.

Never underestimate the power of a PowerShell one-liner. PowerShell one-liners will be covered in more detail in my new PowerShell 101 book.

µ

Share this: Twitter

Reddit

LinkedIn

Facebook

Pocket

Print

