Patches by machine
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.
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
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
dchristian
12 years ago
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.
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.
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.
