Systems Management : Email Alert on Machines that haven't rebooted in 30 days

Email Alert on Machines that haven't rebooted in 30 days

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

0 Comments [ + ] Show comments

Answers (2)

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

Posted by: awingren 10 years ago

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

Comments:

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. - Wildwolfay 10 years ago

Posted by: dugullett 10 years ago

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

Comments:

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 - dugullett 10 years ago
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? - awingren 10 years ago
- Did you create a new one, or edit the old? It sounds like it may be hanging on to some old settings. - dugullett 10 years ago
  - I edited the old one. I'll try creating a new one. Thank you! - awingren 10 years ago
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 - awingren 10 years ago
- 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. - dugullett 10 years ago
  - I sent the info to support. We'll see what they say. I'll report back what I find out! - awingren 10 years ago
- 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... - Wildwolfay 10 years ago

Answer this question

Posted by:

View more:

Systems Management Questions

question