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
 
Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

1

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%'
Answered 08/12/2013 by: dugullett
Red Belt

  • 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.
    • 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%'
      • Once again thanks for you help just what I needed.
  • 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.
    • 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%'
      • Once again perfect thanks again!
Please log in to comment
Answer this question or Comment on this question for clarity