I turned this smart label (http://bit.ly/13ZxgWI) into a email alert with this code:


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 ORG2.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 = 2
                 where ((  (1  in (select 1 from ORG2.SOFTWARE, ORG2.MACHINE_SOFTWARE_JT where MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID and MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID and DateDiff(CurDate(), LAST_REBOOT) > 30)) ))


The colums in the email notification are:
Computer Name, System Description, MAC Address, IP Address

Is there a way to specify which columns the notification sends?  I'd like to see Computer Name, last reboot, amp connection present.  It would be even better if the results could be sorted by Computer name and then last reboot.

Thank you,
-awingren

Answer Summary:
Hi everyone! I found out from support the columns returning are not customizable. When I get my votes back, I will put this in as a feature request. Thanks, ~awingren
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

1

Hi everyone!  I found out from support the columns returning are not customizable.  When I get my votes back, I will put this in as a feature request.

Thanks,
~awingren

Answered 10/14/2013 by: awingren
Eighth Degree Black Belt

  • Below I remarked:

    Run this as a REPORT and not as a NOTIFICATION QUERY.

    If you run as a REPORT then you can still pull from all of the databases but the columns you select (and what you alias) will show up on the report.
Please log in to comment

Answers

0

Try this. 

SELECT M.NAME, SYSTEM_DESCRIPTION, MAC, IP,
CASE WHEN S.CLIENT_CONNECTED = 1 THEN 'CONNECTED' ELSE 'NOT CONNECTED' 
END AS 'AMP CONNECTION'
FROM MACHINE M
LEFT JOIN KBSYS.SMMP_CONNECTION S ON M.KUID=S.KUID
WHERE LAST_REBOOT < DATE_SUB(NOW(), INTERVAL 30 DAY)
ORDER BY M.NAME, LAST_REBOOT
Answered 08/06/2013 by: dugullett
Red Belt

  • You might also want to try something like that will include machines that have synced recently. It may help clean it up. This includes only machines that have synced in the past 14 days.

    SELECT M.NAME, SYSTEM_DESCRIPTION, MAC, IP, LAST_SYNC,
    CASE WHEN S.CLIENT_CONNECTED = 1 THEN 'CONNECTED' ELSE 'NOT CONNECTED'
    END AS 'AMP CONNECTION'
    FROM MACHINE M
    LEFT JOIN KBSYS.SMMP_CONNECTION S ON M.KUID=S.KUID
    WHERE LAST_REBOOT < DATE_SUB(NOW(), INTERVAL 30 DAY)
    AND LAST_SYNC > DATE_SUB(NOW(), INTERVAL 14 DAY)
    ORDER BY M.NAME, LAST_REBOOT
  • Thanks dugullett!! I love the addition of including only machines that have synced in the past 14 days! That's perfect. The email alert is still coming to me with these columns: Computer Name, System Description, MAC Address, IP Address. I'd like to see Computer Name, and last reboot.

    I just tried this:

    SELECT M.NAME, UPTIME, IP, LAST_SYNC,
    CASE WHEN S.CLIENT_CONNECTED = 1 THEN 'CONNECTED' ELSE 'NOT CONNECTED'
    END AS 'AMP CONNECTION'
    FROM MACHINE M
    LEFT JOIN KBSYS.SMMP_CONNECTION S ON M.KUID=S.KUID
    WHERE LAST_REBOOT < DATE_SUB(NOW(), INTERVAL 30 DAY)
    AND LAST_SYNC > DATE_SUB(NOW(), INTERVAL 14 DAY)
    ORDER BY M.NAME, LAST_REBOOT

    It worked fine as a regular report, but as an email alert I still get the same four colums: Computer Name, System Description, MAC Address, IP Address

    Maybe what I am trying to do isn't supposed to work?
    • Did you create a new one, or edit the old? It sounds like it may be hanging on to some old settings.
      • I edited the old one. I'll try creating a new one. Thank you!
  • Even with the new email alert I am still only seeing:
    Computer Name, System Description, MAC Address, IP Address

    with this "Notification Query"--

    SELECT M.NAME, UPTIME, IP, LAST_SYNC,
    CASE WHEN S.CLIENT_CONNECTED = 1 THEN 'CONNECTED' ELSE 'NOT CONNECTED'
    END AS 'AMP CONNECTION'
    FROM MACHINE M
    LEFT JOIN KBSYS.SMMP_CONNECTION S ON M.KUID=S.KUID
    WHERE LAST_REBOOT < DATE_SUB(NOW(), INTERVAL 30 DAY)
    AND LAST_SYNC > DATE_SUB(NOW(), INTERVAL 14 DAY)
    ORDER BY M.NAME, LAST_REBOOT
    • I don't use the notifications feature a whole lot. So I'm not 100% familiar with it. I'll see if I can duplicate it. Have you contacted support? It seems like since you already have the query they should be able to get you going.
      • I sent the info to support. We'll see what they say. I'll report back what I find out!
    • Have you tried setting this up just a REPORT?

      If you set it up as a report you should get the columns you want in the order you SELECT them...

      You should be able to turn around and paste this SQL into a SQL report and run it...
Please log in to comment
Answer this question or Comment on this question for clarity