Hello, I have the following SQL report which is supposed to group by machine name and display all patches that have been successfully patched. My problem is that I do not really know how to group items on the report.

SELECT M.NAME AS MACHINE_NAME,
P.IDENTIFIER AS KB_ARTICLE,
P.TITLE AS DISPLAY_NAME,
S.STATUS_DT AS STATUS_DATE
FROM MACHINE_LABEL_JT MJ,
MACHINE M,
LABEL L,
PATCHLINK_MACHINE_STATUS S,
KBSYS.PATCHLINK_PATCH P
WHERE M.ID=MJ.MACHINE_ID
AND L.ID=MJ.LABEL_ID
AND M.ID = S.MACHINE_ID
AND S.PATCHUID = P.UID
AND S.STATUS = 'PATCHED'
AND (( 1 IN (SELECT 1
FROM PATCHLINK_PATCH_STATUS
WHERE P.UID = PATCHLINK_PATCH_STATUS.PATCHUID
AND PATCHLINK_PATCH_STATUS.STATUS IN ( 0 )) ))
AND M.NAME RLIKE 'Audit'
ORDER BY MACHINE_NAME,
P.TITLE

In addition i'm having an issue where only items scanned in July and August are coming out but I would like to see year to date, as you can see I do not have any filters on dates.
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
You can add a GROUP BY after the where statement in your query.

It also looks like there's duplicates in the report, so another option would be a SELECT DISTINCT.

If your trying to group by items on the report in KACE, add MACHINE_NAME as your break on column under the report details.
Answered 08/12/2011 by: dchristian
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity