/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


Custom SQL report for K1000

10/18/2016 1187 views
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

Comments


Be the first to answer this question

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
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