/build/static/layout/Breadcrumb_cap_w.png
07/05/2018 281 views
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

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.
  • 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

All Answers

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.

Answered 07/17/2018 by: chucksteel
Red Belt