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?

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

3

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

 

Answered 10/24/2012 by: ms01ak
Tenth Degree Black Belt

Please log in to comment

Answers

2

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.

Answered 10/24/2012 by: chucksteel
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity