/build/static/layout/Breadcrumb_cap_w.png

Scripting Question


Need help updating SQL to ignore items that use agentless inventory.

03/21/2016 1049 views
Hey guys we use the following SQL query currently for monitoring to see if we have antivirus installed its not working now due to us having agentless inventory items on our k1000.  I have since attempted to update this to make it exclude the agentless items by referencing the machine_labels_JT, but have had no luck.

Can you please take a look my sql query and let me know what you think is causing it to not work.  The new query is returning the same results as the original query, but based on the machine label id it should be excluding it.  This query should now be checking for software titles that match something and if the machine label is not 221, or 223 which are labels i created for our network gear and printers that the kace is monitoring.

Old code below in italics

new code below in bold.

SELECT 
    *,
    UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(LAST_SYNC) AS LAST_SYNC_TIME,
    UNIX_TIMESTAMP(MACHINE.LAST_SYNC) AS LAST_SYNC_SECONDS
FROM
    ORG1.MACHINE
        LEFT JOIN
    KBSYS.KUID_ORGANIZATION ON KUID_ORGANIZATION.KUID = MACHINE.KUID
        LEFT JOIN
    KBSYS.SMMP_CONNECTION ON SMMP_CONNECTION.KUID = MACHINE.KUID
        AND KUID_ORGANIZATION.ORGANIZATION_ID = 1
WHERE
    (((((1 NOT IN (SELECT 
            1
        FROM
            ORG1.SOFTWARE,
            ORG1.MACHINE_SOFTWARE_JT
        WHERE
            MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID
                AND MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID
                AND SOFTWARE.DISPLAY_NAME LIKE '%McAfee%')))
        AND (1 NOT IN (SELECT 
            1
        FROM
            ORG1.SOFTWARE,
            ORG1.MACHINE_SOFTWARE_JT
        WHERE
            MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID
                AND MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID
                AND SOFTWARE.DISPLAY_NAME LIKE '%Symantec%')))
        AND (1 NOT IN (SELECT 
            1
        FROM
            ORG1.SOFTWARE,
            ORG1.MACHINE_SOFTWARE_JT
        WHERE
            MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID
                AND MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID
                AND SOFTWARE.DISPLAY_NAME LIKE '%Trend Micro%'))))


SELECT 
    *,
    UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(LAST_SYNC) AS LAST_SYNC_TIME,
    UNIX_TIMESTAMP(MACHINE.LAST_SYNC) AS LAST_SYNC_SECONDS
FROM
    ORG1.MACHINE
        LEFT JOIN
    KBSYS.KUID_ORGANIZATION ON KUID_ORGANIZATION.KUID = MACHINE.KUID
        LEFT JOIN
    KBSYS.SMMP_CONNECTION ON SMMP_CONNECTION.KUID = MACHINE.KUID
        AND KUID_ORGANIZATION.ORGANIZATION_ID = 1
WHERE
    ((((((1 NOT IN (SELECT 
            1
        FROM
            ORG1.MACHINE_LABEL_JT,
            ORG1.SOFTWARE,
            ORG1.MACHINE_SOFTWARE_JT
        WHERE
            MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID
                AND MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID
                AND SOFTWARE.DISPLAY_NAME LIKE '%McAfee%'
                AND MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID
                AND MACHINE_LABEL_JT.LABEL_ID != '222'
                AND MACHINE_LABEL_JT.LABEL_ID != '221')))
        AND (1 NOT IN (SELECT 
            1
        FROM
            ORG1.MACHINE_LABEL_JT,
            ORG1.SOFTWARE,
            ORG1.MACHINE_SOFTWARE_JT
        WHERE
            MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID
                AND MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID
                AND SOFTWARE.DISPLAY_NAME LIKE '%Symantec%'
                AND MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID
                AND MACHINE_LABEL_JT.LABEL_ID != '222'
                AND MACHINE_LABEL_JT.LABEL_ID != '221')))
        AND (1 NOT IN (SELECT 
            1
        FROM
            ORG1.MACHINE_LABEL_JT,
            ORG1.SOFTWARE,
            ORG1.MACHINE_SOFTWARE_JT
        WHERE
            MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID
                AND MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID
                AND SOFTWARE.DISPLAY_NAME LIKE '%Trend Micro%'
                AND MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID
                AND MACHINE_LABEL_JT.LABEL_ID != '222'
                AND MACHINE_LABEL_JT.LABEL_ID != '221')))
3 Comments   [ + ] Show comments

Comments

  • Would a check on MACHINE.CLIENT_VERSION work for you?
  • That might would do it. so i would just add something after the from, to check the machine.client_version, and then in the where it would be the machine.client_versions = the agent installed clients.
  • I don't have any agentless inventory records to verify, but manual inventory records have a blank client version. Try adding WHERE MACHINE.CLIENT_VERSION <> '' to your query.

Be the first to answer this question

 
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