K1000 Report Help
I have a query that searches for two software items, and machines that have inventoried in the past 30 days. We sent a fix out last week the generated a flag file that we are inventorying. I need to be able to keep the default query in place, but add a column on the end saying that the custom inventory flag file is there. A 1 or a 0 would work.
I've tried adding in something like this, but it just returns NULL.
REPLACE((SELECT MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE FROM MACHINE_CUSTOM_INVENTORY WHERE MACHINE_CUSTOM_INVENTORY.ID=M.ID AND MACHINE_CUSTOM_INVENTORY.SOFTWARE_ID=87563),'<br/>','-') AS Update
Here is my query.
select M.NAME, IP, USER_LOGGED, LAST_SYNC, LAST_REBOOT FROM MACHINE M where (LAST_SYNC > CURDATE() - INTERVAL 30 DAY) AND ((( (1 not in (select 1 from SOFTWARE S, MACHINE_SOFTWARE_JT where M.ID = MACHINE_SOFTWARE_JT.MACHINE_ID and MACHINE_SOFTWARE_JT.SOFTWARE_ID = S.ID and S.DISPLAY_NAME like 'software fix%')) ) AND (1 in (select 1 from SOFTWARE S, MACHINE_SOFTWARE_JT where M.ID = MACHINE_SOFTWARE_JT.MACHINE_ID and MACHINE_SOFTWARE_JT.SOFTWARE_ID = S.ID and S.DISPLAY_NAME like 'software.exe exists%')) )) order by M.NAME
Answers (1)
dgullett,
1) If you run this query, does it show your custom inventory fields?
SELECT MCI.STR_FIELD_VALUE
FROM MACHINE M
JOIN MACHINE_CUSTOM_INVENTORY MCI on (MCI.ID = M.ID)
WHERE MCI.SOFTWARE_ID = 87563
2) If you run this (more open) query, is the FLAG column populated? Note that you can't use certain names for columns in MySQL due to their having special significance (such as UPDATE, LAST, JOIN, etc) - the MySQL Query Browser can clarify these as they turn *blue* when they're special.
SELECT DISTINCT M.NAME, IP, USER_LOGGED, LAST_SYNC, LAST_REBOOT,
MCI.STR_FIELD_VALUE as FLAG
FROM MACHINE M
JOIN MACHINE_CUSTOM_INVENTORY MCI on (MCI.ID = M.ID)
JOIN MACHINE_SOFTWARE_JT MJT on (MJT.MACHINE_ID = M.ID)
JOIN SOFTWARE S on (S.ID = MJT.SOFTWARE_ID)
WHERE (LAST_SYNC > CURDATE() - INTERVAL 30 DAY)
AND MCI.SOFTWARE_ID = 87563
ORDER BY M.NAME
3) Assuming those work, you might try using an IF statement to test for the presence of the custom inventory file (1 if true, 0 if not).
SELECT DISTINCT M.NAME, IP, USER_LOGGED, LAST_SYNC, LAST_REBOOT,
IF((SELECT MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE
FROM MACHINE_CUSTOM_INVENTORY
WHERE MACHINE_CUSTOM_INVENTORY.ID=M.ID
AND MACHINE_CUSTOM_INVENTORY.SOFTWARE_ID=87563), 1, 0) AS FLAG
FROM MACHINE M
JOIN MACHINE_CUSTOM_INVENTORY MCI on (MCI.ID = M.ID)
JOIN MACHINE_SOFTWARE_JT MJT on (MJT.MACHINE_ID = M.ID)
JOIN SOFTWARE S on (S.ID = MJT.SOFTWARE_ID)
WHERE (LAST_SYNC > CURDATE() - INTERVAL 30 DAY)
AND S.DISPLAY_NAME not like 'software fix%'
AND S.DISPLAY_NAME like 'software.exe exists%'
ORDER BY M.NAME
Hope that helps!
John
Comments:
-
I just tested these. It's actually showing in the software table even though it is a custom inventory "Fileexists(C:\Temp)". I think it was too late Friday, and I wasn't paying attention.
What would be the proper syntax for using the IF statement with the software table? I'm not sure if just not seeing it, or if my query is wrong. - dugullett 11 years ago -
There are a number of ways you could do this, but I would probably target the software ID (SOFTWARE.ID) like this:
SELECT DISTINCT M.NAME, IP, USER_LOGGED, LAST_SYNC,
LAST_REBOOT, IF(S.ID = 10, 1, 0) AS FLAG
FROM MACHINE M
JOIN MACHINE_CUSTOM_INVENTORY MCI on (MCI.ID = M.ID)
JOIN MACHINE_SOFTWARE_JT MJT on (MJT.MACHINE_ID = M.ID)
JOIN SOFTWARE S on (S.ID = MJT.SOFTWARE_ID)
WHERE (LAST_SYNC > CURDATE() - INTERVAL 30 DAY)
ORDER BY M.NAME
You can determine the SOFTWARE.ID number by running this query in the MySQL Query Browser and then grabbing the corresponding number in the ID column:
SELECT * FROM SOFTWARE S
WHERE S.DISPLAY_NAME like 'softwarename%'
Hope that helps!
John - jverbosk 11 years ago
