Hi All, I was looking for a report on Applicable and Installed Updates Collection Wise and got this below query on the Internet and want to create a SCCM report with this below query. How to make this query working in SCCM report? Do I need to add prompts, if yes please guide me on adding prompts.

SELECT
DISTINCT
SYS.Name0
AS [Server Name], SIS.SMS_Installed_Sites0 AS [Site Code], UCS.Status AS [Patch Status Code],
CASE WHEN UCS.Status = '2' THEN 'Applicable' WHEN UCS.Status = '3' THEN 'Installed' ELSE '' END AS 'Patch Status', UI.BulletinID AS [Bulletin ID],
UI
.ArticleID AS [Article ID], UI.
Title
FROM
v_R_System AS SYS LEFT OUTER
JOIN
v_Update_ComplianceStatusAll
AS UCS ON SYS.ResourceID = UCS.ResourceID INNER
JOIN
v_UpdateInfo
AS UI ON UCS.CI_ID = UI.CI_ID INNER
JOIN
v_RA_System_SMSInstalledSites
AS SIS ON SYS.ResourceID = SIS.
ResourceID
WHERE
(UCS.Status IN ('2', '3')) AND (UI.ArticleID IN ('972270', '974392', '973904', '969947')) AND (SYS.Name0
IN
(SELECT DISTINCT v_FullCollectionMembership.
Name
FROM v_FullCollectionMembership INNER
JOIN
v_R_System
ON v_R_System.ResourceID = v_FullCollectionMembership.ResourceID AND v_R_System.Active0 = 1 AND
v_FullCollectionMembership
.CollectionID IN ('Collection ID'
)))
ORDER
BY
[Patch Status Code]

or you could use a standard report?

Management 1 - Updates required but not deployed
Description: This report returns all vendor-specific software updates that have been detected as required on clients but that have not been deployed to a specific collection. To limit the amount of information returned, you can specify the software update class.
- Specify Collection, Vendor, Update Class (optional)
- Lists each update applicable to at least one computer, with the number and percentage of computers that require the update
- Links to no other reports

Thank you Don. Let me tell you my exact requirement.

Suppose I have a collection "X" for Win2K3 servers and 10 servers are members of that collection. Now I need a report, on running that, the report will ask me to input  collection ID and it will show me all the updates installed on those 10 server in a single page. For applicable updates, I can create an another report with the same criteria/collection wise.

I am very new to reporting and looking for help.

What you are asking for is a unrealistic report. The report will overwhelm anyone with just a few computers. This is easy to see by looking at Compliance 6 - Specific computer. If I look at my site server (win2k3) it has over 300 software updates listed for that server along, 336 (or 4.2 pages) to be exact. Now assume that all 10 servers are the same that would be 3360 lines or 42 pages! (3360/80 lines/page).

IMO, You need to re-think this request.

What you are asking for is a unrealistic report. The report will overwhelm anyone with just a few computers. This is easy to see by looking at Compliance 6 - Specific computer. If I look at my site server (win2k3) it has over 300 software updates listed for that server along, 336 (or 4.2 pages) to be exact. Now assume that all 10 servers are the same that would be 3360 lines or 42 pages! (3360/80 lines/page).

IMO, You need to re-think this req

Hi Monnens,

Please check the below thread i have shared the query for the requirement.

http://social.technet.microsoft.com/Forums/systemcenter/en-US/ae850a4f-e819-4831-9d75-a6439b1910d1/patch-compliance-report-to-triage-patching?forum=configmgrreporting#c335631c-9b2e-4604-9bde-960aeaebd0e1

Actually it's a very usefull report that Bikram want to create. I need this kind of report too, to automatically send a notification to our customers with installed updates/server. It should look like the example below:

SERVER 1:

• update a
• update b

SERVER B:

• update b
• update c

 Does some one have a query for this ? (SCCM 2012 R2)

So you plan on sent them 42 page if they just have 10 servers? This is not a useful report at all. no one will ever read it as it is too long.

If you plan to send this to clients you need to shorten it, in order for them to read it.

Start with this report, it will provide you with a count of all the missing SU on PC. Then you can drill have it drill into the Compliance 6 - Specific computer report. http://smsug.ca/blogs/garth_jones/archive/2009/02/25/patch-compliance-progression-report.aspx

Actually it's a very usefull report that Bikram want to create. I need this kind of report too, to automatically send a notification to our customers with installed updates/server. It should look like the example below:

SERVER 1:

• update a
• update b

SERVER B:

• update b
• update c

 Does some one have a query for this ? (SCCM 2012 R2)

So you plan on sent them 42 page if they just have 10 servers? This is not a useful report at all. no one will ever read it as it is too long.

If you plan to send this to clients you need to shorten it, in order for them to read it.

Start with this report, it will provide you with a count of all the missing SU on PC. Then you can drill have it drill into the Compliance 6 - Specific computer report. http://smsug.ca/blogs/garth_jones/archive/2009/02/25/patch-compliance-progression-report.aspx

If you are choosing only missing then definitely yes  it will be small ,if you need the installed patches as well then it will be quite big and you cannot send reports more than 8 MB based on the limit in outlook delivery.

If you need both then the above report which i shared will help you but the report will be quite big in size though , however you can edit the query and and untick the installed so you will get the missing patches on your environment for each servers viceversa for the installed only report.

What if your environments are up-to-date and monthly patched ... Your report won't take 20 pages ...

What if your environments are up-to-date and monthly patched ... Your report won't take 20 pages ...

Exactly how are you going to filter out old SUs so that you can reduce the number

Yes it can be done and please run the query which i have shared in the thread

I again pasted the query for you below..

SELECT DISTINCT

SYS.Name0 AS [Server Name], CASE WHEN UCS.Status = '2' THEN 'Applicable' WHEN UCS.Status = '3' THEN 'Installed' ELSE '' END AS 'Patch Status',

UI.BulletinID AS [Bulletin ID], UI.ArticleID AS [Article ID], UI.Title

FROM         v_R_System AS SYS LEFT OUTER JOIN

                      v_Update_ComplianceStatusAll AS UCS ON SYS.ResourceID = UCS.ResourceID INNER JOIN

                      v_UpdateInfo AS UI ON UCS.CI_ID = UI.CI_ID

WHERE     (UCS.Status IN ('2', '3')) AND (SYS.Name0 IN (SELECT DISTINCT v_FullCollectionMembership.Name

FROM v_FullCollectionMembership INNER JOIN

     v_R_System ON v_R_System.ResourceID = v_FullCollectionMembership.ResourceID AND v_R_System.Active0 = 1 AND

                                                   v_FullCollectionMembership.CollectionID IN ('XXX00000')))

What I need is a report of installed updates from the last patch round per servers in a collection. So not a list of all updates that are applied to the servers. 

Yes it can be done and please run the query which i have shared in the thread

I again pasted the query for you below..

How exact does this solve the problem of knowing what SU where applied after the last round of SU? All this query does it show you all SU that have been applied or need to be applied. It doesn't show you which SU have been applied since the last round of SU was applied.

Again I will state it, this report will product a ton of data and it NOT useful due to the sheer volume of data it produces. No one will ever read it.  

My manager just asked for this on 2500 computers.  On 800 computer it was 32700 lines!

I want to see his reaction when he opens up the file to see this is exactly what you mentioned, not useful!

Hi Kannan

Thanks for this query is very usefull

I need to het a colum for "Not Applicable" patch & The time Pacth installed How we can get it

what is the Status value we have to use and what databse value for time of installtion

Is there any way please help me

Advance Thanks :)