/build/static/layout/Breadcrumb_cap_w.png

sql Report help

I am trying to add a label to this report where label = "corp systems"


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'


Thank you!


0 Comments   [ + ] Show comments

Answers (1)

Posted by: feeldamped 5 years ago
Third Degree Blue Belt
1

Try this and let me know if it is showing the machines with that label:


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
LEFT JOIN MACHINE_LABEL_JT
ON M.ID = MACHINE_LABEL_JT.MACHINE_ID
LEFT JOIN LABEL
ON MACHINE_LABEL_JT.LABEL_ID = LABEL.ID
WHERE SC.CLIENT_CONNECTED LIKE '1'
AND LABEL.NAME = 'corp systems'

Comments:
  • Looks good thank you.

    But this is only showing today's connections. I am trying to get history of connection maybe for few weeks. Any suggestions? - Cooltech25 5 years ago
    • Instead of using the filter 'SC.CLIENT_CONNECTED LIKE '1''

      You might try changing that to:

      WHERE SC.CONNECT_TIME BETWEEN (NOW() - INTERVAL 14 DAY) AND NOW()

      You can change the 14 day interval to anything you'd like. This would be the past 14 days. - feeldamped 5 years ago
      • Thank you that worked.

        Though it didnt give me what I was looking for. I was hoping it would provide me with amp connection report with time stamp for the last 14 days for each agent.

        Much appreciated! - Cooltech25 5 years ago
    • Are you looking for a history of the times the machines have connected in the past 14 days? not just the most recent connection? - feeldamped 5 years ago
    • Okay. I am not seeing a 'history' table for the smnp connections or any other field in the SMMP_CONNECTION table that would give us history.

      That's at least on my version of the K1000 and we are behind on updating.

      Maybe someone else in the community would be able to help with that part! I'm not sure if the database keeps a log of connection history. - feeldamped 5 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