Home » Scripts (Page 3)

Category Archives: Scripts


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.

Anders Bengtsson

MVP awarded 2007,2008,2009,2010

My Books
Service Manager Unleashed
Service Manager Unleashed
Orchestrator 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.


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 “**********************************************”

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

Look for new databases (…with a pinch of DPM)

With SQL Server Audit, SQL Server 2008 introduces an important new feature that provides a true auditing solution for enterprise customers. While SQL Trace can be used to satisfy many auditing needs, SQL Server Audit offers a number of attractive advantages that may help DBAs more easily achieve their goals such as meeting regulatory compliance requirements. These include the ability to provide centralized storage of audit logs and integration with System Center, as well as noticeably better performance. Perhaps most significantly, SQL Server Audit permits fine-grained auditing whereby an audit can be targeted to specific actions by a principal against a particular object. This paper provides a comprehensive description of the new feature along with usage guidance and then provides some practical examples. Source MSDN

If you want to get an alert when a new database is created in SQL 2008 you will first need to configure auditing on SQL side, and then a rule in Operations Manager to generate an alert. Configure a new audit with audit destination equals Application log or security log. If you select security log you might need to configure some extra security permissions. Create a new server audit policy, configure audit action type equals DATABASE_CHANGE_GROUP.


Next step is to create the rule that will pickup the SQL event and generate an alert. Create a new event based rule, target it to for example SQL Servers, to monitor all your SQL machines. Configure the rule to look for event ID 33205 including CREATE and DATABASE in the event description.


In the expression of the rule, we use “.” to tell Operations Manager “any character before, around or after the two keywords, CREATE and DATABASE.



Another step that you might want to do is to verify that you backup the new SQL database. I modified a power shell script and the result is that it will connect to your DPM server and a SQL box, it will then ask you if you want to add any of the unprotected databases on that server to a protection group in DPM. 

param([string] $ProductionServer, [string] $PGName)
$ProductionServer = read-host "Enter the production server name (a SQL server protected by DPM)"
$PGName = read-host "Enter the name of your existing SQL protection group name"

$dpmservername = read-host “Enter the name of your DPM server”

connect-dpmserver $dpmservername

$PGList = @(Get-ProtectionGroup $dpmservername)

foreach ($PG in $PGList)
if($PG.FriendlyName -eq $PGName)
write-host “Found protection group $PGName”
$MPG = Get-ModifiableProtectionGroup $PG

write-host “Protection Group $PGName does not exist”
exit 1

$PSList=@(Get-ProductionServer $dpmservername)
$DsList = @()

foreach ($PS in $PSList)
if($PS.NetBiosName -eq $ProductionServer)
write-host “Running Inquiry on” $PS.NetbiosName
$DSlist += Get-Datasource -ProductionServer $PS -Inquire

“Production Server $PS does not exist”
exit 1

$protectedDsList = @()
foreach ($ds in $dslist)
if($ds.ToString(“T”, $null) -match “SQL” -and !$ds.Protected)
$toadd = read-host “Do you want to protect the” $ds.Name “database? (y/n)?”
If ($toadd -eq “y”)
$protectedDsList += $ds
Add-ChildDatasource -ProtectionGroup $MPG -ChildDatasource $ds
$x=Get-DatasourceDiskAllocation -Datasource $ds
Set-DatasourceDiskAllocation -Datasource $x -ProtectionGroup $MPG

Set-ReplicaCreationMethod -ProtectionGroup $MPG -Now

write-host “Adding new SQL DBs to” $MPG.FriendlyName
Set-protectiongroup $MPG

disconnect-dpmserver $dpmservername
“Exiting from script”


(tested in a sandbox, so I am aware that the ops mgr databases are not protected and all the test databases) If you want to integrate the script into Ops Mgr you should read this post from David Allen.

Configure Command Notification in R2

There has been a number of questions about the command notification channel in Technet forums. Here is a example how to configure a command notification channel, a subscriber for the channel and a subscription. This example configure a command notification channel to run a vbscript. The vbscript echo a time stamp and the alertname to a local file.

1. Go to Administration/Notification/Channels and create a new command notification channel. For example
Channel name: Contoso Command Notification Channel
Full path of the command file: C:\windows\system32\cmd.exe
Command line paramters: /c C:\logfile.vbs $Data/Context/DataItem/AlertName$
Startup folder for the command line: C:\

2. Go to Subscribers and create a new subscriber
Subscriber name: Contoso Command Line Subscriber (note that you do not need to select a AD user)
Schedule: Always send notification
Addresses: Added a new address
-Address name: Contoso Command Address
-Channel type: Command
-Command Channel: Contoso Command Notification Channel
-Delivery address for the selected channel: I just left this with default, something about sip: but it resultet in a blank field, so just leave it
-Schedule: Always send notification

