Home » Scripts (Page 3)
Category Archives: Scripts
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”
$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”
$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”
$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
“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.
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:
' GET PARAMETERS INTO SCRIPT
strAlert = Wscript.Arguments.Item(0)
‘ LOG TO FILE (DATE,TIME,TO,ALERT NAME,STATE
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.
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")
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.
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 \\10.1.1.10\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.
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.
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()
If varString = 0 Then
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')
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.
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”)
“Provider=SQLOLEDB;Data Source=R2B1;” & _
“Trusted_Connection=Yes;Initial Catalog=ContosoConfiguration;” & _
objRecordSet.Open “SELECT * FROM roles”, _
objConnection, adOpenStatic, adLockOptimistic
varNo = objRecordSet.Record.Count
If varNO > 5 Then
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.
- Create a Timed Script Two State Monitor, input suitable name, scheudle, target and description.
- Input this script below
- As unhealthy expression input “Property[@Name=’Status’] equals Error”
- As healthy expression input “Property[@Name=’Status’] equals Ok”
- 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
This is a script that checks if there is a new file, contains a keyword, and that the file has been created last X hours. For example if you have a couple of servers uploading files every hours, you can check with this script if all servers have uploaded a new file the last hour. Even if the filename is not always the same, for example many applications add a timestamp to the filename like server05_20081012.
Configuration.txt contains all keywords that should be included in a filename. Contoso_filecheck.hta is a GUI that you can use to add keyword, delete keywords and show current keywords. contoso_filechecker.vbs is the script that does the filecheck.
- The script counts number of files in the folder, that was created last X hours (default folder is C:\LogFiles and default time is 24 hours)
- The script creates an array of all the files that was created last X hours
- The script loops all selected files and checks if the keyword is in the filename. It then moves on to the next keyword and loops all selected filenames.
- If the keyword, for example a server1, is in Configuration.txt but there is no file including server1 created last 24 hours, a local event will be created including the time configuration and the keyword.
- You can configure MOM 2005 or Ops Mgr to pickup these events and generate an alert.
Download the script here.