/build/static/layout/Breadcrumb_cap_w.png
05/01/2019 196 views

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

Comments


All Answers

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'
Answered 05/01/2019 by: feeldamped
Second Degree Blue Belt

  • 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?
    • 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.
      • 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!
    • Are you looking for a history of the times the machines have connected in the past 14 days? not just the most recent connection?
    • 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.