{"id":1112,"date":"2009-10-11T09:49:59","date_gmt":"2009-10-11T07:49:59","guid":{"rendered":"http:\/\/contoso.se\/blog\/?p=1112"},"modified":"2009-10-11T09:49:59","modified_gmt":"2009-10-11T07:49:59","slug":"query-a-database-from-a-task","status":"publish","type":"post","link":"https:\/\/contoso.se\/blog\/?p=1112","title":{"rendered":"Query a database from a task"},"content":{"rendered":"

I have seen a number of questions where Operations Manager operators would like to have more info about\u00c2\u00a0windows 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.<\/p>\n

\"querydb\"<\/a><\/p>\n

\u00c2\u00a0
\nThe script<\/p>\n

‘## Get parameter (computer name) into the script
\nset oArgs=wscript.Arguments
\n‘## Query the database for info
\nConst adOpenStatic = 3
\nConst adLockOptimistic = 3<\/p>\n

Set oAPI = CreateObject(“MOM.ScriptAPI”)
\nSet oBag = oAPI.CreatePropertyBag()<\/p>\n

Set objConnection = CreateObject(“ADODB.Connection”)
\nSet objRecordSet = CreateObject(“ADODB.Recordset”)<\/p>\n

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

objRecordSet.Open “SELECT * FROM machines WHERE FQDN LIKE ‘%” & oArgs(0) & “%'”, _
\nobjConnection, adOpenStatic, adLockOptimistic<\/p>\n

varNo = objRecordSet.RecordCount<\/p>\n

Do Until objRecordSet.EOF
\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Wscript.Echo “**********************************************”
\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Wscript.Echo “*\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 *”
\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Wscript.Echo “*\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Contoso Machine Database \u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 *”\u00c2\u00a0
\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Wscript.Echo “*\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 *”
\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Wscript.Echo “**********************************************”
\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Wscript.Echo ” ”
\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Wscript.Echo ” ”
\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Wscript.Echo “Hostname: ” & objRecordSet.Fields.Item(“Hostname”)
\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Wscript.Echo “FQDN: ” & objRecordSet.Fields.Item(“FQDN”)
\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Wscript.Echo “SLA level: ” & objRecordSet.Fields.Item(“SLAlevel”)
\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Wscript.Echo “Owner: ” & objRecordSet.Fields.Item(“Owner”)
\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Wscript.Echo “Role: ” & objRecordSet.Fields.Item(“Role”)
\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Wscript.Echo “Location: ” & objRecordSet.Fields.Item(“Location”)
\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Wscript.Echo “Service: ” & objRecordSet.Fields.Item(“Service”)
\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Wscript.Echo “Note: ” & objRecordSet.Fields.Item(“Note”)
\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Wscript.Echo ” ”
\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Wscript.Echo ” ”
\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 Wscript.Echo “**********************************************”
\n\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 objRecordSet.MoveNext
\nLoop<\/p><\/blockquote>\n

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<\/p>\n