Working with attributes in WhatsUp


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″)

Advertisements
Tagged , , , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: