How do I create an email alert for new computers within one day that don't have certain software installed?
I've been trying to configure an email alert but can't quite get it to do what I want - I am trying to make an email alert that will shoot off an email once a day that will show a list of computers that do not have a certain software title installed and have only new computers for that day - their record was created or agent installed in the last day. Can anyone point me to some information how to configure this?
Answers (2)
I can give you part of the answer. The sql down below will give you any new machines that have been created or just checked in for today. I would modify that code to include an and with the software packages you are looking for that are missing.
select DATE_FORMAT(CREATED,'%W %m-%d-%Y') as CREATED1, MACHINE.NAME as MACHINE_NAME, SYSTEM_DESCRIPTION, IP, MAC, REPLACE(MACHINE.USER_LOGGED,'\\','\\\\') as USER_LOGGED, CS_DOMAIN, CREATED as CREATED_TIME from MACHINE where DATE_FORMAT(CREATED,'%m%d%Y') =5118012 order by CREATED_TIME
Put your AND statement after the date to filter out the software.
Thanks,
M
This is the report that would find computers without Office 2010:
SELECT MACHINE.NAME AS SYSTEM_NAME,USER_LOGGED,LAST_SYNC,IP,MAC,SERVICE_PACK
FROM MACHINE
WHERE ((OS_NAME not like '%Mac%')
AND ((1 not in
(select 1 from SOFTWARE, MACHINE_SOFTWARE_JT where MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID
AND SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID and SOFTWARE.DISPLAY_NAME
like '%Microsoft Office Professional Plus 2010%')) ))
AND MACHINE.CREATED > DATE_SUB(NOW(), INTERVAL 1 DAY)
ORDER BY SYSTEM_NAME
If you're looking for something else change the like '%Microsoft Office Professional Plus 2010%' to like '%software title%'. Also note that I'm not including MacOS computers in this report, since I was looking just for Windows machines without Office 2010. I added the MACHINE.CREATED line to limit this just to machines created in the past day.
Once you have the report created you can schedule it to run daily. There should be an option to only send the email if there is data returned for the report.
