jscott607
Posts: 210
Score: 0
Joined: 9/20/2005
Status: offline
|
Hello. I am having problems getting a web report to pull up what I need. The report is suppose to display the details of a patch listing it as either applicable or installed with the system name and user name.
If I remove the name and user name, it displays the counts. I assume my problem is with how I am using SUM. However, I have tried quite a few different ways of pulling the information and I cannot figure it out.
Any assistance would be greatly appreciated.
SELECT sys.Name0 AS [System Name], sys.User_Name0 AS [User Name], pse.Title0 AS Description, pse.ID0 AS [Update ID], rl.Country,
SUM(CASE WHEN pse.status0 = 'INSTALLED' THEN 1 END) AS Installed,
SUM(CASE WHEN pse.status0 = 'APPLICABLE' THEN 1 END) AS Applicable
FROM v_GS_PATCHSTATEEX AS pse INNER JOIN
v_R_System AS sys ON pse.ResourceID = sys.ResourceID INNER JOIN
v_ApplicableUpdatesSummaryEx ON pse.UpdateID0 = v_ApplicableUpdatesSummaryEx.UniqueUpdateID INNER JOIN
Regional_Location AS rl ON sys.AD_Site_Name0 = rl.AdSite
WHERE (v_ApplicableUpdatesSummaryEx.ScanAgent = 'Microsoft Updates Tool') AND (v_ApplicableUpdatesSummaryEx.ID LIKE 'MS0%') AND
(v_ApplicableUpdatesSummaryEx.TimeAuthorized IS NOT NULL) AND (v_ApplicableUpdatesSummaryEx.InstalledCounts <> 0) AND
(pse.ID0 LIKE 'MS%') AND (rl.Country = @country) AND (pse.Title0 = @title)
GROUP BY pse.ID0, pse.Title0, rl.Country, sys.Name0, sys.User_Name0
HAVING (COUNT(*) > 10)
ORDER BY [Update ID], Description
|
Printable Version
Problem with PatchState report pulling system name and ... - 
New Messages
No New Messages
Hot Topic w/ New Messages
Hot Topic w/o New Messages
Locked w/ New Messages
Locked w/o New Messages
Post New Thread