I want to create a report that mirrors the WSUS report "..Include Updates - Is Needed, Failed, No Status" I can't seem to find the correct variable or integer combination. Does anyone have some insight into this mechanism or know where I can find the configuration options?
-
Through some creative use of the reporting interface, you can get enumerations of the fields with "Friendly Names" (e.g. Update Installation State (Friendlly Name), by placing that field in a report definition, and then viewing the SQL code that is generated from that report definition. You can also do this by simply selecting a report where that field is already defined (e.g. the System report: Computer Update Status will serve this purpose).
Select the report in the report list, right click, and select "Properties" from the context menu.
The Report Definition Properties will show the SQL used to run the report, and in this instance what we're interested in is the CASE statement that converts "Update Installation State", which is an Integer value stored in the database to "Update Installation State (Friendly Name)" which isn't actually stored in the database at all! (It's a programmatically derived field from the integer field actually stored.)
CASE dbo.ComputerUpdateStatus.UpdateInstallationState
WHEN 0 THEN 'Unknown'
WHEN 1 THEN 'NotApplicable'
WHEN 2 THEN 'NotInstalled'
WHEN 3 THEN 'Downloaded'
WHEN 4 THEN 'Installed'
WHEN 5 THEN 'Failed'
WHEN 6 THEN 'Installed Pending Reboot'
ELSE 'Unknown'
END
So, to filter a report for Needed/Failed/NoStatus you can use "Update Installation State" IN LIST {0,2,3,5,6}
btw, although a bit more tedious, you can also do
"Update Installation State (Friendly Name)" IN LIST {'Unknown', 'NotInstalled', 'Downloaded', 'Failed', 'Installed Pending Reboot'}