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.
Please log in to comment

There are no answers at this time

Answers

Answer this question or Comment on this question for clarity