Search-UnifiedAuditLog - List of users.


Hey Guys, 

Trying to generate a report of users who logged into office 365 and/or their mailbox. Just need to be able to use an input file. 


I found this script here: 


To Generate the Data


$ConvertAudit = Search-UnifiedAuditLog  -StartDate "04-01-2020" -EndDate "04-10-2020" -UserIds "" -ResultSize 5000


To produce the report:


$ConvertAudit | Select-Object -ExpandProperty AuditData | ConvertFrom-Json | Select-Object CreationTime,UserId,Operation,Workload,ObjectID,SiteUrl,SourceFileName,ClientIP,UserAgent


The script is great! It allows you to export data for a single user, and format that data from JSON to Excel. This is what i needed!! 


The only problem i am having is i want to be able to use the above script and run it against an input file. The input file would have a list of users email addresses. (PrimarySMTP). 


I have tried to "fix it myself" and have not gotten anywhere. Any help would be appreciated. 





6 Replies

Why don't you simply export the sign-in logs from the Azure AD portal?

@Vasil Michev 

Hey Vasil, Because i need to be able to run this against a list of users, and also i didn't see that the audit log reports in office (Sec and Compliance) reported the last sign in date. 


Also there is more data in the script below than is provided by the canned audit log reports in the sec and comp center. 




I'm referring to the sign-in logs in the Azure AD portal, which are the source of truth when it comes to the "last logged" date, it flows from there to the SCC.



@Vasil Michev 


Yep Agreed. The Azure Sign in logs are pretty good. but they dont contain all the data i need and i cant figure out how to upload a list of users and have a report generated on that list. 


The script provided in my post shows thinks like workload, failed sign ins etc. I can see if a user ACTUALLY accessed their own mailbox (Owner Logins) etc. All this makes life much easier when recovering licenses. 


But that gets back to my need to run a list of user accounts through my script so i can then run a different script to reclaim their licenses. 







@Vasil Michev 


This is the string i came up with to try and use my input list: 


Import-Csv .\VIPUsers1018.csv | foreach {Search-UnifiedAuditLog -StartDate "04-01-2020" -EndDate "04-10-2020" -UserIds $_.PrimarySMTPAddress -ResultSize 5000}


But it returns blank results (no errors) and of course i would still need to connect it with my variables. 



The script you are using returns *any* event, which can result in thousands of entries per user, and doesn't really match your initial description of wanting the "logged in" date. At the very least, use the -Operations parameter to filter just the login events.


Which as I said above are basically copies of what you see in the Azure AD log. Exporting that one is as easy as clicking a button, and so is filtering out just the users you care about. If you want to do this based on a CSV file or similar, just use the lookup functionality in Excel.