How do you report on stale workstations post Covid lockdowns?

So how do you do it?

First, a caveat… I have had no formal PowerShell training, and only started scripting with PowerShell properly in September-ish of 2020, prior to this, the normal ‘one-liners’ were the norm for me to complete my workday. Cliff-like learning curves lay ahead for me… So please go easy on me?

Secondly, if you want to view my code, please do it in something like VS Code. This tool colour codes PowerShell in such a way, that it helps to visualise the code better. I only use PowerShell ISE if I dont have VS Code installed on the machine, or if I'm troubleshooting a piece of code on a Domain Controller.

?

Our lives changed when covid 19 hit in 2019. I remember the day clearly, 20 March 2020. Day one of working from home. This time was filled with frantic app publishing in Azure to ensure applications were accessible to the workforce while working outside of the corporate network and without the need of using the VPN solution, which had limited capacity, reserved for only those needing access to on-prem server environments, in my case, Active Directory and its infrastructure. Luckily, we were already running a Hybrid cloud model with AzureAD, Intune and Microsoft 365, so our transition to remote work was not nearly as bad as some of the horror stories. Our transition was relatively painless (apart from the many, many hours spent frantically getting apps registered). While on paper, and in the eyes of the management team, this is the end of a successful transition, it poses some very interesting solutions to find, one of which is the reporting and management of the stale workstation objects in on-prem AD.

?

Why is this such a problem, just run the traditional processes to report on stale workstations, then disable\delete them as needed, right? The short answer is no. You could simply run the reports and disable those machines, but there will be impact to your users, especially those who never use the VPN to connect to the on-prem network, meaning that the computer objects for actively working users, never get updated, and so appear to be stale, on the Domain Controllers that is.

?

I have solved this problem for my organisation, and this is how I did it.

?

Firstly, you need reliable data, on-prem AD is a start, but is not accurate in my case, so I needed to supplement it, reliably, and accurately. I used AzureAD and MS Intune data extracted from the MS Graph API, but there was a catch, and this was the ability to match the data from each set of data into one report. The problem is that an object in on-prem AD seemingly did not have any data that could be matched to AzureAD to know definitively if the data extracted was for that object. Using the serial number was unreliable, and not available in all sets of data, this also meant that if I had the serial number from one device, and matched it to an AzureAD object, there were often duplicate objects, so the data was unreliable. I then found that the DeviceID attribute in AzureAD can be matched to the ObjectGUID attribute in Active Directory. At least in our estate. We run AADCSync that syncs our on-prem AD data into AzureAD, including the workstations (Windows 10 and 11 at least), so the matching of the object’s data between on-prem AD and AzureAD became a trivial task in PowerShell.

?

The next step, is to match the AzureAD\on-prem AD data with the data extracted from the MS Graph API (which, as it turns out, was actually the Intune data) to supplement the data in the report. Overkill? Absolutely! You see, to understand the level of certainty I needed, one must understand the environment in which us Identity specialists are expected to function in. My mandate has always been ‘No user impact’, as in, “NO USER IMPACT!”. Any user impact means ‘failure’ in our organisation, which translates into a failed change. So yes, overkill is the only way to ensure that there is no user impact.

?

To match the AzureAD\on-prem AD data with the Intune data, I used the AzureAD deviceID as this was the simplest.

?

What data is required to be able to reliably determine if a device is stale or not? Well, I used the following data:

?

AzureAD: DeviceID, ObjectID, AccountEnabled, ApproximateLastLogonTimeStamp, DisplayName, DeviceOSType

Intune: AzureADDeviceID, ID, DeviceName, EnrolledDateTime, LastSyncDateTime, UserPrincipalName

On-prem AD: CN, CanonicalName, objectGUID, LastLogonDate and Enabled

?

I do some pretty interesting data transformation to get the information in the correct formats to allow PowerShell to do some reliable testing, to produce the report. All Date\time data is stored as a date\time in a uniform format of 'yyyy/MM/dd’ and all IDs are stored as string values. If you would like to see the code, and how I use it, please take a look on GitHub, linked HERE.

?

How do I perform the calculations? This is where things go deep into a PowerShell rabbit hole…

?

For clarity, $Staledate is simply a calculation using current date, minus 90 days, and formatted appropriately.

?

For AzureAD:

The following code is what I use to extract the data, into an arraylist called $AllAzureADDevices:

?

$AllAzureADDevices = [System.Collections.ArrayList]@(Get-AzureADDevice -All:$TRUE | Select-Object @{Name = "AzureADDeviceID"; Expression = { $_.DeviceId.toString() } }, @{Name = "ObjectID"; Expression = { $_.ObjectID.toString() } }, @{Name = "AADEnabled"; Expression = { $_.AccountEnabled } }, @{Name = "AADApproximateLastLogonTimeStamp"; Expression = { (Get-Date -Date $_.ApproximateLastLogonTimeStamp -Format 'yyyy/MM/dd') } }, @{Name = "AADDisplayName"; Expression = { $_.DisplayName } }, @{Name = "AADSTALE"; Expression = { if ($_.ApproximateLastLogonTimeStamp -le $StaleDate) { "TRUE" } elseif ($_.ApproximateLastLogonTimeStamp -gt $StaleDate) { "FALSE" } else { "NoLoginDateFound" } } }, DeviceOSType | Sort-Object azureADDeviceId )

