Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
<?xml version="1.0" encoding="utf-8"?><ManagementPack ContentReadable="true" SchemaVersion="2.0" OriginalSchemaVersion="1.1" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<Manifest>
<Identity>
<ID>Custom.SQLQueries</ID>
<Version>1.0.0.1</Version>
</Identity>
<Name>Custom - SQLQueries</Name>
<References>
<Reference Alias="MicrosoftWindowsLibrary7585010">
<ID>Microsoft.Windows.Library</ID>
<Version>7.5.8501.0</Version>
<PublicKeyToken>31bf3856ad364e35</PublicKeyToken>
</Reference>
<Reference Alias="MicrosoftSQLServer2014Discovery6620">
<ID>Microsoft.SQLServer.2014.Discovery</ID>
<Version>6.6.2.0</Version>
<PublicKeyToken>31bf3856ad364e35</PublicKeyToken>
</Reference>
<Reference Alias="SystemLibrary7585010">
<ID>System.Library</ID>
<Version>7.5.8501.0</Version>
<PublicKeyToken>31bf3856ad364e35</PublicKeyToken>
</Reference>
<Reference Alias="SystemCenter">
<ID>Microsoft.SystemCenter.Library</ID>
<Version>7.0.8433.0</Version>
<PublicKeyToken>31bf3856ad364e35</PublicKeyToken>
</Reference>
<Reference Alias="Health">
<ID>System.Health.Library</ID>
<Version>7.0.8433.0</Version>
<PublicKeyToken>31bf3856ad364e35</PublicKeyToken>
</Reference>
</References>
</Manifest>
<TypeDefinitions>
<SecureReferences>
<SecureReference ID="RunAsProfile_55f40f9a04ca47d88abc7d79e871fc77" Accessibility="Internal" />
</SecureReferences>
</TypeDefinitions>
<Monitoring>
<Monitors>
<UnitMonitor ID="UIGeneratedMonitor0f0288e8b46b40f99d90bd30a9e74099" RunAs="RunAsProfile_55f40f9a04ca47d88abc7d79e871fc77" Accessibility="Public" Enabled="false" Target="MicrosoftSQLServer2014Discovery6620!Microsoft.SQLServer.2014.Database" ParentMonitorID="Health!System.Health.AvailabilityState" Remotable="true" Priority="Normal" TypeID="MicrosoftWindowsLibrary7585010!Microsoft.Windows.TimedScript.TwoStateMonitorType" ConfirmDelivery="false">
<Category>Custom</Category>
<OperationalStates>
<OperationalState ID="UIGeneratedOpStateIda703d7f666384443aca6b5d657460b81" MonitorTypeStateID="Error" HealthState="Warning" />
<OperationalState ID="UIGeneratedOpStateId24de33994c1c46e9a6503ec1837206e0" MonitorTypeStateID="Success" HealthState="Success" />
</OperationalStates>
<Configuration>
<IntervalSeconds>300</IntervalSeconds>
<SyncTime>00:00</SyncTime>
<ScriptName>CollectionUpdateTime.vbs</ScriptName>
<Arguments>$Target/Host/Property[Type="MicrosoftSQLServer2014Discovery6620!Microsoft.SQLServer.2014.DBEngine"]/ConnectionString$ $Target/Property[Type="MicrosoftSQLServer2014Discovery6620!Microsoft.SQLServer.2014.Database"]/DatabaseName$</Arguments>
<ScriptBody>Set objArgs = Wscript.Arguments
Set oAPI = CreateObject("MOM.ScriptAPI")
Set oBag = oAPI.CreatePropertyBag()
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
Dim ConnString
ConnString = "DRIVER={SQL Server};Server=" &amp; objArgs(0) &amp; ";Database=" &amp; objArgs(1) &amp; ";"
objConnection.Open ConnString
objRecordSet.Open "SELECT TOP(1) CC.CollectionID, CN.CollectionName, CC.TimeUpdated FROM Collection_MemberChg_Notif CC JOIN Collections CN ON CC.CollectionID = CN.SiteID WHERE CN.CollectionName = 'Test Collection' AND CC.TimeUpdated &lt; DATEADD(mi, -20, GETDATE())", objConnection, 3, 3
oBag.AddValue "Records", objRecordSet.RecordCount
If objRecordSet.RecordCount &gt; 0 Then
oBag.AddValue "LastUpdated", objRecordSet.Fields("TimeUpdated")
Call oAPI.LogScriptEvent(objArgs(1), 101, 0, "Last Updated: " &amp; objRecordSet.Fields("TimeUpdated"))
Else
Call oAPI.LogScriptEvent(objArgs(1), 0, 0, "Up to date")
End If
oAPI.Return(oBag)</ScriptBody>
<TimeoutSeconds>60</TimeoutSeconds>
<ErrorExpression>
<SimpleExpression>
<ValueExpression>
<XPathQuery Type="String">Property[@Name="Records"]</XPathQuery>
</ValueExpression>
<Operator>Equal</Operator>
<ValueExpression>
<Value Type="String">1</Value>
</ValueExpression>
</SimpleExpression>
</ErrorExpression>
<SuccessExpression>
<SimpleExpression>
<ValueExpression>
<XPathQuery Type="String">Property[@Name="Records"]</XPathQuery>
</ValueExpression>
<Operator>Equal</Operator>
<ValueExpression>
<Value Type="String">0</Value>
</ValueExpression>
</SimpleExpression>
</SuccessExpression>
</Configuration>
</UnitMonitor>
</Monitors>
<Overrides>
<SecureReferenceOverride ID="SecureOverride09e092a9_252b_4569_ea7c_f1ca3ade999a" Context="SystemLibrary7585010!System.Entity" Enforced="false" SecureReference="RunAsProfile_55f40f9a04ca47d88abc7d79e871fc77">
<Value>00A7FB373A624502F8355CAAAC9CEA7B9A18429C6700000000000000000000000000000000000000</Value>
</SecureReferenceOverride>
</Overrides>
</Monitoring>
<Presentation>
<Folders>
<Folder ID="Folder_905ba04f467c492e934cacb3843495eb" Accessibility="Public" ParentFolder="SystemCenter!Microsoft.SystemCenter.Monitoring.ViewFolder.Root" />
</Folders>
</Presentation>
<LanguagePacks>
<LanguagePack ID="ENU" IsDefault="false">
<DisplayStrings>
<DisplayString ElementID="Custom.SQLQueries">
<Name>Custom - SQLQueries</Name>
<Description>This MP contains customised script monitors for SQL databases that are able to run under alternate credentials (as specified in the Run As Profiles.</Description>
</DisplayString>
<DisplayString ElementID="Folder_905ba04f467c492e934cacb3843495eb">
<Name>Custom - SQLQueries</Name>
</DisplayString>
<DisplayString ElementID="UIGeneratedMonitor0f0288e8b46b40f99d90bd30a9e74099">
<Name>CM_TST - Test Collection not updated</Name>
</DisplayString>
<DisplayString ElementID="UIGeneratedMonitor0f0288e8b46b40f99d90bd30a9e74099" SubElementID="UIGeneratedOpStateIda703d7f666384443aca6b5d657460b81">
<Name>Unhealthy</Name>
</DisplayString>
<DisplayString ElementID="UIGeneratedMonitor0f0288e8b46b40f99d90bd30a9e74099" SubElementID="UIGeneratedOpStateId24de33994c1c46e9a6503ec1837206e0">
<Name>Healthy</Name>
</DisplayString>
<DisplayString ElementID="RunAsProfile_55f40f9a04ca47d88abc7d79e871fc77">
<Name>SQL - System Center Monitor</Name>
<Description>Monitor profile for Microsoft System Center SQL databases.</Description>
</DisplayString>
</DisplayStrings>
</LanguagePack>
<LanguagePack ID="ENG" IsDefault="false">
<DisplayStrings>
<DisplayString ElementID="UIGeneratedMonitor0f0288e8b46b40f99d90bd30a9e74099" SubElementID="UIGeneratedOpStateIda703d7f666384443aca6b5d657460b81">
<Name>Unhealthy</Name>
</DisplayString>
<DisplayString ElementID="UIGeneratedMonitor0f0288e8b46b40f99d90bd30a9e74099" SubElementID="UIGeneratedOpStateId24de33994c1c46e9a6503ec1837206e0">
<Name>Healthy</Name>
</DisplayString>
</DisplayStrings>
</LanguagePack>
</LanguagePacks>
</ManagementPack>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.