/build/static/layout/Breadcrumb_cap_w.png

Patch Report

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


0 Comments   [ + ] Show comments

Answers (2)

Answer Summary:
Posted by: getElementById 5 years ago
Third Degree Blue Belt
1

Top Answer

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
Posted by: amatsyplyuk 5 years ago
White Belt
0
Thanks a lot. This is what I was looking for. I need to learn more about SQL query.

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

Share

 
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