I can imagine a case where it might be helpful to process the raw trail files of Sophos Health found under:
%ProgramData%\Sophos\Health\Event Store\Trail\
Note: It is possible to also get this information from Live Discover using the "sophos_events_summary" and "sophos_events_details" tables in your queries.
For the purposes of this post, the following query will prettify the most 5 last written JSON files in descending order:
($(Get-ChildItem -Path "$env:programdata\Sophos\Health\Event Store\trail") | Sort-Object LastWriteTime -Descending | Select-Object -First 5).name | %{convertfrom-json $([string]$(gc -path "$env:programdata\Sophos\Health\Event Store\trail\$_") )}
If you wanted to print just the "threatName" field for each JSON file you could with:
($(Get-ChildItem -Path "$env:programdata\Sophos\Health\Event Store\trail") | Sort-Object LastWriteTime -Descending | Select-Object -First 2).name | %{(convertfrom-json $([string]$(gc -path "$env:programdata\Sophos\Health\Event Store\trail\$_"))).threatName}
To create a report (report.csv), you should be able to paste:
$Directory=$env:ProgramData + "\Sophos\Health\Event Store\trail\"
$OutData = @()
Get-ChildItem -Path $Directory -File -Filter "*.json" | Foreach-Object {
$j = [System.IO.File]::ReadLines($_.FullName) | ConvertFrom-Json
$Arr = New-Object PSObject
$Arr | Add-Member -Name "fileName" -MemberType NoteProperty -Value $_.Name
$Arr | Add-Member -Name "timeStamp" -MemberType NoteProperty -Value $j.timeStamp
$Arr | Add-Member -Name "app" -MemberType NoteProperty -Value $j.app
$Arr | Add-Member -Name "severity" -MemberType NoteProperty -Value $j.severity
$Arr | Add-Member -Name "resourceId" -MemberType NoteProperty -Value $j.resourceId
$Arr | Add-Member -Name "threatName" -MemberType NoteProperty -Value $j.threatName
$Arr | Add-Member -Name "threatType" -MemberType NoteProperty -Value $j.threatType
$Arr | Add-Member -Name "location" -MemberType NoteProperty -Value $j.location
$Arr | Add-Member -Name "path" -MemberType NoteProperty -Value $j.path
$Arr | Add-Member -Name "paths" -MemberType NoteProperty -Value $j.paths
$Arr | Add-Member -Name "updateSummary" -MemberType NoteProperty -Value $j.updateSummary
$Arr | Add-Member -Name "serviceName" -MemberType NoteProperty -Value $j.serviceName
$Arr | Add-Member -Name "counterName" -MemberType NoteProperty -Value $j.counterName
$Arr | Add-Member -Name "componentName" -MemberType NoteProperty -Value $j.componentName
$Arr | Add-Member -Name "sequence" -MemberType NoteProperty -Value $j.sequence
$Arr | Add-Member -Name "showNotification" -MemberType NoteProperty -Value $j.showNotification
$Arr | Add-Member -Name "userName" -MemberType NoteProperty -Value $j.userName
$Arr | Add-Member -Name "userSid" -MemberType NoteProperty -Value $j.userSid
$Arr | Add-Member -Name "reboot" -MemberType NoteProperty -Value $j.reboot
$Arr | Add-Member -Name "id" -MemberType NoteProperty -Value $j.id
$Arr | Add-Member -Name "familyId" -MemberType NoteProperty -Value $j.familyId
$OutData += $Arr
}
$a = $OutData | ConvertTo-CSV -NoTypeInformation | Out-File -FilePath Report.csv
This might be useful if you were trying to make sense of end-user events.
Regards,
Jak
