/build/static/layout/Breadcrumb_cap_w.png

Scripting Question


Patch Report

11/09/2015 1306 views

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:
0 Comments   [ + ] Show comments

Comments


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
Third Degree Blue Belt

All 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

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