Home » Scripts » Query a database from a task

Contoso.se

Welcome to contoso.se! My name is Anders Bengtsson and this is my blog about Microsoft infrastructure and system management. I am a principal engineer in the FastTrack for Azure team, part of Azure CXP, at Microsoft. Contoso.se has two main purposes, first as a platform to share information with the community and the second as a notebook for myself.

Everything you read here is my own personal opinion and any code is provided "AS-IS" with no warranties.

Anders Bengtsson

MVP
MVP awarded 2007,2008,2009,2010

My Books
Service Manager Unleashed
Service Manager Unleashed
Orchestrator Unleashed
Orchestrator 2012 Unleashed
OMS
Inside the Microsoft Operations Management Suite

Contoso.se

Welcome to contoso.se! My name is Anders Bengtsson and this is my blog about Azure infrastructure and system management. I am a senior engineer in the FastTrack for Azure team, part of Azure Engineering, at Microsoft.  Contoso.se has two main purposes, first as a platform to share information with the community and the second as a notebook for myself.

Everything you read here is my own personal opinion and any code is provided "AS-IS" with no warranties.



MVP awarded 2007,2008,2009,2010

My Books

Service Manager Unleashed


Orchestrator 2012 Unleashed


Inside the Microsoft Operations Management Suite

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 comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.