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)
Please log in to answer
Posted by:
feeldamped
5 years ago
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
-
Yes - Cooltech25 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
