/build/static/layout/Breadcrumb_cap_w.png

Notification Email, Replace System Description with Assignee Name?

is there an easy way for someone with zero SQL skills to replace the data field of "System Description" with "Assignee Name" in the email output of a notification? I am fine if the header at the top of the list does not change, just need the data in the displayed cells to change.

2 Comments   [ + ] Show comments
  • Is this for the service desk? It sounds like you are using a custom rule and the option to send results turned on. Is that the case? Either that or this is a report that is setup for a notification?

    Either way, please post the select statement of the rule or the report and someone can show you how to add the assignee name. - chucksteel 5 years ago
  • I is a Notification set up under reporting.

    SELECT MACHINE.NAME AS SYSTEM_NAME, SYSTEM_DESCRIPTION, MACHINE.IP, MACHINE.MAC, MACHINE.ID as TOPIC_ID FROM MACHINE LEFT JOIN ASSET ON ASSET.MAPPED_ID = MACHINE.ID AND ASSET.ASSET_TYPE_ID=5 LEFT JOIN ASSET_CLASS ON ASSET_CLASS.ID = ASSET.ASSET_CLASS_ID LEFT JOIN MACHINE_DISKS ON (MACHINE_DISKS.ID = MACHINE.ID) WHERE ((ASSET_CLASS.NAME = 'Desktop') AND (MACHINE_DISKS.PERCENT_USED > '89') OR (ASSET_CLASS.NAME = 'Laptop') AND (MACHINE_DISKS.PERCENT_USED > '89') OR (ASSET_CLASS.NAME = 'Scale PC') AND (MACHINE_DISKS.PERCENT_USED > '89')) GROUP BY MACHINE.ID - TBisel 5 years ago

Answers (1)

Posted by: chucksteel 5 years ago
Red Belt
0
Here you go:
SELECT MACHINE.NAME AS SYSTEM_NAME, 
SYSTEM_DESCRIPTION, MACHINE.IP, MACHINE.MAC, MACHINE.ID as TOPIC_ID,
USER.FULL_NAME
FROM MACHINE 
LEFT JOIN ASSET ON ASSET.MAPPED_ID = MACHINE.ID AND ASSET.ASSET_TYPE_ID=5 
LEFT JOIN ASSET_CLASS ON ASSET_CLASS.ID = ASSET.ASSET_CLASS_ID 
LEFT JOIN MACHINE_DISKS ON (MACHINE_DISKS.ID = MACHINE.ID) 
LEFT JOIN USER ON USER.ID = ASSET.OWNER_ID
WHERE ((ASSET_CLASS.NAME = 'Desktop') AND (MACHINE_DISKS.PERCENT_USED > '89') OR (ASSET_CLASS.NAME = 'Laptop') AND (MACHINE_DISKS.PERCENT_USED > '89') OR (ASSET_CLASS.NAME = 'Scale PC') AND (MACHINE_DISKS.PERCENT_USED > '89')) GROUP BY MACHINE.ID
I added a join statement to the USER table based on the owner of the asset and selected the USER.FULL_NAME from the user table. If you also want to remove the system description column remove it from the list of selected columns at the beginning of the statement.

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