Query a database from a task

I have seen a number of questions where Operations Manager operators would like to have more info about windows machines in the console. You could include info in the console with a new management pack, but if you already have it in another database, it would be easier to query that database direct. I have written a script that will take a machine name (prinicipal name) and query a database for more information it.

querydb

 
The script

‘## Get parameter (computer name) into the script
set oArgs=wscript.Arguments
‘## Query the database for info
Const adOpenStatic = 3
Const adLockOptimistic = 3

Set oAPI = CreateObject(“MOM.ScriptAPI”)
Set oBag = oAPI.CreatePropertyBag()

Set objConnection = CreateObject(“ADODB.Connection”)
Set objRecordSet = CreateObject(“ADODB.Recordset”)

objConnection.Open _
“Provider=SQLOLEDB;Data Source=hq-opsmgr28;” & _
“Trusted_Connection=Yes;Initial Catalog=databasename_here;” & _
“User ID=domai\username;Password=Password_here;”

objRecordSet.Open “SELECT * FROM machines WHERE FQDN LIKE ‘%” & oArgs(0) & “%'”, _
objConnection, adOpenStatic, adLockOptimistic

varNo = objRecordSet.RecordCount

Do Until objRecordSet.EOF
    Wscript.Echo “**********************************************”
    Wscript.Echo “*                                            *”
    Wscript.Echo “*          Contoso Machine Database       *” 
    Wscript.Echo “*                                            *”
    Wscript.Echo “**********************************************”
    Wscript.Echo ” ”
    Wscript.Echo ” ”
    Wscript.Echo “Hostname: ” & objRecordSet.Fields.Item(“Hostname”)
    Wscript.Echo “FQDN: ” & objRecordSet.Fields.Item(“FQDN”)
    Wscript.Echo “SLA level: ” & objRecordSet.Fields.Item(“SLAlevel”)
    Wscript.Echo “Owner: ” & objRecordSet.Fields.Item(“Owner”)
    Wscript.Echo “Role: ” & objRecordSet.Fields.Item(“Role”)
    Wscript.Echo “Location: ” & objRecordSet.Fields.Item(“Location”)
    Wscript.Echo “Service: ” & objRecordSet.Fields.Item(“Service”)
    Wscript.Echo “Note: ” & objRecordSet.Fields.Item(“Note”)
    Wscript.Echo ” ”
    Wscript.Echo ” ”
    Wscript.Echo “**********************************************”
    objRecordSet.MoveNext
Loop

I store this script local (C:\scripts\querySQL.vbs) on the machine running the console, and call it from a task. The settings of the task are the following

  • Task Name: Contoso – query db
  • Task target: Windows Computer
  • Application: C:\windows\system32\cmd.exe
  • Parameters: /C cscript.exe C:\scripts\querySQL.vbs $Target/Property[Type=”Windows!Microsoft.Windows.Computer”]/PrincipalName$
  • Working directory: C:\Windows\system32

Leave a Reply

Your email address will not be published. Required fields are marked *