I am new to SQL query need help editing this query. Its not adding all computers in our inventory into report. it looks like it pulls only those that are being patched by dell kace is there a way to adjust this script to create report for all computers?  

 

SELECT M.NAME AS MACHINE_NAME,
SUM(MS.STATUS='PATCHED') AS PATCHED,
SUM(MS.STATUS='NOTPATCHED') AS NOT_PATCHED,
ROUND(((SUM(MS.STATUS='PATCHED'))*100)/(SUM(MS.STATUS='PATCHED')+SUM(MS.STATUS='NOTPATCHED'))) AS PERCENT_PATCHED
FROM PATCHLINK_MACHINE_STATUS MS
JOIN KBSYS.PATCHLINK_PATCH PP ON (PP.UID = MS.PATCHUID)
JOIN PATCHLINK_PATCH_STATUS PPS ON (PPS.PATCHUID = PP.UID)
JOIN MACHINE M ON (M.ID = MS.MACHINE_ID)
JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
GROUP BY M.NAME
ORDER BY PERCENT_PATCHED, M.NAME

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answer Chosen by the Author

1
Let me know if this gives you the results you want. The ones with no values just show as null. I commented out the parts that are not necessary but otherwise left the statement mostly intact so you could see the differences. 


SELECT 
M.NAME AS MACHINE_NAME, 
SUM(MS.STATUS='PATCHED') AS PATCHED,
SUM(MS.STATUS='NOTPATCHED') AS NOT_PATCHED,
ROUND(((SUM(MS.STATUS='PATCHED'))*100)/(SUM(MS.STATUS='PATCHED')+SUM(MS.STATUS='NOTPATCHED'))) AS PERCENT_PATCHED
    
FROM 
MACHINE M
    LEFT JOIN PATCHLINK_MACHINE_STATUS MS
ON (M.ID = MS.MACHINE_ID)
LEFT JOIN KBSYS.PATCHLINK_PATCH PP 
ON (PP.UID = MS.PATCHUID)
/*JOIN PATCHLINK_PATCH_STATUS PPS 
ON (PPS.PATCHUID = PP.UID)*/ /* NOT NEEDED */
/*JOIN MACHINE_LABEL_JT ML 
ON (M.ID = ML.MACHINE_ID)*/    /* NOT NEEDED */
/*JOIN LABEL L 
ON (ML.LABEL_ID = L.ID) */ /* NOT NEEDED */
        
GROUP BY 
M.NAME
ORDER BY 
PERCENT_PATCHED, M.NAME
Answered 11/09/2015 by: getElementById
Second Degree Blue Belt

Please log in to comment

Answers

0
Thanks a lot. This is what I was looking for. I need to learn more about SQL query.
Answered 11/09/2015 by: amatsyplyuk
White Belt

Please log in to comment
Answer this question or Comment on this question for clarity
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja
Nine Simple (but Critical) Tips for Effective Patch Management
This paper reviews nine simple tips that can make patch management simpler, more effective and less expensive.

Share