I have a PowerShell Script that executes a SQL query. It is set to run every
10mins. For about the past week it has periodically stopped running and I have
to restart the SCOM service on the endpoint to get it going again. Not sure if
it's of interest, but when I look at the log file it writes to, the timestamps
are not always 10mins apart. Sometimes they are, but then there are other times
where it is running much more often. Here is a screenshot of the log output:
[cid:[email protected]]
I am also attaching the two snippets related to this monitor. Please excuse any
formatting problems or issues with Find/Replace. I use the Visual Studio
Authoring Extension, so I had to copy the snippets and sanitize them elsewhere,
so hopefully they still make sense. My goal is to figure out why the script
isn't honoring the 10min intervals, as well as why it stops running altogether.
I am not able to find anything in the OpsMgr logs on the management servers or
the endpoint. I am happy to learn about any troubleshooting steps or other
areas I should be referencing to diagnose this issue.
Thank you.
-Geoff
Confidentiality Notice: This is a transmission from Community Hospital of the
Monterey Peninsula. This message and any attached documents may be confidential
and contain information protected by state and federal medical privacy
statutes. They are intended only for the use of the addressee. If you are not
the intended recipient, any disclosure, copying, or distribution of this
information is strictly prohibited. If you received this transmission in error,
please accept our apologies and notify the sender. Thank you.
param($SQLServer,
$SQLQuery,
$LogFile = "C:\Temp\Results.log"
)
# Functions to log process/results #
function Write-Log
{
[cmdletbinding()]
Param(
[Parameter(Mandatory=$true,
Position=0)]
[string]$Message
)
If(-not (Test-Path (Split-Path $LogFile -Parent)))
{
New-Item -Path (Split-Path $LogFile -Parent) -ItemType Directory
}
If(-not (Test-Path $LogFile))
{
New-Item -Path (Split-Path $LogFile -Parent) -Name (Split-Path $LogFile
-Leaf) -ItemType File
}
Add-Content -Path $LogFile -Value "$(Get-Date) | $Message$newLine"
}
function Start-Log
{
[cmdletbinding()]
Param(
[Parameter(Mandatory=$true,
Position=0)]
[string]$Entry
)
If(-not (Test-Path (Split-Path $LogFile -Parent)))
{
New-Item -Path (Split-Path $LogFile -Parent) -ItemType Directory
}
If(-not (Test-Path $LogFile))
{
New-Item -Path (Split-Path $LogFile -Parent) -Name (Split-Path $LogFile
-Leaf) -ItemType File
}
Add-Content -Path $LogFile -Value
"========================================================================"
Write-Log $Entry
}
# Create SCOM property bag
$api = New-Object -ComObject "MOM.ScriptAPI"
$bag = $api.CreatePropertyBag()
# SQL Connection and gathering results
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Integrated Security =
True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
Start-Log -Entry "Executing SQL Query"
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
Write-Log "Populating Query Results"
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$Results = $dataset.tables[0]
# Gather queue error counts
Write-Log "Evaulating error count results"
$1errCount = ($Results.que_name | ? {$_ -like "1err*"}).Count
$2errCount = ($Results.que_name | ? {$_ -like "2err*"}).Count
$3errCount = ($Results.que_name | ? {$_ -like "3err*"}).Count
$4errCount = ($Results.que_name | ? {$_ -like "4err*"}).Count
$5errCount = ($Results.que_name | ? {$_ -like "5err*"}).Count
$6errCount = ($Results.que_name | ? {$_ -like "6err*"}).Count
$7errCount = ($Results.que_name | ? {$_ -like "7err*"}).Count
# Total errors from all queues
[int]$TotalErrors = $1errCount + $1errCount + $2errCount + $3errCount +
$4errCount + $5errCount + $6errCount + $7errCount
# Gather all Batch IDs
$IDs = $Results.inq_batch_id
If($IDs.Count -gt 0){
$BatchIDs = $IDs | foreach {$_}
$ListIDs= [string]::join([environment]::newline, $BatchIDs)
}
Write-Log "Results:"
Write-Log " ArcErrCount: $1errCount"
Write-Log " BDIErrCount: $2errCount"
Write-Log " ColdErrCount: $3errCount"
Write-Log " EOBErrCount: $4errCount"
Write-Log " MagErrCount: $5errCount"
Write-Log " SignErrCount: $6errCount"
Write-Log " AnsiErrCount: $7errCount"
Write-Log " Total Errors: $TotalErrors"
# Add data to Property Bag for SCOM Processing #
$bag.AddValue('1errCount',$1errCount)
$bag.AddValue('2errCount',$2errCount)
$bag.AddValue('3errCount',$3errCount)
$bag.AddValue('4errCount',$4errCount)
$bag.AddValue('5errCount',$5errCount)
$bag.AddValue('6errCount',$6errCount)
$bag.AddValue('7errCount',$7errCount)
$bag.AddValue('TotalErrors',$TotalErrors)
$bag.AddValue('BatchIDs',$BatchIDs)
# Uncomment next line to validate data within PowerShell Console
# $api.return($bag)
# Return data to SCOM
$bag
<ManagementPackFragment SchemaVersion="2.0"
xmlns:xsd="http://www.w3.org/2001/XMLSchema";>
<Monitoring>
<Monitors>
<UnitMonitor ID="Acme.App.Monitor.SQLQuery.PSScript"
Accessibility="Public" Enabled="true" Target="Acme.App.DatabaseServer"
ParentMonitorID="Health!System.Health.AvailabilityState" Remotable="true"
Priority="Normal" TypeID="Acme.App.MonitorType.PSScript"
ConfirmDelivery="false">
<Category>AvailabilityHealth</Category>
<AlertSettings
AlertMessage="Acme.App.Monitor.SQLQuery.PSScript.AlertMessage">
<AlertOnState>Error</AlertOnState>
<AutoResolve>true</AutoResolve>
<AlertPriority>Normal</AlertPriority>
<AlertSeverity>MatchMonitorHealth</AlertSeverity>
<AlertParameters>
<AlertParameter1>$Data/Context/Property[@Name='TotalErrors']$</AlertParameter1>
<AlertParameter2>$Data/Context/Property[@Name='1ErrorCount']$</AlertParameter2>
<AlertParameter3>$Data/Context/Property[@Name='2ErrCount']$</AlertParameter3>
<AlertParameter4>$Data/Context/Property[@Name='3ErrCount']$</AlertParameter4>
<AlertParameter5>$Data/Context/Property[@Name='4ErrCount']$</AlertParameter5>
<AlertParameter6>$Data/Context/Property[@Name='5ErrCount']$</AlertParameter6>
<AlertParameter7>$Data/Context/Property[@Name='6ErrCount']$</AlertParameter7>
<AlertParameter8>$Data/Context/Property[@Name='7ErrCount']$</AlertParameter8>
<AlertParameter9>$Data/Context/Property[@Name='BatchIDs']$</AlertParameter9>
</AlertParameters>
</AlertSettings>
<OperationalStates>
<OperationalState ID="UnderThreshold"
MonitorTypeStateID="UnderThreshold" HealthState="Success" />
<OperationalState ID="OverErrorThreshold"
MonitorTypeStateID="OverErrorThreshold" HealthState="Error" />
</OperationalStates>
<Configuration>
<IntervalSeconds>600</IntervalSeconds>
<SyncTime />
<Threshold>1</Threshold>
<SQLServer>SQLHOSTNAME</SQLServer>
<SQLQuery>
Removed for privacy
</SQLQuery>
<LogFile>C:\Scripts\Logs\SQLQuery_Results.log</LogFile>
</Configuration>
</UnitMonitor>
</Monitors>
</Monitoring>
<Presentation>
<StringResources>
<StringResource ID="Acme.App.Monitor.SQLQuery.PSScript.AlertMessage" />
</StringResources>
</Presentation>
<LanguagePacks>
<LanguagePack ID="ENU" IsDefault="true">
<DisplayStrings>
<DisplayString ElementID="Acme.App.Monitor.SQLQuery.PSScript">
<Name>Monitor Name</Name>
<Description>Monitor Description</Description>
</DisplayString>
<DisplayString
ElementID="Acme.App.Monitor.SQLQuery.PSScript.AlertMessage">
<Name>Alert Name</Name>
<Description>Removed for privacy.</Description>
</DisplayString>
<DisplayString ElementID="Acme.App.Monitor.SQLQuery.PSScript"
SubElementID="UnderThreshold">
<Name>UnderThreshold</Name>
<Description>UnderThreshold</Description>
</DisplayString>
<DisplayString ElementID="Acme.App.Monitor.SQLQuery.PSScript"
SubElementID="OverErrorThreshold">
<Name>OverErrorThreshold</Name>
<Description>OverErrorThreshold</Description>
</DisplayString>
</DisplayStrings>
</LanguagePack>
</LanguagePacks>
</ManagementPackFragment>
