In the section "Significant registry keys" is a VBScript which will show you when Sophos has last updated its virus definitions on the end node.
I have converted the VBScript into Powershell. You can add this as a K1000 custom inventory rule, so at each check-in, you will get the latest status on the last update time of the anti-virus definitions.
Here is the Powershell custom inventory rule. At the end of the last line, change the -5 to your time zone offset.
FileExists(C:\Windows\system32\WindowsPowerShell\v1.0\powershell.exe) AND ShellCommandTextReturn(cmd /q /c powershell.exe -command "$f=[DATETIME] '01/01/1970 00:00:00'; $f.AddSeconds((Get-ItemProperty -Path 'REGISTRY::HKLM\SOFTWARE\Sophos\AutoUpdate\UpdateStatus' LastUpdateTime).LastUpdateTime) | ForEach-Object {'{0:yyyy}-{0:MM}-{0:dd} {0:HH}:{0:mm}:{0:ss}' -f ($_.AddHours(-5))}")
Here is a K1000 report that you can run to see if any nodes have not updated in the past 2 weeks from the last check-in. You must change the software ID to the custom inventory rule ID from above.
SELECT MACHINE.NAME AS 'Hostname', LAST_SYNC, CAST(SUBSTRING(MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE FROM -24 FOR 19) AS DATETIME) AS 'SOPHOS LastUpdateStatus'
FROM MACHINE
JOIN MACHINE_CUSTOM_INVENTORY ON MACHINE.ID = MACHINE_CUSTOM_INVENTORY.ID
WHERE MACHINE_CUSTOM_INVENTORY.SOFTWARE_ID = '29754'
AND MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE < DATE_SUB(LAST_SYNC, INTERVAL 14 DAY)
ORDER BY LAST_SYNC DESC
Caveats:
- If a computer does not have Sophos or the LastUpdateStatus regkey does not exist, the LastUpdateStatus will be reported as year 1969.
- If a computer was off for a long time and it synced before the anti-virus updated, it may be a false positive.
- In the Powershell custom inventory rule, I had to use ShellCommandTextReturn instead of ShellDateReturn, because otherwise the time was reported incorrectly in the result by several hours. That is why the K1000 report I use the datetime cast.
- Not sure about how to change the Powershell time offset if your org runs in multiple time zones. Maybe read it from the registry.
- K1000 report can use some tweaking. Any suggestions welcome.
Comments