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
Answers (1)
Please log in to answer
Posted by:
chucksteel
6 years ago
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.
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 6 years ago
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 6 years ago