I need to generate the SQL query to generate a report on the patch status for all Windows servers
Answers (2)
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 1 month ago
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%'