3. Goto Subscriptions and add a new subscription, for example
Name: Contoso Command Line Subscription
Criteria: with a specific resolution state: new(0) and closed (255)
Subscribers: Contoso Command Line Subscriber
Channel: Contoso Command Notification Channel
Summary: Make sure the channel is enabled

Thats it, and the logfile.vbs looks like:

' ******************************************
' ******************************************
strAlert = Wscript.Arguments.Item(0)

‘ ******************************************
‘ ******************************************
Dim Stuff, myFSO, WriteStuff, dateStamp
strdateStamp = Date()
strTimeStamp = Time()
‘Write information to Text File
Stuff = strDateStamp & “,” & strTimeStamp & “,” & strAlert
Set myFSO = CreateObject(“Scripting.FileSystemObject”)
Set WriteStuff = myFSO.OpenTextFile(“opsmgr_notification_logfile.txt”, 8, True)

More info about command notification channel here.

Changing Passwords with Operations Manager

Yesterday when I was going to do some tests in one of my labs I could not remember the password. Fortunately the Operations Manager R2 RMS machine was unlocked so I could use it. I then created a rule that ran a modification of the following script on each machine, and swish! I could logon again 🙂

Set WshNetwork = WScript.CreateObject("WScript.Network")
strComputer = WshNetwork.ComputerName
strComputer = "."
Set objUser = GetObject("WinNT://" & strComputer & "/Administrator,user")
objUser.SetPassword "NewPasswordHere"

With that in mind it is funny how some organization look at security and accounts. It is not unusual that a consultant or co-worker get Administrator permissions or Author permissions in Operations Manager, but they would never get full exchange administrator or domain administrator permissions in the environment.

Some interesting link regarding the subject, Ops Mgr security guide and Ops Mgr Security Hardening Guide.

Measure the time it takes to write a file

Update, download a MP with this script here, Contoso.Write.File.to.Share

I have written a script that writes a line of text to a file on a share. It will then return the time it took in the strTimer parameter. You can use this script to measure the time it takes to write text to a file on your network. The default share is \\\share. You can also change the loop (default 1500) to write the text line more times to the file, that would result in a test with a larger file.

test-Imapconnectivity together with PS1,VBS and R2

The Exchange 2007 management packs runs a number of test power shell cmdlet to check that Exchange is working. In some scenarios you cannot use the standard Exchange test cmdlet created by the New-TestCasConnectivityUser.ps1 script. For example in scenarios with complex namespaces or security requirements. In the Ops Mgr 2007 SP1 Exchange 2007 MP (converted) you can override the cmdlet command but The MSExchangeMonitoring service do not support scripts and only a limited set of Exchange cmdlets (test-*) and parameters (positional parameters are not allowed). The accepted syntax is also more restricted than the one accepted by the Exchange Management Shell. So even if you add parameters to the cmdlet it will not work with Ops Mgr. The new Exchange 2007 MP for Ops Mgr R2 do not support override of the cmdlet command at all, but it includes a lot of other great news.

In this example I will run the test-Imapconnectivity cmdlet with the HQ\svc-opsmgr-exchange account. I will run the power shell commands from a vbscript, then picks up the result, analyze it and send it back to Ops Mgr. I use a file, C:\cred.txt, to store the password for the test user (hq\svc-opsmgr-exchange). This file is encrypted (by a PowerShell command) and can only be used by the user created it. That is why the run as profile is so important.

There are a couple of steps to make this work

1. Create a encrypted password file on the affected machines (C:\cred.txt). More info about that file here.

2. Create a management pack to discover the exchange machines you want to use. The class that the management pack discover will be used as target for your run-as profile and for the monitor.

3. Create a two state monitor with the script and settings below, configure it to run for example every 15 minute
Unhealthy Expression: Property[@Name=’ImapConnectivity’] Does not equal Ok
Healthy Expression: Property[@Name=’ImapConnectivity’] equals Ok
Under alerting you can add $Data/Context/Property[@Name=’ImapConnectivity’]$ to see the status and also $Data/Context/Property[@Name=’varPSResult’]$ to see the result from the power shell command.

4. Configure a account and a run-as profile with the same account as you used to encrypt the password file

5. Configure your monitor to use this profile. This can (as far as I know) only be done direct in XML or in the Authoring Console

6. Import everything into Operations Manager and verify your run-as profile and your account. It is important that both your monitor is configure to use the run as profile and that your profile target the affected class/object.

