Report/Notification for machines with an active AMP connection on a specific subnet
Hello
I have been tyring to create a report/notifcation for machines that are actively connected to K1000 with a specific IP address. Using MySql quiereies I found on other forums and created in the Kbox I have 2 reports that provide this information. Trying to get this down to one report/notification. Listed below are the 2 queries.
This MYSql query returns all machiens that have the specific IP address that I need:
select MACHINE.*, C.CLIENT_CONNECTED, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME, UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_SECONDS from ORG1.MACHINE LEFT JOIN KBSYS.KUID_ORGANIZATION O ON O.KUID=MACHINE.KUID LEFT JOIN KBSYS.SMMP_CONNECTION C ON C.KUID = MACHINE.KUID AND O.ORGANIZATION_ID = 1 where (( (1 in (select 1 from ORG1.MACHINE M2 where M2.ID = MACHINE.ID and M2.IP rlike '192.168.140.*' union select 1 from ORG1.MACHINE_NICS where MACHINE.ID = MACHINE_NICS.ID and MACHINE_NICS.IP rlike '192.168.140.*')) ))
This MYSql query returns all machines that have an active AMP connection to the K1000
SELECT MACHINE.IP, SC.CLIENT_CONNECTED, MACHINE.NAME AS SYSTEM_NAME, USER_LOGGED FROM MACHINE LEFT JOIN KBSYS.SMMP_CONNECTION SC ON MACHINE.KUID = SC.KUID WHERE SC.CLIENT_CONNECTED LIKE '1'
Both work as a standalone but, would really be helpful if I could get this combined to one query.
I'm by no means a MySql expert so any insight on how to do this or perhaps there is a better approach?
Thanks
0 Comments
[ + ] Show comments
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
dugullett
11 years ago
Try this.
SELECT M.IP, SC.CLIENT_CONNECTED, M.NAME AS SYSTEM_NAME, USER_LOGGED FROM MACHINE M LEFT JOIN KBSYS.SMMP_CONNECTION SC ON M.KUID = SC.KUID WHERE SC.CLIENT_CONNECTED LIKE '1' AND M.IP LIKE '192.168.140%'
Comments:
-
Thanks Dugullett works perfectly... maybe I'm pressing my luck but, by any chance do you (or anyone else) know how to add the time of last AMP connection and last inventory time. I greatly appreciate any help you or anyone else might be able to provide. - jwilliams 11 years ago
-
Add M.LAST_INVENTORY, SC.CLIENT_CONNECTED.
SELECT M.IP, M.LAST_INVENTORY, SC.CLIENT_CONNECTED,SC.CLIENT_CONNECTED, M.NAME AS SYSTEM_NAME, USER_LOGGED
FROM MACHINE M
LEFT JOIN KBSYS.SMMP_CONNECTION SC ON M.KUID = SC.KUID
WHERE SC.CLIENT_CONNECTED LIKE '1'
AND M.IP LIKE '192.168.140%' - dugullett 11 years ago-
Once again thanks for you help just what I needed. - jwilliams 11 years ago
-
While its not a huge deal at this point would be nice to have the AMP Connection time stamp on the report as well.Any idea how to add this? Thank you in advance for your help. - jwilliams 11 years ago
-
Made a typo in the first one. SC.CONNECT_TIME will get that.
SELECT M.IP, M.LAST_INVENTORY, SC.CLIENT_CONNECTED,SC.CONNECT_TIME, M.NAME AS SYSTEM_NAME, USER_LOGGED
FROM MACHINE M
LEFT JOIN KBSYS.SMMP_CONNECTION SC ON M.KUID = SC.KUID
WHERE SC.CLIENT_CONNECTED LIKE '1'
AND M.IP LIKE '192.168.140%' - dugullett 11 years ago-
Once again perfect thanks again! - jwilliams 11 years ago
