SQL Query Help
I have a query that is checking if Sophos exists. It should be a simple query, but it is returning machines with Sophos installed. Here is my full query. I've tried taking out the asset info, and LAST_SYNC info, but it still returns the same results.
select DISTINCT M.NAME, IP, MAC, USER_LOGGED as User, DOMAIN, A5.FIELD_25 as 'UTSW Tag#', A5.FIELD_36 as 'Custodian', LAST_SYNC FROM MACHINE M LEFT JOIN MACHINE_SOFTWARE_JT MJT ON M.ID = MJT.MACHINE_ID LEFT JOIN SOFTWARE S ON MJT.SOFTWARE_ID = S.ID LEFT JOIN ASSET A ON A.MAPPED_ID = M.ID AND A.ASSET_TYPE_ID=5 LEFT JOIN ASSET_DATA_5 A5 ON A5.ID = A.ASSET_DATA_ID where (LAST_SYNC > CURDATE() - INTERVAL 10 DAY) AND (S.DISPLAY_NAME NOT LIKE 'SOPHOS%') order by M.NAME
If I run it this way the it returns the correct results. My question is why wouldn't the top one work? Seems simple enough.
select DISTINCT M.NAME, IP, MAC, USER_LOGGED as User, DOMAIN, A5.FIELD_25 as 'UTSW Tag#', A5.FIELD_36 as 'Custodian', LAST_SYNC FROM MACHINE M LEFT JOIN ASSET A ON A.MAPPED_ID = M.ID AND A.ASSET_TYPE_ID=5 LEFT JOIN ASSET_DATA_5 A5 ON A5.ID = A.ASSET_DATA_ID where (LAST_SYNC > CURDATE() - INTERVAL 10 DAY) AND ((1 not in (select 1 from SOFTWARE S, MACHINE_SOFTWARE_JT MSJT where M.ID = MSJT.MACHINE_ID and MSJT.SOFTWARE_ID = S.ID and S.DISPLAY_NAME like 'sophos%')) ) order by M.NAME
Answers (2)
The difference is that the subquery in the second "working" query is filtering out the results (i.e. those machines without Sophos installed) and then passing those matching machines to the main query to add on all of the other info.
The first query looks like it should work, but in practice I'm finding that a simplified version (without the asset info) still returns pretty much all of my machines. Changing it from S.DISPLAY_NAME NOT LIKE ____ to S.DISPLAY_NAME LIKE ____ returns the expected results, but I think you'll find if you use the "NOT LIKE" and yank DISTINCT from the SELECT statement you'll see why it's not working as written (at least I did, with all of the results that were returned) - it basically returns *all* of the software matches for the machines' software list *except* the one you have specified in the "NOT LIKE" statement, but the DISTINCT is masking this.
I don't have time to play with this at the moment, but you might try running a query like this to determine the actual SOFTWARE.ID for your Sophos app (or apps), and then try joining/filtering specifically on those rather than just SOFTWARE.ID (which will return everything)... or just use a subquery (which is what I would suggest, since you can actually see what's being returned more clearly).
SELECT * FROM SOFTWARE S
WHERE S.DISPLAY_NAME rlike 'Sophos'
John
Not sure. But I recommend using..
LAST_SYNC > DATE_SUB(NOW(),INTERVAL 10 DAY) (you don't need the brackets)
since it just looks a little cleaner.
Comments:
-
The brackets are there because I'm looking for Sophos, Kapersky, Symantec etc. I just got lazy and didn't remove them when posting. I've used DATE_SUB before, and since both get the same results that didn't really matter. I did remove that line during testing to see if it returned machines without AV. - dugullett 11 years ago
-
Ok. Let me get some coffee and I'll try again. Why are you working on a Saturday, man? - gcarpenter 11 years ago
-
Interesting! I tried using "WHERE NOT EXISTS (BLAH BLAH BLAH) and it stopped recognizing anything. Stepped through it. I just think it's hard because I don't have Asset_Data_Field5 or the like, so whatever results I'm getting are full of nulls and makes things overly redundant. - gcarpenter 11 years ago
