SQL Report: Windows Service Pack Level by OS

One of the problems with the canned compliance reports is the lack of easily isolated Service Pack information, so I created a report that allows you to select Windows 2000, XP, or Vista and then filter out the most recent SP for that OS.

1. Create a new report
2. Give your report a meaningful name
3. Enter SQL directly
4. Copy and paste this SQL into the text box
5. Click finish (testing won't work here, because we need to create a few parameters first)

*Continued below*

SELECT T0.[Name] AS 'Name', 
T0.[Domain] AS 'Domain', 
T0.[Last Logon User] AS 'Last Logon User', 
T1.[OS Name] AS 'OS Name', 
T1.[Service Pack] AS 'Service Pack' 

FROM [Inv_AeX_OS_Operating_System] T1 
RIGHT OUTER JOIN [Inv_AeX_AC_Identification] T0 
ON T1.[_ResourceGuid] = T0.[_ResourceGuid] 

WHERE T1.[OS Name] LIKE %version%
AND T1.[Service Pack] NOT LIKE %spchoice%

ORDER BY T0.[Name] ASC

Create version parameter:

This will allow you to select which version of windows you want to query.

1. Click the pencil icon to edit the report
2. Click the "New Parameter" button
3. For Name, type "version" (without the quotations)
4. Select "Dropdown" from the dropdown menu
5. Place a check in the "Prompt user for value..." box
6. For User Prompt, type "Select Windows Version" (without the quotations)
7. Make sure "List Type" is set to "Fixed List"
8. Make sure "Value Type" is set to "String"
9. For Value, Type "2000;%2000%|XP;%XP%|Vista;%Vista%" (without the quotations)
10. Click "OK"

Create spchoice parameter:

This will allow you to select the most recent SP level and will filter those from your report

1. Click the "New Parameter" button
2. For Name, type "spchoice" (without the quotations)
3. Select "Dropdown" from the dropdown menu
4. Place a check in the "Prompt user for value..." box
5. For User Prompt, type "Select Service Pack Level" (without the quotations)
6. Make sure "List Type" is set to "Fixed List"
7. Make sure "Value Type" is set to "String"
8. For Value, Type "SP1;%1%|SP2;%2%|SP3;%3%|SP4;%4%" (without the quotations)
9. Click "OK"

Now just click "Apply" and your report is ready for use.