?

DeviceID is renamed to AzureADDeviceID and stored as a string value.

ObjectID is stored as a string value.

AccountEnabled is renamed to AADEnabled.

ApproximateLastLogonTimeStamp is renamed to AADApproximateLastLogonTimeStamp and stored as a date\time.

DisplayName is renamed to AADDisplayName

Now for a little magic. AADStale is a calculated value that tests whether the ApproximateLastLogonTimeStamp date is older than the $Staledate value, or newer. If older, the value is “True”, if newer, the value is “False”.

DeviceOSType is stored as is.

?

The array is sorted by AzureADDeviceID

?

For Intune:

I use the following code (and a custom Function) to extract the data from the MS Graph API and store it in an arraylist called $IntuneInterimArray:

?

$IntuneInterimArray = [System.Collections.ArrayList]@(Invoke-MSGraphOperation -Get -APIVersion "Beta" -Resource "deviceManagement/managedDevices?`$filter=operatingSystem eq 'Windows'" -Headers $AuthenticationHeader -Verbose |

Where-Object { $_.azureADDeviceId -ne "00000000-0000-0000-0000-000000000000" } |

Select-Object @{Name = "azureADDeviceId"; Expression = { $_.azureADDeviceId.toString() } },

@{Name = "IntuneDeviceID"; Expression = { $_.id.ToString() } },

@{Name = "MSGraphDeviceName"; Expression = { $_.deviceName } },

@{Name = "enrolledDateTime"; Expression = { (Get-Date -Date $_.enrolledDateTime -Format "yyyy/MM/dd") } },

@{Name = "MSGraphlastSyncDateTime"; Expression = { (Get-Date -Date $_.lastSyncDateTime -Format "yyyy/MM/dd") } },

@{Name = "MSGraphLastSyncStale"; Expression = { if ((Get-Date -Date $_.lastSyncDateTime -Format "yyyy/MM/dd") -le $StaleDate) { "TRUE" } elseif ((Get-Date -Date $_.lastSyncDateTime -Format "yyyy/MM/dd") -gt $StaleDate) { "FALSE" } else { "NoLoginDateFound" } } }, @{Name = "UserUPN"; Expression = { $_.userPrincipalName } } | Sort-Object IntuneDeviceID)

?

AzureADDeviceID is stored as a string value.

ID is renamed to IntuneDeviceID and stored as a string value.

deviceName is renamed to MSGraphDeviceName.

EnrolledDateTime is stored as a date\time.

lastSyncDateTime is renamed to MSGraphLastSyncDateTime and is stored as a date\time.

The magic happens here: MSGraphLastSyncStale is a calculated value that tests whether lastSyncDateTime is older then $StaleDate or newer, if older, the value is stored as “True”, if newer, then “False”.

userPrincipalName is renamed to UserUPN.

?

For on-prem AD:

To extract the on-prem data, I used the following piece of code, looped per domain in the forest, to create an arraylist called ‘RAWAllComps’. This is the snippet of code I use to extract the data, this is in a loop, so $comps is added to $RAWAllComps in the loop, to get the data for each windows 10\11 device in the forest.

?

$Comps = [System.Collections.ArrayList]@(Get-ADComputer -Server $ServerTarget -Filter 'operatingSystemVersion -like "10.*"' -Properties CN, CanonicalName, objectGUID, LastLogonDate, whenChanged, DistinguishedName, OperatingSystem, Enabled -ErrorAction Stop | Where-Object {($_.OperatingSystem -notlike "*Server*")} | Select-Object CN, CanonicalName, objectGUID, LastLogonDate, whenChanged, DistinguishedName, OperatingSystem, Enabled )

?

I know what you are thinking… Christopher, you are being a muppet, surely you can do the data transformation in this foreach loop at the time of extraction from the DCs, instead of doing it as an extra step later in the code?!?! Short answer is Yes, of course this can be done there, but keep in mind that I am working with a LOT of data in my environment, and doing the data transform at the same time as the extraction, while more efficient, causes excessive timeouts when extracting data from DCs that are not local to the server running the extraction. I was also forced to add sections to my code to delete used arrays and clear the memory, or risk RAM starvation (one of my earlier iterations of the script would overwhelm 32Gb RAM without breaking a sweat).

?

Below is the data transformation code:

?

