Custom SQL report for K1000
Hello,
I'm trying to create a report that shows that we have certain software installed. I've followed some guides and have been able to run a SQL query for the software separately but running into problems when trying to combine the search parameters. The working query I have is as below which is from a dell support page:
select
S.DISPLAY_NAME as SOFTWARE_NAME,
S.DISPLAY_VERSION as VERSION,
M.NAME as MACHINE_NAME,
M.USER_FULLNAME as LAST_USER
from
SOFTWARE S
left join
MACHINE_SOFTWARE_JT MSJ ON S.ID = MSJ.SOFTWARE_ID
left join
MACHINE M ON MSJ.MACHINE_ID = M.ID
where
S.DISPLAY_NAME like 'Malwarebytes\' Managed Client'
and not IS_PATCH
order by S.DISPLAY_NAME , S.DISPLAY_VERSION , M.NAME
This and a search for Symantec Endpoint Protection both work fine when running the query in MySQL Workbench. I've tried several different variations of the below with no progress:
select
S.DISPLAY_NAME as SOFTWARE_NAME,
S.DISPLAY_VERSION as VERSION,
M.NAME as MACHINE_NAME,
M.USER_FULLNAME as LAST_USER
from
SOFTWARE S
left join
MACHINE_SOFTWARE_JT MSJ ON S.ID = MSJ.SOFTWARE_ID
left join
MACHINE M ON MSJ.MACHINE_ID = M.ID
where
S.DISPLAY_NAME like 'Malwarebytes\' Managed Client'
and 'Symantec Endpoint Protection'
and not IS_PATCH
order by S.DISPLAY_NAME , S.DISPLAY_VERSION , M.NAME
The end goal of this query is to generate a report confirming that our machines have both Symantec and Malwarebytes installed and the last patching date/results as I've seen elsewhere on the site. Any help would be greatly appreciated.
Update - 20/10/16: Appears I was putting my and/or in the wrong place. I've gotten what I needed with the following SQL query with some help from KACE support.
select
S.DISPLAY_NAME as SOFTWARE_NAME,
S.DISPLAY_VERSION as VERSION,
M.NAME as MACHINE_NAME,
M.IP as MACHINE_IP
from
SOFTWARE S
left join
MACHINE_SOFTWARE_JT MSJ ON S.ID = MSJ.SOFTWARE_ID
left join
MACHINE M ON MSJ.MACHINE_ID = M.ID
where
S.DISPLAY_NAME like 'Malwarebytes\' Managed Client'
and not IS_PATCH
or S.DISPLAY_NAME like 'Symantec Endpoint Protection'
and not IS_PATCH
order by S.DISPLAY_NAME , S.DISPLAY_VERSION , M.NAME
Now I've just got to add patching results to the report.
0 Comments
[ + ] Show comments
Answers (0)
Please log in to answer
Be the first to answer this question
