About attributes in WhatsUp
You can find the attributes for a device when you go to device properties and then attributes. By default they don’t hold much but they can used as a useful store of information for that device or for storing variables between active monitor/performance monitor polls.
The easiest way to manipulate the attributes is by directly updating the DeviceAttribute table in the WhatsUp database. The table has the following columns:
| nDeviceAttributeID |
The primary key for the table and an incremental integer (1,2,3,4,5 etc) |
| nDeviceID |
This is the nDeviceID of the device to which the attribute belongs. It is also an integer |
| sName |
The name of the attribute. In WhatsUp this appears as the “Name” column. It is a string. |
| sValue |
The value assigned to the specific attribute. This is also a string. |
Attributes can be added, removed and modified by inserting, deleting and updating rows in the table. For example:
UPDATE [WhatsUp].[dbo].[DeviceAttribute] SET sValue ='Windows Server 2003' WHERE (nDeviceID = '100') AND (sName = 'OS')
In this case the attribute named OS is set with the value “Windows Server 2003”.
Similarly to create (INSERT) and remove (DELETE):
INSERT INTO [WhatsUp].[dbo].[DeviceAttribute] (nDeviceID,sName,sValue) VALUES ('100','OS','Windows 2003')
DELETE FROM [WhatsUp].[dbo].[DeviceAttribute] WHERE (nDeviceID='100' AND sName='OS')
Using this in scripts
WhatsUp allows us to use the built-in database object to access the database. To use this we put this at the top of our scripts:
‘ Retrieve DB object for connecting to WhatsUp
Set oDb = Context.GetDB
and then we can use the oDb object when executing SQL code against the database, like this:
INSERT INTO [WhatsUp].[dbo].[DeviceAttribute] (nDeviceID,sName,sValue) VALUES ('100','OS','Windows 2003')
oDb.Execute(sqlQuery)
If we want to get the output, say do a query and retrieve the result we can do it this way:
sqlCheck = "SELECT nDeviceAttributeID FROM DeviceAttribute WHERE (nDeviceID = '100') AND (sName = 'OS')"
Set oRecordset = oDb.Execute(sqlCheck) context.logmessage oRecordset("nDeviceAttributeID")
which will print the result of the query to the screen. If an array is returned then this can be handle similarly by specifying the oRecordset(“nDeviceID”), oRecordSet(“sValue”) etc.
Of course when you’re writing your scripts you won’t want to do this every time so it’s easier to put these into get/set functions:
function isAttribExist(tmpDeviceID,tmpAttributeName)
boolReturn=0
sqlCheck = "SELECT nDeviceAttributeID FROM DeviceAttribute WHERE (nDeviceID = " & tmpDeviceID & ") AND (sName = N'" & tmpAttributeName & "')"
Set oRs1 = oDb.Execute(sqlCheck)
if oRS1.EOF then
boolReturn=0
else
boolReturn=1
end if
isAttribExist = boolReturn
end function
function updateAttribute(tmpDeviceID,tmpAttributeName,tmpValue)
sqlQuery="UPDATE [WhatsUp].[dbo].[DeviceAttribute] SET sValue = '" & tmpValue & "' WHERE (nDeviceID = " & tmpDeviceID & ") AND (sName = N'" & tmpAttributeName & "')"
context.logmessage sqlQuery
oDb.Execute(sqlQuery)
end function
function addNewAttribute (tmpDeviceID,tmpAttributeName,tmpValue)
sqlQuery="INSERT INTO [WhatsUp].[dbo].[DeviceAttribute] (nDeviceID,sName,sValue) " & _
"VALUES ('" & tmpDeviceID & "','" & tmpAttributeName & "','" & tmpValue & ")"
context.logmessage sqlQuery
oDb.Execute(sqlQuery)
end function
function setAttribute(tmpDeviceID,tmpAttributeName,tmpValue)
if isAttribExist(tmpDeviceID,tmpAttributeName) then
updateAttribute tmpDeviceID,tmpAttributeName,tmpValue
else
addNewAttribute tmpDeviceID,tmpAttributeName,tmpValue
end if
end function
function getAttribute(tmpDeviceID,tmpAttributeName)
strReturn=""
sqlCheck = "SELECT sValue FROM DeviceAttribute WHERE (nDeviceID = " & tmpDeviceID & ") AND (sName = N'" & tmpAttributeName & "')"
Set oRs1 = oDb.Execute(sqlCheck)
if oRS1.EOF then
strReturn=""
else
strReturn=oRs1("sValue")
end if
getAttribute = strReturn
end function
Get usage:
strResult=getAttribute(DeviceID,attribute name)
e.g.
strResult=getAttribute(100,”OS”)
Set usage:
setAttribute(DeviceID,attribute name,value to set)
e.g.
setAttribute(100,”OS”,”Windows Server 2003″)
Stu Jordan 