Query devices with specific Active Monitor Type and Description

Query devices with specific Active Monitor Type and Description

This query is used to select the devices based on a specific Active Monitor type and associated comment. For example, we are going to list all devices that have Interface Active Monitor and these monitors has comment contains Ethernet. device with specific Active Monitor description
SELECT DISTINCT Device.nDeviceID, Device.sDisplayName FROM Device 
LEFT OUTER JOIN PivotActiveMonitorTypeToDevice ON PivotActiveMonitorTypeToDevice.nDeviceID = Device.nDeviceID 
LEFT OUTER JOIN ActiveMonitorType ON ActiveMonitorType.nActiveMonitorTypeID = PivotActiveMonitorTypeToDevice.nActiveMonitorTypeID     
WHERE Device.bRemoved = 0 AND 
ActiveMonitorType.sMonitorTypeName LIKE N'Interface' 
AND PivotActiveMonitorTypeToDevice.sComment LIKE '%Ethernet%'
Notes:
You can use this query to create Dynamic group and generate reports for these devices only.