Stream Audit Log - Script to check who viewed/liked a video returns the same element multiple times

[fstorer]

Hello everyone,

I have been asked to find out who viewed/liked a specific video in Microsoft Stream. I am using the following script to get the first info (for the second I replace the StreamInvokeVideoView with StreamInvokeVideoLike):

$StartDate = (Get-Date).AddDays(-30)
$EndDate = (Get-Date)
$Viewers = Search-UnifiedAuditLog -StartDate $StartDate -EndDate $EndDate -RecordType MicrosoftStream -Operations StreamInvokeVideoView -ObjectIds "VIDEOLINK" -ResultSize 500 | Sort-Object CreationDate 
$ConvertAudit = $Viewers | Select-Object -ExpandProperty AuditData | ConvertFrom-Json
$ConvertAudit | Select-Object CreationTime, ResourceTitle, UserId, ClientIP, Operation, Workload, ResultStatus

 The issue I am having is that when I check the $Viewers and the $ConvertAudit variables, I have several repeated elements (e.g. I see the same user with the same creationdate and the same auditdata repeated almost 100 times).

What am I doing wrong?

I will then export the results to a csv or xlsx file. 

Any help would be greatly appreciated!

Many thanks in advance!

Francesco

[#2 · Accepted solution]

@fstorer 

I can't see anything wrong with what you've done.

I did condense the multiple commands into one line during my testing, which I'll include below for posterity, but it achieves the same result as what you've done (just more efficiently as it's not storing multiple large variables.) But ultimately that doesn't help explain your issue.

If you were using some kind of "ForEach-Object" construct then I'd be looking for the usual suspect involving recursive looping, but you're not doing any of that. It all looks very straight forward.

The question that begs is: perhaps the duplicates genuinely are in the source data? From what you've written, it looks to me like that is indeed the case.

Probably what I'd do is compare the output from the native Search-UnifiedAuditLog to the output from (Search-UnifiedAuditLog).AuditData | ConvertFrom-Json output. If that lines up and there's duplicates in both then that's your answer.

You can work around the duplicate issue by using the "-Unique" parameter on Select-Object statement, but it'd still pay to know for sure exactly where the alleged duplicates exist before going down this path.

My testing one-liner:

(Search-UnifiedAuditLog -StartDate ([datetime]::UtcNow.AddHours(-4)) -EndDate ([datetime]::UtcNow) -RecordType MicrosoftStream -Operations StreamInvokeVideoView).AuditData | ConvertFrom-Json | Select-Object -Property CreationTime, ResourceTitle, UserId, ClientIP, Operation, Workload, ResultStatus | Export-Csv -NoTypeInformation -Path C:\Data\StreamData.csv;

And once again with the "-Unique" parameter - which I would only use if you find the duplicates feature in the source data returned by Search-UnifiedAuditLog:

(Search-UnifiedAuditLog -StartDate ([datetime]::UtcNow.AddHours(-4)) -EndDate ([datetime]::UtcNow) -RecordType MicrosoftStream -Operations StreamInvokeVideoView).AuditData | ConvertFrom-Json | Select-Object -Unique -Property CreationTime, ResourceTitle, UserId, ClientIP, Operation, Workload, ResultStatus | Export-Csv -NoTypeInformation -Path C:\Data\StreamData.csv;

Note: I tend to leave out things like Sort-Object, Group-Object, Format-Table -AutoSize, etc. These don't scale well with large data sets.

If you're adamant on using Sort-Object within the command (as distinct from simply sorting it in Excel as a post processing exercise) then try keeping it as far to the end of the processing stream as possible to minimise the resource utilisation damage it incurs (again, only relevant with large data sets), since this typically means it's growing the sort list using smaller objects (and therefore less memory and processing overhead.) For example:

(Search-UnifiedAuditLog -StartDate ([datetime]::UtcNow.AddHours(-4)) -EndDate ([datetime]::UtcNow) -RecordType MicrosoftStream -Operations StreamInvokeVideoView).AuditData | ConvertFrom-Json | Select-Object -Unique -Property CreationTime, ResourceTitle, UserId, ClientIP, Operation, Workload, ResultStatus | Sort-Object -Property CreationTime | Export-Csv -NoTypeInformation -Path C:\Data\StreamData.csv;

Cheers,

Lain

[#3]

Thank you @LainRobertson for your quick reply!

I can confirm that the duplicates (around 40/50 for every element) are both in the output from the native Search-UnifiedAuditLog and the output from (Search-UnifiedAuditLog).AuditData | ConvertFrom-Json.

The "-unique" parameter does the job but I can't explain why I am getting all those duplicates in the source data.

The script below (based on this article and script from Tony Redmond) also returns unique objects, although I didn't run the "measure-command" to see how much it takes to execute:

$StartDate = (Get-Date).AddDays(-30)
$EndDate = (Get-Date) 
$OutputCSVFile = "C:\Temp\VideoViewers.csv"
$VideoTitle = "<TITLE>"
[array]$Records = (Search-UnifiedAuditLog -RecordType MicrosoftStream -Operations StreamInvokeVideoView -StartDate $StartDate -EndDate $EndDate -Formatted -ResultSize 1000)
$VideoViewers = [System.Collections.Generic.List[Object]]::new() 	
ForEach ($Rec in $Records) {
   $AuditData = $Rec.AuditData | ConvertFrom-Json
   If ($AuditData.ResourceTitle -eq $VideoTitle) {
   $Data = [PSCustomObject]@{
       User     = $Rec.UserIds
       IP       = $AuditData.ClientIP
       ViewDate = $Rec.CreationDate
       Title    = $AuditData.ResourceTitle
       FullURL  = $AuditData.ObjectId }
   $VideoViewers.Add($Data)
   } # End if
} # End ForEach

$VideoViewers | Sort-Object User | Export-Csv -NoTypeInformation $OutputCSVFile

Thanks again for all the help, it is much appreciated!

Francesco