Need help updating SQL to ignore items that use agentless inventory.
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
-
Would a check on MACHINE.CLIENT_VERSION work for you? - grayematter 8 years ago
-
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. - brianfulcher15 8 years ago
-
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. - grayematter 8 years ago
Answers (0)
Please log in to answer
Be the first to answer this question
