wugSpace: The WhatsUp Gold Community

Script Library

This is a public Media Gallery publicRSS

Action

Add a whole bunch of attributes to all objects in WUG

Action posted 9/1/09 by Zachary Loeber

1499 Views

Name:

Add a whole bunch of attributes to all objects in WUG

Description:

I was tasked with aligning a spreadsheet of multiple columns of data with our whatsup monitoring database. I chose to import the data from the spreadsheet into attributes on each device in whatsup. To make sure that all devices have all attributes this is the script I wrote and then scheduled to run nightly as the first part of a complicated SSIS job. This takes a text file with the attributes to add and adds them if they do not already exist. If an device has the attribute "New Device Status" and it is not blank and does not have the value of "Re-initialize" or "Add New" then it adds the attributes in the file to the device.

Not really an "Action" within wug but is rather meant to be externally run. You will need to run it as an account with permission to connect to the database and make changes. Definately run this at your own risk!!!

This script does not add any attribute values, that is up to the reader to figure out (or later I'll post my overly complicated ssis job maybe). This effectively adds an attribute list of empty attributes, this can clear out any existing attributes that are in the attribute file if you are not careful.

Language:

VB Script

Code:

'Uncomment this if you are running manually
'On Error Resume Next
' This is where your attribute file is, One attribute per line
Const AttrFile = "C:\AddAttributes\Attributes.txt"
' This is the "static" attribute to look for. You can base your script actions
'  on this attribute. If it does not exist for a device, or exists and is empty 
'  or has the value of "Add New" then treat like a newly added machine.
Const RESULT_ATTRIBUTE = "New Device Status"

' This is your database connection string, modify this to suit your environment
'  Remove any "<" or ">" symbols
Const strWUGsql = "Driver={SQL Server};Server=<my.server.internal>;" & _ 
                "Database=<whatsup>;UID=<WhatsUp-ReadWrite>;PWD=<password>"
Const adOpenStatic = 3
Const adLockOptimistic = 3

Set o_Db = CreateObject("ADODB.Connection")

o_Db.Open strWUGsql
             
'Open Our Database
If (db_connect(o_Db,strWUGsql) <> 0) Then
    WScript.Echo "Failed : Unable to open Database"
    Quit
End If

'Build the Sql query.
v_Sql = "SELECT Device.nDeviceID FROM Device WHERE Device.bRemoved = 0"

'Create a recordset to run the query
Set o_Record = CreateObject("ADODB.Recordset")
If Err Then
    WScript.Echo "Failed : Unable to create recordset"
    Quit
End If

'Run the query
o_Record.Open v_Sql,o_Db,3
If Err Then
    WScript.Echo "Failed : Unable to perform query"
    Quit
End If

'Everything is fine if there are no records...
If o_Record.RecordCount = 0 Then
    WScript.Echo "Succeeded : Everything is UP !! Congrats !!"
    Quit
End If

'Browse through the records and build the text list
o_Record.MoveFirst

' Go through each device and check attributes
While Not o_Record.Eof
    Check_Attributes(o_Record("nDeviceID"))
    o_Record.MoveNext
Wend
Wscript.Echo "Complete!"
Quit

'--------------------------------------------------------------------
'Attribute Add - WUG attribute update and addition script
'Zachary Loeber Feb 2009
'--------------------------------------------------------------------
Sub Check_Attributes(deviceID)    
    Dim oFSO, oFile, AttrText
    Dim strAction
    
    ' Device ID from wug
    nDeviceID  = deviceID
    
    ' Retrieve DB object
    Set oDb = CreateObject("ADODB.Connection")
    
    If (db_connect(oDb,strWUGsql) <> 0) Then
        WScript.Echo "Alert failed : Unable to open Database"
         Quit
    End If

    '-------------------------------------------------
    ' Check for "New Device Status" attribute
    '-------------------------------------------------
    sqlCheck         = "SELECT nDeviceAttributeID,sValue FROM DeviceAttribute WHERE (nDeviceID = " & _
                        nDeviceID & ") AND (sName = '" & RESULT_ATTRIBUTE & "')"
    Set oRs1         = oDb.Execute(sqlCheck)
    
    ' Checking if the attribute already exists. If it exists and is empty 
    '  then treat it like a new device to update     
    If ( Not oRs1.EOF ) Then 
        If IsEmpty(oRs1("sValue")) Then
            strAction = "Add New"
        Else
            strAction = oRs1("sValue")
        End If
    Else 
        strAction = "Add New"
    End If
    
    ' If no results returned then it doesn't exist so add it and other attributes
    '    or if attribute is set to Re-initialize then try to setup all the attributes again
    If (strAction = "Re-Initialize") Or (strAction = "Add New") Then
        ATTRIBUTE_VALUE = "In Progress"
        ' If it doesn't already exist add new device status attribute
        If ( oRs1.EOF ) Then
            sqlcmd = "INSERT INTO DeviceAttribute (nDeviceID, sName, sValue) VALUES ("& _
            nDeviceID & ",'" & RESULT_ATTRIBUTE & "','" & ATTRIBUTE_VALUE & "')"
            oDb.Execute(sqlcmd)
        End If
        
        ' Update the value of new device status
        sqlcmd = "UPDATE DeviceAttribute SET sValue = '" & ATTRIBUTE_VALUE & _
        "' WHERE (nDeviceID = " & nDeviceID & ") AND (sName = '" & RESULT_ATTRIBUTE & "')"
        oDb.Execute(sqlcmd)
    
        Set oFSO = CreateObject("Scripting.FileSystemObject")
        If oFSO.FileExists(AttrFile) Then
            Set oFile = oFSO.OpenTextFile(AttrFile, 1)
             Do While Not oFile.AtEndOfStream
              AttrText = oFile.ReadLine
               If Trim(AttrText) <> "" Then                   
                    '--------------------------------------------------------------------
                    ' Check for attributes, if exists update, if not create the attribute
                    '--------------------------------------------------------------------
                    RESULT_ATTRIBUTE = AttrText
                    ' Checking if the attribute already exists. If not we create one.
                    sqlCheck         = "SELECT nDeviceAttributeID FROM DeviceAttribute WHERE (nDeviceID = " & _
                                    nDeviceID & ") AND (sName = '" & RESULT_ATTRIBUTE & "')"
                    Set oRs1         = oDb.Execute(sqlCheck)
                
                    If oRs1.EOF Then
                        sqlcmd = "INSERT INTO DeviceAttribute (nDeviceID, sName, sValue) VALUES (" & _
                        nDeviceID & ", '" & RESULT_ATTRIBUTE & "', '" & value & "')"
                        oDb.Execute(sqlcmd)
                    Else 
                        sqlcmd = ""
                    End If                            
               End If
             Loop
            oFile.Close
        Else
            Wscript.Echo "Custom Attribute File Does Not Exist: " & AttrFile
        End If
    End If    
End Sub

Function db_connect( byRef curSession ,connection_string)
    dim connection
    on error resume next
    ' Opening connection
    set connection = CreateObject("ADODB.Connection")
    If Err.Number <> 0 then
       db_connect= "Error # " & CStr(Err.Number) & " " & Err.Description
        err.clear
        Exit Function
    End If
  
    connection.Open connection_string
    If Err.Number <> 0 then
        db_connect= "Error # " & CStr(Err.Number) & " " & Err.Description
        err.clear
        Exit Function
    End If
    set curSession=connection
    db_connect=0
End Function

Disclaimer:

All scripts found on wugSpace are provided as-is with no warranty or guarantee. These scripts are not supported by Ipswitch Technical Support. If you have a problem with this script, please ask any questions in the comments.

Be the first to rate this
|
Sign in to rate this