$AllOPCompsArray = [System.Collections.ArrayList]@($RAWAllComps | Select-Object

@{Name = "azureADDeviceId"; Expression = { $_.objectGUID.toString() } },

@{Name = "OPDeviceName"; Expression = { $_.CN } },

@{Name = "OPDeviceFQDN"; Expression = { "$($_.CN).$($_.CanonicalName.Split('/')[0])" } },

@{Name = "SourceDomain"; Expression = { "$($_.CanonicalName.Split('/')[0])" } },

@{Name = "OPLastLogonTS"; Expression = { (Get-Date -Date $_.LastLogonDate -Format "yyyy/MM/dd") } },

@{Name = "OPSTALE"; Expression = { if ($_.LastLogonDate -le $StaleDate) { "TRUE" } elseif ($_.LastLogonDate -gt $StaleDate) { "FALSE" } else { "NoLoginDateFound" } } }, @{Name = "OPEnabled"; Expression = { $_.Enabled } } | Sort-Object azureADDeviceId )

?

objectGUID is renamed to azureADDeviceId and stored as a string value.

CN is renamed to OPDeviceName.

OPDeviceFQDN is a calculated value using CN and CanonicalName.

SourceDomain is a calculated value extracted from CanonicalName.

LastLogonDate is renamed to OPLastLogonTS and stored as date\time.

whenChanged is renamed to OPLastupdateTS and stored as date\time.

OPSTALE is a calculated value using LastLogonDate and $StaleDate.

Enabled is renamed to OPEnabled.

?

The data is sorted by azureADDeviceID.

?

Once all the data is extracted, and formatted appropriately, we can start working with the data.

I perform a leftjoin of the Intune data with the on-prem data using AzureADDeviceID and store this data in an array, lets call it IntuneRaw1.

I then perform a leftjoin of the on-prem data with the Intune data using AzureADDeviceID and store this data in an array, lets call it IntuneRaw2. I then keep only information where the devices have no IntuneDeviceID.

Then, I add the arrays together, and perform a data deduplication using AzureADDeviceID.

?

Why would I do such a thing… Seems like the work of a madman, Christopher?

So, perhaps it is. I was having trouble with the data integrity, I found that I was getting inconsistent results and seemingly losing relevant data. I found that it I did things like I did above, the data was complete. The reason for this is because I found that there were devices in on-prem AD, but not in Intune, and there were devices in Intune, that were not in on-prem AD.

?

Now, we perform a leftjoin the AzureAD data with the deduplicated data using AzureADDeviceID, I call this arraylist $DDAllDevProcArray.

?

But Christopher, you just said you needed to perform the join both ways to get a complete picture, why not do that with the AzureAD data as well?

Good question… This will be fixed, seems I missed this, but keep in mind that I was working on on-prem stale device reporting, not AzureAD devices, but I will cover this in another article, where I tackle both on-prem and AzureAD stale devices. I will also document the script I use to disable\delete these objects, in both locations.

?

Now for the key to this entire process…

Here is the code used to perform the calculations to find the devices that are truly stale.

?

$AllDevices = [System.Collections.ArrayList]@($DDAllDevProcArray | Select-Object AzureADDeviceID, IntuneDeviceID, ObjectID, AADDisplayName, MSGraphDeviceName, OPDeviceName, OPDeviceFQDN, SourceDomain, UserUPN, enrolledDateTime, AADApproximateLastLogonTimeStamp, MSGraphlastSyncDateTime, OPLastLogonTS, AADEnabled, OPEnabled, AADSTALE, OPSTALE, MSGraphLastSyncStale,

@{Name = "TrueStale"; Expression = { if ($_.AADStale -notlike "False" -and $_.OPStale -notlike "False" -and $_.MSGraphLastSyncStale -notlike "False") { "TRUE" } else { "FALSE" } } },

@{Name = "AccountEnabled"; Expression = { if ($_.AADEnabled -notlike "False" -and $_.OPEnabled -notlike "False") { "TRUE" } else { "FALSE" } } })

?

TrueStale is calculated using AADSTALE, OPStale and MSGraphLastSyncStale. If AADStale, OPStale and MSGraphLastSyncStale values are not ‘False’ (as in, anything other than ‘false’), then the TrueStale field is set as ‘True’, meaning that the device is truly stale.

?

But Christopher, why do you do it this way? Why not simply state if AADStale, OPStale and MSGraphLastSyncStale values are ‘True’, the device is truly stale?

Good question, I found that many of the devices were missing data in one of the fields, so if the device had not synced to AzureAD, or if the device was not Intune managed (this was a massive problem for us for a long time), matching ‘true’ caused the code ‘fail’ processing on many of the devices, and marked them as not stale, if any field was missing data. The way I do it above, will mark a device as truly stale, if the device is stale in on-prem AD, but missing data in Intune and AzureAD, similarly, if a device is stale in on-prem AD, and stale in AzureAD, but no data in Intune, the device is marked as truly stale, however, if any of the fields contain ‘False’, as in not stale, the device cannot be truly stale.

Later, I found that Intune is our estate is configured to manage stale devices automatically by removing devices that have not been used\seen in 90 days, so the Intune data is most certainly overkill, but its more complicated to remove this data, than to just wait for it to process.

?

Once the data analysis is complete, I export the data out in Excel files and in CSV.

?

I have documented the process on Github.

Thank you for your time.

Christopher Baxter