Forum Discussion
Stream Audit Log - Script to check who viewed/liked a video returns the same element multiple times
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
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
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 https://office365itpros.com/2020/05/29/tracking-video-uploads-stream/ 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 $OutputCSVFileThanks again for all the help, it is much appreciated!
Francesco
