/build/static/layout/Breadcrumb_cap_w.png

Request for assistance with SQL query

Hi everyone.  I'm trying to modify an existing script which grabs missing patches from all devices.  I want to tweak that so I the report only shows devices running Windows Server 2019, or Windows 10, or Windows 11.   Right now, I'm manually deleting rows in Excel to get the info I want and it's a pain.  How do I modify the report so I only see specific OSes?

SELECT MACHINE.NAME,

       PATCH.PUBLISHER,

       PATCH.TITLE,

       PATCH_MACHINE_STATUS.DETECT_STATUS

       OS_NAME AS OPERATING SYSTEM,  (------------------I've added this line but I know it's only a partial line, I don't know what else to add to filter out the OS)

       MACHINE_LOCATION

  FROM (PATCH_MACHINE_STATUS PATCH_MACHINE_STATUS

        INNER JOIN KBSYS.PATCH PATCH

           ON (PATCH_MACHINE_STATUS.PATCH_ID = PATCH.ID))

       INNER JOIN MACHINE MACHINE

          ON (PATCH_MACHINE_STATUS.MACHINE_ID = MACHINE.ID)

WHERE (PATCH_MACHINE_STATUS.DETECT_STATUS = 'NOTPATCHED')


0 Comments   [ + ] Show comments

Answers (3)

Answer Summary:
Posted by: KevinG 1 year ago
Red Belt
0

Top Answer

The filter is in the form of a "where" clause.

You can change the current where clause from WHERE (PATCH_MACHINE_STATUS.DETECT_STATUS = 'NOTPATCHED')

to

WHERE PATCH_MACHINE_STATUS.DETECT_STATUS = 'NOTPATCHED' and MACHINE.OS_NAME in ( Windows Server 2019, Windows 10, Windows 11)      <- See my note about retrieving the exact OS_NAME's to build the list.

To filter on only one OS_NAME you can use "and MACHINE.OS_NAME = 'Windows 10' "


Also change OS_NAME AS OPERATING SYSTEM,

To

MACHINE.OS_NAME AS OPERATING SYSTEM,



Run  the following select command in a report to get the exact name of the Operating System name to be used in your where clause list.

select distinct OS_NAME from MACHINE



Comments:
  • Thanks very much for this. I'm getting an error but I'm not sure where on line 7.

    "mysqli error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'MACHINE.OS_NAME AS OPERATING SYSTEM)\n INNER JOIN KBSYS.PATCH PATCH\n ...' at line 7]"

    SELECT MACHINE.NAME,
    PATCH.PUBLISHER,
    PATCH.TITLE,
    PATCH_MACHINE_STATUS.DETECT_STATUS
    MACHINE_LOCATION
    FROM (PATCH_MACHINE_STATUS PATCH_MACHINE_STATUS
    MACHINE.OS_NAME AS OPERATING SYSTEM,
    INNER JOIN KBSYS.PATCH PATCH
    ON (PATCH_MACHINE_STATUS.PATCH_ID = PATCH.ID))
    INNER JOIN MACHINE MACHINE
    ON (PATCH_MACHINE_STATUS.MACHINE_ID = MACHINE.ID)
    WHERE PATCH_MACHINE_STATUS.DETECT_STATUS = 'NOTPATCHED' and MACHINE.OS_NAME in (Windows Server 2019 Standard x64) - Tramer61 1 year ago
Posted by: npovey 1 year ago
Senior White Belt
0



Comments:
  • The posted SQL statement contained syntax errors.
    * "MACHINE.OS_NAME AS OPERATING SYSTEM, " was after the "FROM"
    * "Missing comma after field names in the select list." PATCH_MACHINE_STATUS.DETECT_STATUS,
    * Notice that quotes have been placed around 'OPERATING SYSTEM' and , 'Windows Server 2019 Standard x64' in the updated SQL statement.

    Not sure what data you wanted with the machine_location table?


    Start with this SQL statement and, modify it to meet your requirements.


    select MACHINE.NAME,
    MACHINE.OS_NAME AS 'OPERATING SYSTEM',
    PATCH.PUBLISHER,
    PATCH.TITLE,
    DETECT_STATUS
    from PATCH_MACHINE_STATUS
    join MACHINE on PATCH_MACHINE_STATUS.MACHINE_ID = MACHINE.ID
    join KBSYS.PATCH on PATCH_MACHINE_STATUS.PATCH_ID = KBSYS.PATCH.ID
    where DETECT_STATUS = 'NOTPATCHED' and MACHINE.OS_NAME in ('Windows Server 2019 Standard x64') - KevinG 1 year ago
Posted by: Tramer61 1 year ago
White Belt
0
Thanks so much your you help with this.  It's working as I need it now. Very much appreciated!

Comments:
  • Thanks for the update. Glad to hear it is working for you. :-) - KevinG 1 year ago
 
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