/build/static/layout/Breadcrumb_cap_w.png

I need to generate the SQL query to generate a report on the patch status for all Windows servers

I’m looking the SQL query to generate a new report for determining the patch update status for all Windows Servers. I got the below SQL code from Copilot, but it didn’t work and threw me an error as shown in the below screenshot. Could anyone advise how to resolve this error and help me to generate the report that I needed? I’m new to KACE SMA and SQL query.

wNCf5Fevpqy8wAAAABJRU5ErkJggg==


0 Comments   [ + ] Show comments

Answers (2)

Posted by: JordanNolan 8 months ago
10th Degree Black Belt
1

The knowledge in the AI is way too old.  I know the AI likes to claim it was updated in 2022, but that is the samething as saying the last time I updated my wardrobe was 2022 when all you did is purchase a new pair of socks.  Technically it is an update, but Stranger Things is calling you to borrow your clothes for their cast.

The query may have worked in Version 7.x, or earler, but not now.


Select
    ORG1.MACHINE.NAME As Computer,
    ORG1.MACHINE.USER_FULLNAME,
    ORG1.MACHINE.OS_NAME,
    ORG1.PATCH_MACHINE_STATUS.PATCH_ID,
    PATCH.PUBLISHER,
    PATCH.TITLE,
    ORG1.PATCH_SCHEDULE_RUN_MACHINE.STATUS As `Patching Status`

From

    ORG1.MACHINE Left Join
    ORG1.PATCH_MACHINE_STATUS On ORG1.PATCH_MACHINE_STATUS.MACHINE_ID = ORG1.MACHINE.ID Left Join
    PATCH On PATCH.ID = ORG1.PATCH_MACHINE_STATUS.PATCH_ID Left Join
    ORG1.PATCH_SCHEDULE_RUN_MACHINE On ORG1.PATCH_SCHEDULE_RUN_MACHINE.MACHINE_ID = ORG1.MACHINE.ID

You can filter this by OS_NAME = "%Server% if you just want Windows Servers.  This query assumes you are checking on the patching the SMA is doing.


Comments:
  • Thanks for your suggestion, I tried to use the above SQL query as you suggested but it didn't work and got the same error as before currently I'm using the Maria DB version is 10.6.14-MariaDB, So I'm going to use the wizard instead of the SQL code - rajeshwaranm 8 months ago
Posted by: JordanNolan 7 months ago
10th Degree Black Belt
1

Sorry, forgot that Patch table was moved to the KBSYS database and has to be explicitely called.  This also assumes you just have one Org and it is Org1


Select

    ORG1.MACHINE.NAME As Computer,
    ORG1.MACHINE.USER_FULLNAME,
    ORG1.MACHINE.OS_NAME,
    ORG1.PATCH_MACHINE_STATUS.PATCH_ID,
    KBSYS.PATCH.PUBLISHER,
    KBSYS.PATCH.TITLE,
    ORG1.PATCH_SCHEDULE_RUN_MACHINE.STATUS As `Patching Status`

From

    ORG1.MACHINE Left Join
    ORG1.PATCH_MACHINE_STATUS On ORG1.PATCH_MACHINE_STATUS.MACHINE_ID = MACHINE.ID Left Join
    KBSYS.PATCH On PATCH.ID = ORG1.PATCH_MACHINE_STATUS.PATCH_ID Left Join
    ORG1.PATCH_SCHEDULE_RUN_MACHINE On ORG1.PATCH_SCHEDULE_RUN_MACHINE.MACHINE_ID = MACHINE.ID

You could add the filter:

Where MACHINE.OS_NAME Like '%Server%'


 
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