Dynamic Group Examples

WhatsUp Gold is pre-configured with dynamic group examples, which you can see in the Devices view, under Device Groups. For more information on these groups, see Using Dynamic Groups.

The following examples show several dynamic group filters that you can use to create some interesting dynamic groups for your devices. To use these examples, select the text of the filter, and then copy and paste the text into the Filter box of the Dynamic Group dialog.

Note: You may have to remove the copyright information from the cut and paste if it appears when you copy from this help file.

To show all devices that have had a state change in the last three hours:

To show all devices with multiple interfaces:

SELECT DISTINCT NetworkInterface.nDeviceID
FROM Device
JOIN NetworkInterface
ON Device.nDeviceID = NetworkInterface.nDeviceID
WHERE Device.bRemoved = 0
GROUP BY NetworkInterface.nDeviceID
HAVING COUNT(NetworkInterface.nDeviceID) > 1

To show all devices that have gone down in the last two hours and are still down:

SELECT DISTINCT Device.nDeviceID
FROM Device
JOIN PivotActiveMonitorTypeToDevice
ON Device.nDeviceID = PivotActiveMonitorTypeToDevice.nDeviceID
JOIN ActiveMonitorStateChangeLog
ON PivotActiveMonitorTypeToDevice.nPivotActiveMonitorTypeToDeviceID =
ActiveMonitorStateChangeLog.nPivotActiveMonitorTypeToDeviceID
JOIN MonitorState
ON Device.nWorstStateID = MonitorState.nMonitorStateID
WHERE Device.bRemoved = 0
AND PivotActiveMonitorTypeToDevice.bDisabled = 0
AND DATEDIFF(hh, ActiveMonitorStateChangeLog.dStartTime, GETDATE()) <= 2
AND MonitorState.nInternalMonitorState = 1

To show all the devices (in one specific group) that have had an action fire in the last two days:

SELECT DISTINCT Device.nDeviceID
FROM Device
JOIN ActionActivityLog
ON Device.nDeviceID = ActionActivityLog.nDeviceID
JOIN PivotDeviceToGroup
ON Device.nDeviceID = PivotDeviceToGroup.nDeviceID
JOIN DeviceGroup
ON PivotDeviceToGroup.nDeviceGroupID = DeviceGroup.nDeviceGroupID
WHERE Device.bRemoved = 0
AND DATEDIFF(Dd,ActionActivityLog.dDateTime,GETDATE()) <= 2
AND DeviceGroup.sGroupName = 'My Key Resources Group'

To show all devices that need acknowledgement:

To show all devices with disks that are 90% full or fuller:

SELECT DISTINCT Device.nDeviceID
FROM Device
JOIN PivotStatisticalMonitorTypeToDevice
ON Device.nDeviceID = PivotStatisticalMonitorTypeToDevice.nDeviceID
JOIN StatisticalDiskIdentification
ON PivotStatisticalMonitorTypeToDevice.nPivotStatisticalMonitorTypeToDeviceID =
StatisticalDiskIdentification.nPivotStatisticalMonitorTypeToDeviceID
JOIN StatisticalDiskCache
ON StatisticalDiskIdentification.nStatisticalDiskIdentificationID =
StatisticalDiskCache.nStatisticalDiskIdentificationID
WHERE Device.bRemoved = 0
AND PivotStatisticalMonitorTypeToDevice.bEnabled = 1
AND StatisticalDiskCache.nDataType = 1
AND (((nUsed_Avg / nSize) > 0.90)
AND (NOT nSize = 0
OR nSize IS
NULL))

To show all devices in maintenance or with at least one down active monitor and match the specified device types:

SELECT DISTINCT Device.nDeviceID
FROM Device
JOIN MonitorState
ON Device.nWorstStateID = MonitorState.nMonitorStateID
WHERE Device.bRemoved = 0
AND MonitorState.nInternalMonitorState IN (1,2)
AND Device.nDeviceTypeID IN (3,4,38,63,64,65,66,67,68,71,72)

To show only devices on which all active monitors are down:

SELECT DISTINCT Device.nDeviceID
FROM Device
JOIN MonitorState
ON Device.nWorstStateID = MonitorState.nMonitorStateID
WHERE Device.bRemoved = 0
AND MonitorState.nInternalMonitorState = 1
AND Device.nWorstStateID = Device.nBestStateID

To show only those devices on which all active monitors have been down for 20 minutes or more:

