Report/Notification for machines with an active AMP connection on a specific subnet
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.*')) ))
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'