This query provides the device name but not the item name that raised the Event.
This is the query that provides output including the Device Name and IP Address the Event is raised on.
SELECT DISTINCT(e.NPCEventID),ei.NPCItemID,e.OccurredOn,i.ItemName,i.ItemTypeName,i.ItemSubTypeName,e.Description,
V6_ntoa(d.address) device_address
FROM em.events e
JOIN em.event_items ei ON (e.NPCEventID=ei.NPCEventID)
JOIN em.items i ON (ei.NPCItemID=i.ItemID)
LEFT OUTER JOIN netqosportal.t_device d ON (ei.NPCItemID=d.itemid)
WHERE e.ProducerID=3 AND e.TypeID=18 AND e.Description RLIKE '^A Threshold Violation event.*BGP_Down' AND e.OccurredOn > UNIX_TIMESTAMP() - 86400;
This is the query that provides output including the Device Name, IP Address and specific Item Name that the Event is raised on.
SELECT DISTINCT e.NPCEventID,eid.NPCItemID rtrid, e.OccurredOn, d.ItemName router,
V6_ntoa(d.address) device_address, epc.Value component, e.Description
FROM em.events e
JOIN em.event_items eid ON (eid.NPCEventID=e.NPCEventID) and eid.itemindex=0
JOIN netqosportal.t_device d ON (eid.NPCItemID=d.itemid)
JOIN em.event_properties epc ON e.npceventid = epc.npceventid and epc.Name = 'ItemName'
WHERE e.ProducerID=3 AND e.TypeID=18 AND e.Description RLIKE '^A Threshold Violation event.*BGP_Down'
AND e.OccurredOn > UNIX_TIMESTAMP() - 86400;