/build/static/layout/Breadcrumb_cap_w.png

Kace SQL customized report - ADD agent connection date and time

Hello,

I do have a customized report to keep track of patchs installed in our systems where i would like to add "agent last connection" column for each device.

I have no clue with queries  so If anyone could help would be appreciated !

Thank you

------------------------------------------------------------------------

SELECT 

    M.NAME AS MACHINE_NAME,

    SYSTEM_DESCRIPTION,

    OS_NAME AS WINDOWS_VERSION,

    SUM(MS.DETECT_STATUS = 'PATCHED') AS PATCHED,

    SUM(MS.DETECT_STATUS = 'NOTPATCHED') AS NOTPATCHED,

    concat("http://kace.mycompany.com/adminui/machine.php?ID=", M.id) as linkKACE

FROM 

    PATCH_MACHINE_STATUS MS

        JOIN

    KBSYS.PATCH PP ON (PP.ID = MS.PATCH_ID)

        JOIN

    PATCH_STATUS PPS ON (PPS.PATCH_ID = PP.ID)

        JOIN

    MACHINE M ON (M.ID = MS.MACHINE_ID)

WHERE

    PPS.STATUS = 0 AND PP.IS_SUPERCEDED = 0

    AND PPS.IS_SUPERCEDED = 0

GROUP BY MS.MACHINE_ID

ORDER BY NOTPATCHED DESC



0 Comments   [ + ] Show comments

Answers (2)

Answer Summary:
Posted by: KevinG 4 months ago
Red Belt
0

Added Last Inventory and Last Connection date/time


SELECT

    M.NAME AS MACHINE_NAME,

    SYSTEM_DESCRIPTION,

    OS_NAME AS WINDOWS_VERSION,

    SUM(MS.DETECT_STATUS = 'PATCHED') AS PATCHED,

    SUM(MS.DETECT_STATUS = 'NOTPATCHED') AS NOTPATCHED,

    M.LAST_INVENTORY,

    KBSYS.SMMP_CONNECTION.CONNECT_TIME as 'Last Connection',

    concat("http://kace.mycompany.com/adminui/machine.php?ID=", M.id) as 'Link to Device'

FROM PATCH_MACHINE_STATUS MS

        JOIN KBSYS.PATCH PP ON (PP.ID = MS.PATCH_ID)

        JOIN PATCH_STATUS PPS ON (PPS.PATCH_ID = PP.ID)

        JOIN MACHINE M ON (M.ID = MS.MACHINE_ID)

        JOIN KBSYS.SMMP_CONNECTION ON M.KUID = KBSYS.SMMP_CONNECTION.KUID

WHERE PPS.STATUS = 0 AND PP.IS_SUPERCEDED = 0 AND PPS.IS_SUPERCEDED = 0

GROUP BY MS.MACHINE_ID ORDER BY NOTPATCHED DESC

Posted by: J84 4 months ago
White Belt
0

Perfect ! thank you very much :)


Comments:
  • Thanks for the update. Glad to hear it is working for you. :-) - KevinG 4 months ago

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