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')
Answers (3)
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 4 months ago
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 4 months ago
