Forum Discussion
Help with parameter for Search-UnifiedAuditLog
Hi,
Disclaimer: I am new to PowerShell, hence why I turn here for your input.
Background:
I'm creating a Power BI dashboard based on data exported from the O365 Audit Log. For the moment, I'm not using the recently launched API, but a daily scheduled PowerShell script to export the data. Everything works fine with one exception:
Issue:
The maximum number of objects that can be returned in one query is 5000 (see article for details). We have a fairly small tenant, so I have divided the cmdlet to run twice to cover 24h (AM+PM), that gives us 10k rows which usually is enough. Now the exception is when we run SharePoint backups via Metalogix. The operation is of course walking through every single file which results in logs well above 10k rows.
Solution?
What I want to achieve is to exclude the user account associated with the backup operation, to only return "relevant" objects/events. I was hoping to archive this by using the parameters provided in the cmdlet, but with my limited knowledge I can't figure it out. I can pass user ID's to include, but would this also allow me to exclude by user ID? If so, how?
I can always divide the cmdlet to run more than twice per 24h, allowing more objects to be returned, but I hope there is a better solution to this.
Many thanks!
NarasimaPerumal Chandramohan thanks for pointing me in the right direction. I managed to solve it by using SessionID and SessionCommand. All I needed was a while loop that kept running until the variable taking the audit data returned null, and keep appending the export file in every loop run.
What you are looking for I guess is something like a "NOT" operator, which will allow you to exclude all actions from particular user/account. Unfortunately, I'm not aware of such for the UserIds parameter of the Search-UnifiedAuditLog cmdlet. I guess you can do it the other way around, use the UserIds parameter to explicitly list all users, apart for the service account used for backups.
Flagging TonyRedmond to double-check.
VasilMichev Yes, I NOT EQUAL operator would be the optimal thing, but I have not got that to work. I guess it's not supported as no similar functionality exists for the Audit Log interface in the Security & Complience Center. And to pass all users except one is not a great option unfortunately.
I am unaware of a way to exclude accounts from a cmdlet pull for audit records. There might be an easier way for you to do this... Cogmotive Reports has a Discover and Audit module that works against the audit log records (in other words, the same data) and it comes with a pivot table capability that you might be able to do what you want. They also are able to provide more data than 5,000 records at a time because they store the audit log data in their own stores. You could try that...
TonyRedmond Thank you for the tip about Cogmotive. I will have a look at the product for sure. However, I like the customizability that I get in Power BI, building interactive reports for different stakeholders. And I guess it's something about the challenge of building it yourself :)
Have you checked the parameter "SessionCommand" in the Search-UnifiedAuditLog cmdlet?. By using this you can get all the records. But you need to do the filters in the DB where you have stored the audit logs.
NarasimaPerumal Chandramohan thanks for pointing me in the right direction. I managed to solve it by using SessionID and SessionCommand. All I needed was a while loop that kept running until the variable taking the audit data returned null, and keep appending the export file in every loop run.
Here is my approach to solve this problem, I had something alike and wanted to share it with you, there was a lot of chatter on one specific parameter rendering the 5000 limit useless, within the 24 hours that is, so I created a 4 hour iteration ignoring the bogus parameter, hopes it helps you.
# ignore command "set-whatever" over 1000 hits every x hours$global:day = (Get-Date)# set start date at midnight$hours = $global:day.TimeOfDay.TotalMinutes$startdate = $global:day.AddMinutes(- $hours)# set end date at midnight$enddate = $startdate.AddHours(24)#$logsearch=@()# iterate every x hours ignoring bogus operation$increment=4for($i=0; $i -le (24-$increment); $i=$i+$increment) { $i$logsearch += Search-UnifiedAuditLog -StartDate $startdate.AddHours($i) -EndDate $startdate.AddHours(4+$i) -RecordType <searchtype> -SessionCommand ReturnLargeSet -resultsize 5000|? Operations -NotMatch "set-whatever"}#you could add a group of operation restrictions by using "-in" operator if you have a bunch.
#
NarasimaPerumal Chandramohan I have been fiddling around with that a bit but did not manage to get it to work. To me the instructions on how to use it are a bit vague. I don't know what they mean with Paging, and a full tenant backup is likely to require more than 50k objects to be returned.
However, it seems like I can use multiple session ID's and that each one will return 50k if I use the ReturnLastSet parameter. I think it's worth trying out again. I need to make sure that the rest of the script can handle the way objects are returned through paging, as it needs to convert from JSON to allow proper export.
Here is a bit of the script I use today:
$AuditOutputPM = Search-UnifiedAuditLog -StartDate "$StartDate 12:00 PM" -EndDate "$EndDate 23:59 PM" -ResultSize 5000 $AuditOutputAM = Search-UnifiedAuditLog -StartDate "$StartDate 00:01 AM" -EndDate "$EndDate 11:59 AM" -ResultSize 5000 $ConvertedOutputPM = $AuditOutputPM | Select-Object -ExpandProperty AuditData | ConvertFrom-Json $ConvertedOutputAM = $AuditOutputAM | Select-Object -ExpandProperty AuditData | ConvertFrom-Json $ConvertedOutputPM | Select-Object CreationTime,UserId,Operation,Workload,ObjectID,SiteUrl,SourceFileName,ClientIP,UserAgent | Export-Csv $OutputFile -NoTypeInformation -Append $ConvertedOutputAM | Select-Object CreationTime,UserId,Operation,Workload,ObjectID,SiteUrl,SourceFileName,ClientIP,UserAgent | Export-Csv $OutputFile -NoTypeInformation -Append
