Get AD users who have not logged in the past x Days and are member of a specific group?

Question

I'm sorry if this looks like a duplicate. Actually the same question has been asked under http://serverfault.com/questions/783399/get-a-list-of-users-who-are-domain-admins-and-have-not-logged-in-the-past-30-day I am doing some reading on how AD works and one of the exercises suggests that I get a list of users who are Domain Admins and have not logged in the past 90 days.

user360071 suggested the below script:

Import-Module ActiveDirectory
$age = 30
$When = ((Get-Date).AddDays(-$age)).Date
$DomainAdminsDn = (Get-ADGroup 'Domain Admins').DistinguishedName
Get-ADUser -Property LastLogonDate -Filter { ((memberof -eq $DomainAdminsDn) -and (LastLogonDate -lt $When))} | Select SamAccountName,UserPrincipalName,LastLogonDate | Export-CSV $env:USERPROFILE\Desktop\users.csv -NoTypeInformation

or

Import-Module ActiveDirectory
$age = Read-Host "Accounts that have not been logged into in the last how many days should be shown?" 
$When = ((Get-Date).AddDays(-$age)).Date
$DomainAdminsDn = (Get-ADGroup 'Domain Admins').DistinguishedName
Get-ADUser -Property LastLogonDate -Filter { ((memberof -eq $DomainAdminsDn) -and (LastLogonDate -lt $When))} | Select SamAccountName,UserPrincipalName,LastLogonDate | Export-CSV $env:USERPROFILE\Desktop\users.csv -NoTypeInformation

However, I'm having the same issue as the other user who posted this question. The CSV file is empty.

I ran the below code to make sure I have DAs in my environment:

 $DomainAdminsDn = (Get-ADGroup 'Domain Admins').DistinguishedName
 Get-ADUser -Property LastLogonDate -Filter {(memberof -eq $DomainAdminsDn)}

These are the returned results:

DistinguishedName : CN=James Conrad,OU=Sales,OU=London,DC=lab,DC=local
Enabled           : True
GivenName         : James
LastLogonDate     :
Name              : James Conrad
ObjectClass       : user
ObjectGUID        : e990ab92-7034-4be2-8064-ff7a3e31e8b6
SamAccountName    : jconrad
SID               : S-1-5-21-2556462985-1643875289-4278096718-1118
Surname           : Conrad
UserPrincipalName : [email protected]

DistinguishedName : CN=Ed Meadows,OU=Branch Office 1,OU=London,DC=lab,DC=local
Enabled           : True
GivenName         : Ed
LastLogonDate     : 11/06/2016 7:37:21 PM
Name              : Ed Meadows
ObjectClass       : user
ObjectGUID        : 0a70ef30-ffb7-40dd-b0ef-ef94a394e66a
SamAccountName    : emeadows
SID               : S-1-5-21-2556462985-1643875289-4278096718-1123
Surname           : Meadows
UserPrincipalName : [email protected]

As you can see I should at least be getting James Conrad in my CSV file since he never logged in and he's a Domain Admin. Any idea why this happens? Is it empty because James Conrad never logged on and Ed Meadows does not meet the criteria (-lt -90)?

Solution

When I complete automation projects or do automation training classes I'm always asked why I don't use Export-Csv. Microsoft claims Export-Csv is the same as ConvertTo-Csv except that it saves the output to the file. While that statement is literally correct, it's not a replacement for the tried and true code of:

$Objects | ConvertTo-Csv -NoTypeInformation > .\usagecase.csv

Export-Csv has issues with non-standard formatted objects. This is especially prescient in the cmdlets from the ActiveDirectory module. It's a case of left hand / right hand. The AD cmdlets don't return objects using standard formatting and you'll run into odd issues like this using them.

The reason why the tried and true method works in this case is because Powershell will format data the same way everytime for output to the console. The key is the redirection operator > which tells Powershell to dump console output to the file instead of the screen.

Comments

[Dominique]

I like how I'm getting lots of -'es but no suggestion. Gotta love this.

[bentek]

Do you get results from Get-ADUser -Property LastLogonDate -Filter { ((memberof -eq $DomainAdminsDn) -and (LastLogonDate -lt $When))} | Select SamAccountName,UserPrincipalName,LastLogonDate ?

[Dominique]

yeah that works, I get all the accounts. It seems the problem is in using the export-csv command

[bentek]

Your exact code works fine for me. Are you running PowerShell as Administrator? Maybe permission issues writing the file to disk?

[Dominique]

Yup, I tried it in another environment and I got the same problem. Not sure why this happens. I just removed the export-csv part and did it this way. 1) save the above script to a .ps1 file and exclude the | export-csv part 2) navigate to the script and run it including >> filename.csv. For example, I navigated to desktop where the script was, and typed .\get-dausers.ps1 >> users.csv, and it worked. Thanks for your suggestion