Posted today on WUP Board. So here they are. ;) http://www.ipswitch.com/forums/shwmessage.aspx?ForumID=14&MessageID=12194
Daniel -- Daniel Blaser System Engineer ISP Abt. Lie-Comtel Tel: +423 / 236 17 60 Liechtensteinische Kraftwerke Fax: +423 / 236 17 41 Im alten Riet 17, 9494 Schaan Web: http://www.lkw.li ----------------------------------------------------------------------------------- ----------------------------------------------------------------------------------- ----------------------------------------------------------------------------------- Wuppers, As I had certain problems with SQL queries & WUP at the beginning myself, I have decided to post my small collection I've garthered together to WUP Maillist. Probably some Wuppers will find useful code snipplets there. It would be nice if others could also share their SQL statements with the rest of us - preferably at the WUP Maillist. Regards, Daniel ----------------------------------------------------------------------------------- ----------------------------------------------------------------------------------- ----------------------------------------------------------------------------------- // Dieses SQL-Statement gibt Geräte und zugeordnete PAssive Monitor aus, sortiert nach Gerätenamen. // This SQL-Statement returns Devices and attached passive monitors, sorted by Device Name. SELECT Device.sDisplayName, PassiveMonitorType.sMonitorTypeName FROM Device JOIN PivotPassiveMonitorTypeToDevice ON Device.nDeviceID = PivotPassiveMonitorTypeToDevice.nDeviceID JOIN PassiveMonitorType ON PivotPassiveMonitorTypeToDevice.nPassiveMonitorTypeID = PassiveMonitorType.nPassiveMonitorTypeID WHERE ISNULL(Device.bRemoved, 0) = 0 AND ISNULL(PivotPassiveMonitorTypeToDevice.bRemoved, 0) = 0 AND ISNULL(PassiveMonitorType.bRemoved, 0) = 0 ORDER BY Device.nDeviceID // Dieses SQL-Statement gibt alle Maps aus, in welche ein bestimmtes Gerät vorkommt // This SQL-Statement returns alll Maps, to which a certain device is attached. SELECT DeviceGroup.sGroupName FROM DeviceGroup JOIN PivotDeviceToGroup ON DeviceGroup.nDeviceGroupID = PivotDeviceToGroup.nDeviceGroupID JOIN Device ON PivotDeviceToGroup.nDeviceID = Device.nDeviceID WHERE Device.sDisplayName = 'w0' AND ISNULL(Device.bRemoved,0)=0 // Dieses SQL-Statement gibt alle Geräte und deren Actions aus, welche direkt auf einen ActiveMonitor gestzt sind. // This SQL-Statement returns devices and all actions, which are set directly on an active monitor. SELECT Device.sDisplayName, ActionType.sActionTypeName 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 // Dieses SQL-Statement gibt alle Geräte und deren gesetzte Actions aus. // This SQL-Statement returns all devices and actions set to them. SELECT Device.sDisplayName, ActionType.sActionTypeName FROM Device JOIN ActionPolicy ON Device.nActionPolicyID = ActionPolicy.nActionPolicyID JOIN PivotActionTypeToActionPolicy ON ActionPolicy.nActionPolicyID = PivotActionTypeToActionPolicy.nActionPolicyID JOIN ActionType ON PivotActionTypeToActionPolicy.nActionTypeID = ActionType.nActionTypeID // Dieses SQL-Statement liest Actiontypes aus // This SQL-Statement returns Actiontypes. Select nActionTypeID, sActionTypeName, nCLSID FROM ActionType WHERE bGlobalActionType = 1 ORDER BY sActionTypeName ASC // Dieses SQL-Statement gibt DisplayName, Group,IP-Address, Hostname und Notes in einem Table aus. // This SQL-Statement returns DisplayName, Group,IP-Address, Hostname und Notes in a table. SELECT Device.sDisplayName, DeviceGroup.sGroupName, NetworkInterface.sNetworkAddress, NetworkInterface.sNetworkName, Device.sNote FROM Device JOIN PivotDeviceToGroup ON Device.nDeviceID = PivotDeviceToGroup.nDeviceID JOIN DeviceGroup ON PivotDeviceToGroup.nDeviceGroupID = DeviceGroup.nDeviceGroupID JOIN NetworkInterface ON Device.nDeviceID = NetworkInterface.nDeviceID WHERE ISNULL(Device.bRemoved,0)=0 // Dieses SQL-Statement sagt einem in welcher Gruppe sich ein bestimmtes Gerät befindet. // This SQL-Statement returns the group(s) of a certain device. SELECT DeviceGroup.sGroupName FROM DeviceGroup JOIN PivotDeviceToGroup ON DeviceGroup.nDeviceGroupID = PivotDeviceToGroup.nDeviceGroupID JOIN Device ON PivotDeviceToGroup.nDeviceID = Device.nDeviceID WHERE Device.sDisplayName = 'DEVICE' AND ISNULL(Device.bRemoved,0)=0 Please visit http://www.ipswitch.com/support/mailing-lists.html to be removed from this list. An Archive of this list is available at: http://www.mail-archive.com/whatsup_forum%40list.ipswitch.com/