The script:

pscommand = "Add-PSSnapin Microsoft.Exchange.Management.PowerShell.Admin; $pass = get-content C:\cred.txt | convertto-securestring; $credential = new-object -typename System.Management.Automation.PSCredential -argumentlist 'hq\svc-opsmgr-exchange',$pass; $Result = Test-ImapConnectivity -MailboxCredential:$Credential -MonitoringContext:$true -ConnectionType:2 -TrustAnySSLCertificate:$true -LightMode:$true | ft Result; $Result"
cmd = "powershell.exe " & pscommand
Set shell = CreateObject("WScript.Shell")
Set executor = shell.Exec(cmd)

varPSResult = executor.StdOut.ReadAll
varString = InStr(varPSResult, "Success")

Dim oAPI, oBag
Set oAPI = CreateObject("MOM.ScriptAPI")
Set oBag = oAPI.CreatePropertyBag()

Call oBag.AddValue("Result",varPSResult)
Call oBag.AddValue("varString",varString)

If varString = 0 Then
Call oBag.AddValue("ImapConnectivity","Error")
Call oAPI.Return(oBag)
Call oBag.AddValue("ImapConnectivity","Ok")
Call oAPI.Return(oBag)
End If

Thanks to Pete Zerger (the run-as profile jedi) and Marco Shaw

List all reports in Ops Mgr 2007

If you want to get a complete list of the reports in your Operations Manager environment, you can use the following queries. Run these queries against the reportserver database. The first query gets all reports and the second query gets all reports with a name like SQL.

Select * from catalog where hidden = '0' and (type='2' or type='4')

Select * from catalog where hidden = '0' and name like '%sql%' and (type='2' or type='4')

Run a task with another account

If operators needs to run a task that they normally don’t have permissions to run you can use run as accounts and profiles. I was trying that in Ops Mgr 2007 R2 this week.

In my first scenario I needed operators in the user profile Contoso Operators to run the computer management task against a number of machines. But the operators don’t have permissions enough on the target machines. So I created a account under run as accounts and configure a new run as profile. In the profile I specified the account and target a group. In that group I had added a couple of health service objects, as the computer management task are target to the health service class. This worked, but everything target to health service was affected by the new run as account. The result was a working task but a couple of new “run-as-profile-account” alerts in the console.

My second idea was to create a new management pack including a new class and discovery rules for something on all the needed machines. I built this in the R2 Authoring Console. There are some good info about author management packs at this page.

When the discovery was working I added a task to run the computer management console. I then created a new profile, selected the same account as in the first scenario but target only my new class. When a operator now runs the task, it is target to the new class, and the profile with a specified account is also target to this new class. The result is that a operator can run the computer management task, with the specified account, even if they dont have enought permissions on their logged on domain account.

Query a database with a monitor

I have seen a number of questions about how to run queries against a database and verify the answer. One way is to run a script inside a monitor. In this blog I wrote how to setup a script in a two state monitor. The script in this post will count number of  fields, if there are more then five, status of the monitor will be changed. Note that counting starts at 0 with fields collection.

 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=R2B1;” & _
“Trusted_Connection=Yes;Initial Catalog=ContosoConfiguration;” & _
“User ID=CORP\Administrator;Password=;”

objRecordSet.Open “SELECT * FROM roles”, _
objConnection, adOpenStatic, adLockOptimistic

varNo = objRecordSet.Record.Count

If varNO > 5 Then
Call oBag.AddValue(“SQL_Status”,”Error”)
Call oBag.AddValue(“SQL_Status”,”Ok”)
End If

Call oAPI.Return(oBag)

Timed Script Two State Monitor

This is an example how to use a timed script two state monitor to monitor a file exists. If the file exists the monitor is healthy.

  1. Create a Timed Script Two State Monitor, input suitable name, scheudle, target and description.
  2. Input this script below
  3. As unhealthy expression input “Property[@Name=’Status’] equals Error”
  4. As healthy expression input “Property[@Name=’Status’] equals Ok”
  5. Configure alerts and then your done.

Dim oAPI, oBag
Set oAPI = CreateObject(“MOM.ScriptAPI”)
Set oBag = oAPI.CreatePropertyBag()
Set objFSO = CreateObject(“Scripting.FileSystemObject”)
strFile = “C:\myfile.log”
If objFSO.FileExists(strFile) Then
Call oBag.AddValue(“Status”,”Ok”)
Call oAPI.Return(oBag)
Call oBag.AddValue(“Status”,”Error”)
Call oAPI.Return(oBag)
End If