/bundles/itninjaweb/img/Breadcrumb_cap_w.png
I would like to set up an automated process that emails the users when their device(s) are MIA for +15 days. I found one article but the solution failed ~ even with adjustments to the code.
7 Comments   [ - ] Hide Comments

Comments

  • Please post the article and adjustments that failed.
  • Article: http://www.itninja.com/blog/view/using-ticket-rules-to-automate-non-ticket-emails
    Code: IF(USER.EMAIL IS NULL or USER = '', 'my.email@domain.com', USER.EMAIL) as user_email,
    -- 'my.email@domain.com' as user_email, -- (for testing)
    • Does your MIA report run successfully?
  • yes and I can do a mail merge using Excel and Word, but I just want the emails sent automatically to the user of the device.
  • Can you paste the full MIA report SQL? If you've set up the CTR page already, let us know the settings you have on there, too.
  • SELECT MACHINE.NAME AS SYSTEM_NAME, LAST_SYNC, OS_NAME, MACHINE.IP, USER_FULLNAME, ASSET_OWNER.EMAIL AS ASSIGNEE_EMAIL, BIOS_SERIAL_NUMBER, CS_MANUFACTURER, CS_MODEL, MACHINE.NOTES FROM MACHINE LEFT JOIN ASSET ON ASSET.MAPPED_ID = MACHINE.ID AND ASSET.ASSET_TYPE_ID=5 LEFT JOIN USER ASSET_OWNER ON ASSET_OWNER.ID = ASSET.OWNER_ID LEFT JOIN KBSYS.KUID_ORGANIZATION KUID_ORG ON KUID_ORG.KUID = MACHINE.KUID LEFT JOIN KBSYS.SMMP_CONNECTION SC ON MACHINE.KUID = SC.KUID AND substring(SCHEMA(),4) = KUID_ORG.ORGANIZATION_ID WHERE ((SC.DISCONNECT_TIME < now() ) AND (concat(SC.CLIENT_CONNECTED) = '0') AND ((TIMESTAMP(SC.DISCONNECT_TIME) > NOW() OR TIMESTAMP(SC.DISCONNECT_TIME) <= DATE_SUB(NOW(),INTERVAL 15 DAY)))) ORDER BY ASSIGNEE_EMAIL
  • The report is scheduled to run everyday at noon and should be emailed to me. It never runs, but I can run it manually.
  • Sorry, but I don't know what the CTR page is...
Please log in to comment

There are no answers at this time
Answer this question or Comment on this question for clarity

Answers