SELECT DISTINCT Device.nDeviceID
FROM Device
JOIN PivotActiveMonitorTypeToDevice
ON Device.nDeviceID = PivotActiveMonitorTypeToDevice.nDeviceID
JOIN ActiveMonitorStateChangeLog
ON PivotActiveMonitorTypeToDevice.nPivotActiveMonitorTypeToDeviceID =
ActiveMonitorStateChangeLog.nPivotActiveMonitorTypeToDeviceID
JOIN MonitorState
ON PivotActiveMonitorTypeToDevice.nMonitorStateID =
MonitorState.nMonitorStateID
WHERE Device.bRemoved = 0
AND PivotActiveMonitorTypetoDevice.bRemoved = 0
AND PivotActiveMonitorTypeToDevice.bDisabled = 0
AND MonitorState.nInternalMonitorState = 1
AND DATEDIFF(Mi,ActiveMonitorStateChangeLog.dStartTime,GETDATE()) >= 20
AND Device.nWorstStateId = Device.nBestStateId

To show devices whose actions (or whose active monitors' actions) have a specific word in their name:

Note: To search for a different action, change the action name after LIKE. Be sure to leave both % symbols.

SELECT DISTINCT Device.nDeviceID
FROM Device
JOIN ActionPolicy
ON Device.nActionPolicyID = ActionPolicy.nActionPolicyID
JOIN PivotActionTypeToActionPolicy
ON ActionPolicy.nActionPolicyID =
PivotActionTypeToActionPolicy.nActionPolicyID
JOIN ActionType
ON PivotActionTypeToActionPolicy.nActionTypeID =
ActionType.nActionTypeID
WHERE Device.bRemoved = 0
AND ActionType.sActionTypeName LIKE '%Critical%'

UNION

SELECT DISTINCT Device.nDeviceID
FROM Device
JOIN PivotActiveMonitorTypeToDevice
ON Device.nDeviceID = PivotActiveMonitorTypeToDevice.nDeviceID
JOIN ActionPolicy
ON PivotActiveMonitorTypeToDevice.nActionPolicyID =
ActionPolicy.nActionPolicyID
JOIN PivotActionTypeToActionPolicy
ON ActionPolicy.nActionPolicyID =
PivotActionTypeToActionPolicy.nActionPolicyID
JOIN ActionType
ON PivotActionTypeToActionPolicy.nActionTypeID =
ActionType.nActionTypeID
WHERE Device.bRemoved = 0
AND PivotActiveMonitorTypeToDevice.bRemoved = 0
AND ActionType.sActionTypeName LIKE '%Critical%'

To show devices to which a particular performance monitor is assigned:

To show devices to which a particular passive monitor is assigned:

SELECT DISTINCT Device.nDeviceID
FROM Device
JOIN PivotPassiveMonitorTypeToDevice
ON Device.nDeviceID = PivotPassiveMonitorTypeToDevice.nDeviceID
JOIN PassiveMonitorType
ON PassiveMonitorType.nPassiveMonitorTypeID =
PivotPassiveMonitorTypeToDevice.nPassiveMonitorTypeID
WHERE Device.bRemoved = 0
AND PivotPassiveMonitorTypeToDevice.bRemoved = 0
AND PassiveMonitorType.sMonitorTypeName LIKE '%Cold Start%'

To show devices to which a particular active monitor is assigned:

SELECT DISTINCT Device.nDeviceID
FROM Device
JOIN PivotActiveMonitorTypeToDevice
ON Device.nDeviceID = PivotActiveMonitorTypeToDevice.nDeviceID
JOIN ActiveMonitorType
ON ActiveMonitorType.nActiveMonitorTypeID =
PivotActiveMonitorTypeToDevice.nActiveMonitorTypeID
WHERE Device.bRemoved = 0
AND PivotActiveMonitorTypeToDevice.bRemoved = 0
AND ActiveMonitorType.sMonitorTypeName LIKE '%Ping%'

To find a device by its display name, host name, or IP address:

SELECT DISTINCT Device.nDeviceID
FROM Device
JOIN NetworkInterface
ON Device.nDeviceID = NetworkInterface.nDeviceID
AND Device.nDefaultNetworkInterfaceID =
NetworkInterface.nNetworkInterfaceID
JOIN DeviceType
ON Device.nDeviceTypeID = DeviceType.nDeviceTypeID
WHERE (Device.sDisplayName LIKE '%Mail Server%'
OR NetworkInterface.sNetworkName LIKE '%server1.ipswitch.com%'
OR NetworkInterface.sNetworkAddress LIKE '%1.2.3.4%')
AND Device.bRemoved = 0