/build/static/layout/Breadcrumb_cap_w.png

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:
Posted by: dugullett 10 years ago
Red Belt
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%'

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 10 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 10 years ago
      • Once again thanks for you help just what I needed. - jwilliams 10 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 10 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 10 years ago
      • Once again perfect thanks again! - jwilliams 10 years ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