Hi I have a cookdown vb script that gets property bag per SQL Instance, with a collection of values for each SQL Job. I have a perf collection rule to collect the "last completed run status" of the job, and I have a monitor to go healthy if the lastrunstatus is 1 or 5, and critical if the lastrunstatus is 0.
The cookdown script collects the data as expected, and the rule is writing to the DB as expected. The monitor is not changing state though, it shows all the jobs as healthy, although one is not. It doesn't seem to be picking up the value collected by the DataGenericMapper module. I've attached the management pack, this is what it consists of. The Timed Script Property Bag Provider is hard coded with Interval and SynTime as I didn't want these overridden. Data Source (To collect the data) GarethCookDownSQLJob.VBScript * (Data Source) Timed Script Property Bag Provider * (Conditon Detection) Performance DataGenericMapper Data Source (To Filter on the jobs) GarethCookDownSQLJob.VBScriptFiltered * (Data Source) GarethCookDownSQLJob.VBScript * (Conditon Detection) System Expression Filter MonitorType * (Data Source) GarethCookDownSQLJob.VBScriptFiltered * (Conditon Detection) System Expression Filter (Healthy) * (Conditon Detection) System Expression Filter (Critical) Monitor (To monitor Job Last Run Status) Rule (To collect Job Last Run Status) Any help would be appreciated Kind Regards [ITServiceAssurance_Logo (2)] Gareth Miles System Center Technical Lead Phone: +27 31 580 1582 Mobile: +27 83 648 8559 ############################################################################################# The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. Furthermore, the information contained in this message, and any attachments thereto, is for information purposes only and may contain the personal views and opinions of the author, which are not necessarily the views and opinions of the company. #############################################################################################
<ManagementPack ContentReadable="true" xmlns:xsd="http://www.w3.org/2001/XMLSchema"; xmlns:xsl="http://www.w3.org/1999/XSL/Transform";> <Manifest> <Identity> <ID>GarethCookDownSQLJob</ID> <Version>1.0.0.10</Version> </Identity> <Name>GarethCookDownSQLJob</Name> <References> <Reference Alias="MicrosoftSQLServerLibrary"> <ID>Microsoft.SQLServer.Library</ID> <Version>6.4.1.0</Version> <PublicKeyToken>31bf3856ad364e35</PublicKeyToken> </Reference> <Reference Alias="SC"> <ID>Microsoft.SystemCenter.Library</ID> <Version>6.1.7221.0</Version> <PublicKeyToken>31bf3856ad364e35</PublicKeyToken> </Reference> <Reference Alias="Windows"> <ID>Microsoft.Windows.Library</ID> <Version>6.1.7221.0</Version> <PublicKeyToken>31bf3856ad364e35</PublicKeyToken> </Reference> <Reference Alias="Health"> <ID>System.Health.Library</ID> <Version>6.1.7221.0</Version> <PublicKeyToken>31bf3856ad364e35</PublicKeyToken> </Reference> <Reference Alias="System"> <ID>System.Library</ID> <Version>6.1.7221.0</Version> <PublicKeyToken>31bf3856ad364e35</PublicKeyToken> </Reference> <Reference Alias="Performance"> <ID>System.Performance.Library</ID> <Version>6.1.7221.0</Version> <PublicKeyToken>31bf3856ad364e35</PublicKeyToken> </Reference> </References> </Manifest> <TypeDefinitions> <ModuleTypes> <DataSourceModuleType ID="GarethCookDownSQLJob.VBScript" Accessibility="Public" Batching="false"> <Configuration> <xsd:element minOccurs="1" name="ConnectionString" type="xsd:string" /> </Configuration> <ModuleImplementation Isolation="Any"> <Composite> <MemberModules> <DataSource ID="Script" TypeID="Windows!Microsoft.Windows.TimedScript.PropertyBagProvider"> <IntervalSeconds>300</IntervalSeconds> <SyncTime>00:00</SyncTime> <ScriptName>CookDownScript.vbs</ScriptName> <Arguments>$Config/ConnectionString$</Arguments> <ScriptBody><![CDATA[ '--------------------------------------------------------------------------------------------------------------------------------------- ' Script Configuration '--------------------------------------------------------------------------------------------------------------------------------------- ' SQL Script Source: Thomas Parry ' Script Interval: 5 Minutes ' Script target: SQL Jobs ' Scripted by: Gareth Miles ' Creation Date: 2015-07-23 ' Script Params: <string> Connection String ' Test Command: opsmgrdb1 True ' Description: Query to check for no game play '--------------------------------------------------------------------------------------------------------------------------------------- Option Explicit Dim oArgs, ConnectionString, computerName, randomValue, oAPI, oBag, alertTextInfo, JobStatus, makeDBConnection, Debug Dim objectName,counterName,instanceName,intValue, detailMsg, ManagedEntityId, creatProcSQL, WshNetwork, JobID, JobName Dim databaseName, objRecordSet, objConnection, dbObject, queryString, SourceID, alertDetails, lastcompleted_runstatus, IsNaN Dim description, category_name, category_excluded, enabled, jobstatus_description, failedstep, jobfailure_message Dim stepfailure_message, is_job_running, current_step, step_name, current_step_runtime_seconds, last10_runduration_seconds Dim last25_runduration_seconds, last100_runduration_seconds, JobLastRunStatus, AVGThresholdPercent, AVGThreshold, LongRunningJobResult Const adOpenStatic = 3 Const adLockOptimistic = 3 Const adUseClient = 3 Const EVENT_TYPE_ERROR = 1 Const EVENT_TYPE_WARNING = 2 Const EVENT_TYPE_INFORMATION = 4 Const dbNotFoundErrorNo = 11112 '----------------------------------------- ' Options that need to be changed '----------------------------------------- Const scriptFileName = "CookDownScript.vbs" Const logScriptRunSuccess = True Const NUMBER_OF_ARGUMENTS_REQUIRED = 1 Const ScriptAlertID = 15998 Const ScriptRunID = 15998 Set oAPI = CreateObject("MOM.ScriptAPI") Set oArgs = WScript.Arguments checkScriptArguments 'Check the parameter count ConnectionString = oArgs(0) Debug = True DatabaseName = "master" checDBExists ConnectionString, databaseName, scriptFileName Set objConnection = CreateObject("ADODB.Connection") objConnection.Provider = "sqloledb" objConnection.ConnectionTimeout = 60 objConnection.Open "server="&ConnectionString&";Database="&databaseName&";Trusted_Connection=yes;Persist Security Info=True" Set WshNetwork = CreateObject("WScript.Network") ComputerName = WshNetwork.ComputerName '############################################################################################################## ' Logic Section '############################################################################################################## 'If ComputerName = "MGS1401" or ComputerName = "DERITSADB01" then queryString = "WITH currentlyrunningjobs /* get a list of currently running jobs */" & vbnewline &_ "AS (" & vbnewline &_ " SELECT sja.session_id" & vbnewline &_ " ,sja.job_id" & vbnewline &_ " ,run_requested_date" & vbnewline &_ " ,sja.start_execution_date" & vbnewline &_ " ,sja.last_executed_step_id" & vbnewline &_ " ,isnull(last_executed_step_date, sja.start_execution_date) AS last_executed_step_date /* if stepdate is null i believe its the first step that ran so we can use the job starttime*/" & vbnewline &_ " FROM msdb.dbo.sysjobactivity sja" & vbnewline &_ " INNER JOIN (" & vbnewline &_ " SELECT TOP 1 session_id" & vbnewline &_ " FROM msdb.dbo.syssessions" & vbnewline &_ " ORDER BY agent_start_date DESC" & vbnewline &_ " ) ss" & vbnewline &_ " ON sja.session_id = ss.session_id" & vbnewline &_ " WHERE start_execution_date IS NOT NULL" & vbnewline &_ " AND stop_execution_date IS NULL" & vbnewline &_ " ) " & vbnewline &_ " ,currentlyrunningjobstep /* work out which step is running for the jobs found above*/" & vbnewline &_ "AS (" & vbnewline &_ " SELECT sj.NAME" & vbnewline &_ " ,cr.job_id" & vbnewline &_ " ,cr.start_execution_date" & vbnewline &_ " ,cr.last_executed_step_date" & vbnewline &_ " ,CASE /* check to see what the action on the previous step is, * i am expecting any steps that fail to fail the job! */" & vbnewline &_ " WHEN last_executed_step_id IS NULL" & vbnewline &_ " THEN sj.start_step_id" & vbnewline &_ " ELSE CASE " & vbnewline &_ " WHEN on_success_action = 3 THEN last_executed_step_id + 1" & vbnewline &_ " WHEN on_success_action = 4 THEN on_success_step_id" & vbnewline &_ " END" & vbnewline &_ " END AS current_step " & vbnewline &_ " FROM currentlyrunningjobs cr" & vbnewline &_ " INNER JOIN msdb.dbo.sysjobs sj" & vbnewline &_ " ON sj.job_id = cr.job_id" & vbnewline &_ " INNER JOIN msdb.dbo.sysjobsteps sjs" & vbnewline &_ " ON cr.job_id = sjs.job_id" & vbnewline &_ " AND isnull(cr.last_executed_step_id, sj.start_step_id) = sjs.step_id" & vbnewline &_ " )" & vbnewline &_ " ,lastcompletedjobhistorymaxentry /*find what the latest completed status is */" & vbnewline &_ "AS (" & vbnewline &_ " SELECT job_id" & vbnewline &_ " ,max(instance_id) AS instance_id" & vbnewline &_ " FROM msdb.dbo.sysjobhistory" & vbnewline &_ " --WHERE step_id = 0 /*i thought we needed to check only completed jobs but maybe this is not the case.*/" & vbnewline &_ " GROUP BY job_id" & vbnewline &_ " ) " & vbnewline &_ " ,lastcompletedstatuswithdetails /* check info of the running steps*/" & vbnewline &_ "AS (" & vbnewline &_ " SELECT sjh.job_id" & vbnewline &_ " ,sjh.instance_id" & vbnewline &_ " ,message" & vbnewline &_ " ,run_status" & vbnewline &_ " ,msdb.dbo.agent_datetime(run_date, run_time) AS jobcompletiondate" & vbnewline &_ " ,run_duration" & vbnewline &_ " FROM lastcompletedjobhistorymaxentry lcjhme" & vbnewline &_ " LEFT JOIN msdb.dbo.sysjobhistory sjh" & vbnewline &_ " ON lcjhme.job_id = sjh.job_id" & vbnewline &_ " AND lcjhme.instance_id = sjh.instance_id" & vbnewline &_ " )" & vbnewline &_ " ,previousjobstephistoryruntimes /* get the previous run history for any running jobs, this is below 3 times and convert the run_duration*/" & vbnewline &_ "AS (" & vbnewline &_ " SELECT sjh.job_id" & vbnewline &_ " ,sjh.step_id" & vbnewline &_ " ,sjh.run_status" & vbnewline &_ " ,sjh.run_date" & vbnewline &_ " ,sjh.run_time" & vbnewline &_ " ,sjh.run_duration" & vbnewline &_ " ,CASE /* run_duration is stored as HHMMSS, not as TIME */" & vbnewline &_ " WHEN len(sjh.run_duration) > 4" & vbnewline &_ " THEN convert(VARCHAR(4), left(sjh.run_duration, len(sjh.run_duration) - 4)) * 3600 + left(right(sjh.run_duration, 4), 2) * 60 + right(sjh.run_duration, 2)" & vbnewline &_ " WHEN len(sjh.run_duration) = 4" & vbnewline &_ " THEN left(sjh.run_duration, 2) * 60 + right(sjh.run_duration, 2)" & vbnewline &_ " WHEN len(sjh.run_duration) = 3" & vbnewline &_ " THEN left(sjh.run_duration, 1) * 60 + right(sjh.run_duration, 2)" & vbnewline &_ " ELSE right(sjh.run_duration, 2)" & vbnewline &_ " END AS run_duration_seconds" & vbnewline &_ " ,row_number() OVER (" & vbnewline &_ " PARTITION BY sjh.job_id ORDER BY instance_id DESC" & vbnewline &_ " ) AS id" & vbnewline &_ " FROM msdb.dbo.sysjobhistory sjh" & vbnewline &_ " INNER JOIN currentlyrunningjobstep crjs" & vbnewline &_ " ON sjh.job_id = crjs.job_id" & vbnewline &_ " AND crjs.current_step = sjh.step_id" & vbnewline &_ " ) " & vbnewline &_ " ,alljobs /* combine all the above ctes as a single job view */" & vbnewline &_ "AS (" & vbnewline &_ " SELECT sj.originating_server_id" & vbnewline &_ " ,sj.job_id" & vbnewline &_ " ,sj.NAME" & vbnewline &_ " ,sj.description" & vbnewline &_ " ,sj.enabled" & vbnewline &_ " ,CASE " & vbnewline &_ " WHEN crjs.current_step IS NULL THEN 0" & vbnewline &_ " ELSE 1" & vbnewline &_ " END AS is_job_running" & vbnewline &_ " ,crjs.current_step" & vbnewline &_ " ,sjst.step_name" & vbnewline &_ " ,crjs.last_executed_step_date" & vbnewline &_ " ,sc.NAME AS category_name" & vbnewline &_ " ,CASE WHEN sc.NAME LIKE ('repl-%') /* hard coded exclusion list */" & vbnewline &_ " OR sc.NAME IN (" & vbnewline &_ " 'copy agents'" & vbnewline &_ " ,'database maintenance'" & vbnewline &_ " ,'bolt replay jobs'" & vbnewline &_ " ,'etl jobs'" & vbnewline &_ " ,'dba maintenance'" & vbnewline &_ " ,'backups'" & vbnewline &_ " ,'transform'" & vbnewline &_ " )" & vbnewline &_ " THEN 1" & vbnewline &_ " ELSE 0" & vbnewline &_ " END AS category_excluded" & vbnewline &_ " ,sjs.last_run_outcome AS lastcompleted_runstatus" & vbnewline &_ " ,CASE sjs.last_run_outcome" & vbnewline &_ " WHEN 0 THEN 'failed'" & vbnewline &_ " WHEN 1 THEN 'succeeded'" & vbnewline &_ " WHEN 2 THEN 'retrying step'" & vbnewline &_ " WHEN 3 THEN 'canceled'" & vbnewline &_ " WHEN 4 THEN 'in-progress message'" & vbnewline &_ " WHEN 5 THEN 'unknown'" & vbnewline &_ " END AS jobstatus_description" & vbnewline &_ " ,CASE " & vbnewline &_ " WHEN sjs.last_run_outcome = 0" & vbnewline &_ " THEN lcjhwd.message" & vbnewline &_ " ELSE NULL" & vbnewline &_ " END AS jobfailure_message" & vbnewline &_ " FROM msdb.dbo.sysjobs sj" & vbnewline &_ " LEFT JOIN currentlyrunningjobstep crjs" & vbnewline &_ " ON sj.job_id = crjs.job_id" & vbnewline &_ " LEFT JOIN msdb.dbo.sysjobsteps sjst" & vbnewline &_ " ON crjs.job_id = sjst.job_id" & vbnewline &_ " AND crjs.current_step = sjst.step_id" & vbnewline &_ " INNER JOIN msdb.dbo.syscategories sc" & vbnewline &_ " ON sj.category_id = sc.category_id" & vbnewline &_ " LEFT JOIN lastcompletedstatuswithdetails lcjhwd" & vbnewline &_ " ON lcjhwd.job_id = sj.job_id" & vbnewline &_ " LEFT JOIN msdb.dbo.sysjobservers sjs" & vbnewline &_ " ON sjs.job_id = sj.job_id" & vbnewline &_ " )" & vbnewline &_ " ,lastfailureentry " & vbnewline &_ "AS (" & vbnewline &_ " SELECT max(instance_id) AS instance_id" & vbnewline &_ " ,aj.job_id" & vbnewline &_ " FROM alljobs aj" & vbnewline &_ " INNER JOIN msdb.dbo.sysjobhistory sjs" & vbnewline &_ " ON aj.job_id = sjs.job_id" & vbnewline &_ " WHERE lastcompleted_runstatus = 0" & vbnewline &_ " AND step_id <> 0" & vbnewline &_ " GROUP BY aj.job_id" & vbnewline &_ " )" & vbnewline &_ " ,jobfailuremessage " & vbnewline &_ "AS (" & vbnewline &_ " SELECT sjs.job_id" & vbnewline &_ " ,step_id" & vbnewline &_ " ,message" & vbnewline &_ " FROM msdb.dbo.sysjobhistory sjs" & vbnewline &_ " INNER JOIN lastfailureentry lfe" & vbnewline &_ " ON sjs.job_id = lfe.job_id" & vbnewline &_ " AND sjs.instance_id = lfe.instance_id" & vbnewline &_ " )" & vbnewline &_ " , currentstepruntime10 /* get the last 10 runs on a specific step and average the run time */" & vbnewline &_ "AS (" & vbnewline &_ " SELECT crjs.job_id" & vbnewline &_ " ,avg(av10.run_duration_seconds) AS last10_runduration_seconds" & vbnewline &_ " FROM currentlyrunningjobstep crjs" & vbnewline &_ " INNER JOIN previousjobstephistoryruntimes av10" & vbnewline &_ " ON av10.job_id = crjs.job_id" & vbnewline &_ " WHERE av10.id < 10" & vbnewline &_ " GROUP BY crjs.job_id" & vbnewline &_ " )" & vbnewline &_ " ,currentstepruntime25 /* get the last 25 runs on a specific step and average the run time */" & vbnewline &_ "AS (" & vbnewline &_ " SELECT crjs.job_id" & vbnewline &_ " ,avg(av25.run_duration_seconds) AS last25_runduration_seconds" & vbnewline &_ " FROM currentlyrunningjobstep crjs" & vbnewline &_ " INNER JOIN previousjobstephistoryruntimes av25" & vbnewline &_ " ON av25.job_id = crjs.job_id" & vbnewline &_ " WHERE av25.id < 25" & vbnewline &_ " GROUP BY crjs.job_id" & vbnewline &_ " )" & vbnewline &_ " ,currentstepruntime100 /* get the last 100 runs on a specific step and average the run time */" & vbnewline &_ "AS (" & vbnewline &_ " SELECT crjs.job_id" & vbnewline &_ " ,avg(av100.run_duration_seconds) AS last100_runduration_seconds" & vbnewline &_ " FROM currentlyrunningjobstep crjs" & vbnewline &_ " INNER JOIN previousjobstephistoryruntimes av100" & vbnewline &_ " ON av100.job_id = crjs.job_id" & vbnewline &_ " WHERE av100.id < 100" & vbnewline &_ " GROUP BY crjs.job_id" & vbnewline &_ " )" & vbnewline &_ "" & vbnewline &_ "SELECT ss.name as instancename" & vbnewline &_ " ,aj.job_id" & vbnewline &_ " ,aj.name as jobname" & vbnewline &_ " ,aj.description" & vbnewline &_ " ,aj.category_name" & vbnewline &_ " ,aj.category_excluded" & vbnewline &_ " ,aj.enabled" & vbnewline &_ " ,aj.lastcompleted_runstatus" & vbnewline &_ " ,aj.jobstatus_description" & vbnewline &_ " ,aj.jobfailure_message" & vbnewline &_ " ,jfm.step_id as failedstep" & vbnewline &_ " ,jfm.message as stepfailure_message" & vbnewline &_ " ,aj.is_job_running" & vbnewline &_ " ,aj.current_step" & vbnewline &_ " ,step_name" & vbnewline &_ " , datediff(ss,aj.last_executed_step_date, getdate()) as current_step_runtime_seconds" & vbnewline &_ " ,last10_runduration_seconds" & vbnewline &_ " ,last25_runduration_seconds" & vbnewline &_ " ,last100_runduration_seconds" & vbnewline &_ "FROM alljobs aj" & vbnewline &_ "Left JOIN sys.servers ss on ss.server_id = aj.originating_server_id" & vbnewline &_ "LEFT JOIN jobfailuremessage jfm on jfm.job_id = aj.job_id" & vbnewline &_ "LEFT JOIN currentstepruntime10 csrt10" & vbnewline &_ " ON csrt10.job_id = aj.job_id" & vbnewline &_ "LEFT JOIN currentstepruntime25 csrt25" & vbnewline &_ " ON csrt25.job_id = aj.job_id" & vbnewline &_ "LEFT JOIN currentstepruntime100 csrt100" & vbnewline &_ " ON csrt100.job_id = aj.job_id" & vbnewline &_ "ORDER BY aj.name" Set objRecordset = objConnection.Execute(queryString) detailMsg = "Script: " & scriptFileName & ", Server: " & computerName & ", SQLServer: " & ConnectionString & vbnewline Do While Not objRecordSet.EOF JobID = objRecordSet("job_id") jobname = objRecordSet("jobname") description = objRecordSet("description") category_name = objRecordSet("category_name") category_excluded = objRecordSet("category_excluded") enabled = objRecordSet("enabled") jobstatus_description = objRecordSet("jobstatus_description") category_excluded = objRecordSet("category_excluded") is_job_running = objRecordSet("is_job_running") step_name = objRecordSet("step_name") If IsNull(objRecordSet("lastcompleted_runstatus")) then lastcompleted_runstatus = "1" Else lastcompleted_runstatus = objRecordSet("lastcompleted_runstatus") End If If IsNull(objRecordSet("jobfailure_message")) then jobfailure_message = "NA" Else jobfailure_message = objRecordSet("jobfailure_message") End If If IsNull(objRecordSet("failedstep")) then failedstep = "NA" Else failedstep = objRecordSet("failedstep") End If If IsNull(objRecordSet("stepfailure_message")) then stepfailure_message = "NA" Else stepfailure_message = objRecordSet("stepfailure_message") End If If IsNull(objRecordSet("current_step")) then current_step = "NA" Else current_step = objRecordSet("current_step") End If If IsNull(objRecordSet("step_name")) then step_name = "NA" Else step_name = objRecordSet("step_name") End If If IsNull(objRecordSet("current_step_runtime_seconds")) then current_step_runtime_seconds = "NA" Else current_step_runtime_seconds = objRecordSet("current_step_runtime_seconds") End If If enabled = 1 and lastcompleted_runstatus = 0 or lastcompleted_runstatus = 5 Then '(lastcompleted_runstatus) 0 = Failed, 1 = Succeeded JobLastRunStatus = "Good" Else JobLastRunStatus = "Bad" End If If IsNull(objRecordSet("last10_runduration_seconds")) then last10_runduration_seconds = "0" Else last10_runduration_seconds = objRecordSet("last10_runduration_seconds") End If If IsNull(objRecordSet("last25_runduration_seconds")) then last25_runduration_seconds = "0" Else last25_runduration_seconds = objRecordSet("last25_runduration_seconds") End If If IsNull(objRecordSet("last100_runduration_seconds")) then last100_runduration_seconds = "0" Else last100_runduration_seconds = objRecordSet("last100_runduration_seconds") End If If IsNull(objRecordSet("current_step_runtime_seconds")) then current_step_runtime_seconds = "0" Else current_step_runtime_seconds = objRecordSet("current_step_runtime_seconds") End If AVGThresholdPercent = (last10_runduration_seconds /100*30) AVGThreshold = last10_runduration_seconds + AVGThresholdPercent If (AVGThreshold > current_step_runtime_seconds) and (current_step_runtime_seconds > 1200) Then LongRunningJobResult = "Bad" Else LongRunningJobResult = "Good" End If Set oBag = oAPI.CreatePropertyBag() Call oBag.AddValue("ComputerName", ComputerName) Call oBag.AddValue("instancename", ConnectionString) Call oBag.AddValue("JobID", JobID) Call oBag.AddValue("jobname", jobname) Call oBag.AddValue("description", description) Call oBag.AddValue("enabled", enabled) Call oBag.AddValue("category_name", category_name) Call oBag.AddValue("category_excluded", category_excluded) Call oBag.AddValue("lastcompleted_runstatus", lastcompleted_runstatus) Call oBag.AddValue("jobstatus_description", jobstatus_description) Call oBag.AddValue("jobfailure_message", jobfailure_message) Call oBag.AddValue("failedstep", failedstep) Call oBag.AddValue("stepfailure_message", stepfailure_message) Call oBag.AddValue("is_job_running", is_job_running) Call oBag.AddValue("current_step", current_step) Call oBag.AddValue("step_name", step_name) Call oBag.AddValue("current_step_runtime_seconds", current_step_runtime_seconds) Call oBag.AddValue("last10_runduration_seconds", last10_runduration_seconds) Call oBag.AddValue("last25_runduration_seconds", last25_runduration_seconds) Call oBag.AddValue("last100_runduration_seconds", last100_runduration_seconds) Call oBag.AddValue("JobLastRunStatus", JobLastRunStatus) Call oBag.AddValue("LongRunningJobResult", LongRunningJobResult) Call oAPI.AddItem(oBag) detailMsg = detailMsg & "JobName: " & JobName & vbnewline &_ "JobLastRunStatus: " & JobLastRunStatus & vbnewline &_ "enabled: " & enabled & vbnewline &_ "lastcompleted_runstatus: " & lastcompleted_runstatus & vbnewline &_ "jobstatus_description: " & jobstatus_description & vbnewline &_ "failedstep: " & failedstep & vbnewline &_ "is_job_running: " & is_job_running & vbnewline &_ "current_step: " & current_step & vbnewline &_ "LongRunningJobResult: " & LongRunningJobResult & vbnewline & vbnewline & vbnewline objRecordSet.moveNext Loop objRecordSet.Close objConnection.Close oAPI.ReturnItems() CreateDebugAlert detailMsg 'ELSE ' If Debug = True Then ' Set oAPI = CreateObject("MOM.ScriptAPI") ' CreateAlert computerName, ScriptAlertID, "INFORMATION", "This is not MGS1401, aborting this test script." ' WScript.Quit ' ELSE ' WScript.Quit ' End If ' 'End If '############################################################################################################## ' End the logic section '############################################################################################################## 'If (logScriptRunSuccess = True) Then logScriptRunSuccessSub Sub CheckScriptArguments Dim errorMessage, arg, lineSep lineSep = "===================================================" If oArgs.Count <> NUMBER_OF_ARGUMENTS_REQUIRED Then errorMessage = lineSep & vbnewline & " " & scriptFileName & vbnewline & lineSep & vbnewline If oArgs.Count > NUMBER_OF_ARGUMENTS_REQUIRED Then errorMessage = errorMessage & "Too Many Arguments: " Else errorMessage = errorMessage & "Too Little Arguments: " End If errorMessage = errorMessage & oArgs.Count & "/" & NUMBER_OF_ARGUMENTS_REQUIRED & vbnewline &_ "Arguments recieved: " & getScriptArguments(", ") & vbnewline & "Please correct this" CreateAlert scriptFileName, 660, "ERROR", errorMessage Wscript.Quit() End If End Sub Function getScriptArguments( sep ) Dim outString, arg, GSAargCount, GSAcurrentArgCount GSAargCount = oArgs.Count GSAcurrentArgCount = 1 For Each arg In oArgs If GSAcurrentArgCount <> GSAargCount Then outString = outString & arg & sep Else outString = outString & arg End If GSAcurrentArgCount = GSAcurrentArgCount + 1 Next getScriptArguments = outString End Function Sub logScriptRunSuccessSub Dim strLogSuccessTest strLogSuccessTest = "Script ran successfully." & vbnewline & vbnewline &_ "Script Name: " & scriptFileName & vbnewline &_ "Arguments: " & getScriptArguments(", ") & vbnewline & vbnewline &_ "To re-run the script use the following call it like below." &_ vbnewline & " " & scriptFileName & " " & getScriptArguments(" ") CreateAlert scriptFileName, ScriptRunID, "INFORMATION", strLogSuccessTest End Sub Sub CreateAlert(scriptName, eventID, eventType, eventDescription) eventType = UCase(eventType) Select Case eventType Case "ERROR" eventType = EVENT_TYPE_ERROR Case "WARNING" eventType = EVENT_TYPE_WARNING Case "INFORMATION" eventType = EVENT_TYPE_INFORMATION End Select Call oAPI.LogScriptEvent(scriptName,eventID,eventType,eventDescription) End Sub Sub CreateDebugAlert(detailMsg) If Debug = "True" Then CreateAlert computerName, ScriptAlertID, "INFORMATION", detailMsg End If End Sub Sub checkStoredProcExists( procName ) Dim queryString, errorMsg, objRecordset1 Set objRecordset1 = CreateObject("ADODB.Recordset") queryString = "if object_id('" & databaseName & ".." & procName & "') is NULL " & vbnewline &_ "Begin " & vbnewline &_ "Select 0 as wasFound" & vbnewline &_ "End " & vbnewline &_ "else " & vbnewline &_ "Begin " & vbnewline &_ "Select 1 as wasFound " & vbnewline &_ "End" objRecordset1.Open queryString, dbObject 'If proc missing and allowed to create it, else quit If objRecordset1.Fields("wasFound") = "0" Then WScript.Quit() End If Set objRecordset1 = Nothing End Sub Sub checDBExists( dbServer, strDbName, strScriptName ) Dim cde_objCon, cde_objRS, cde_msg Set cde_objCon = CreateObject("ADODB.Connection") Set cde_objRS = CreateObject("ADODB.Recordset") cde_objCon.Provider = "sqloledb" cde_objCon.ConnectionTimeout = 60 cde_objCon.Open "server=" & dbServer & ";Database=master;Trusted_Connection=yes;Persist Security Info=True" cde_objRS.Open "select COUNT(*) as wasFound from sys.databases where name = '" & strDbName & "'", cde_objCon '// Alert that the DB was not found If ( Cint(cde_objRS("wasFound")) = 0 ) Then cde_msg = "Could not run script (" & strScriptName & ") because the DB (" & strDbName &_ ") was not found on the instance (" & dbServer & ")." Set cde_objCon = Nothing Set cde_objRS = Nothing CreateAlert computerName, 11112, "WARNING", cde_msg Wscript.Quit End If Set cde_objCon = Nothing Set cde_objRS = Nothing End Sub ]]></ScriptBody> <TimeoutSeconds>60</TimeoutSeconds> </DataSource> <ConditionDetection ID="PerfMapper" TypeID="Performance!System.Performance.DataGenericMapper"> <ObjectName>SQLJob</ObjectName> <CounterName>JobLastRunStatus</CounterName> <InstanceName>$Data/Property[@Name='JobID']$</InstanceName> <Value>$Data/Property[@Name='lastcompleted_runstatus']$</Value> </ConditionDetection> </MemberModules> <Composition> <Node ID="PerfMapper"> <Node ID="Script" /> </Node> </Composition> </Composite> </ModuleImplementation> <OutputType>Performance!System.Performance.Data</OutputType> </DataSourceModuleType> <DataSourceModuleType ID="GarethCookDownSQLJob.VBScriptFiltered" Accessibility="Public" Batching="false"> <Configuration> <xsd:element minOccurs="1" name="JobID" type="xsd:string" /> <xsd:element minOccurs="1" name="ConnectionString" type="xsd:string" /> </Configuration> <ModuleImplementation Isolation="Any"> <Composite> <MemberModules> <DataSource ID="Script" TypeID="GarethCookDownSQLJob.VBScript"> <ConnectionString>$Config/ConnectionString$</ConnectionString> </DataSource> <ConditionDetection ID="FilterSQLJob" TypeID="System!System.ExpressionFilter"> <Expression> <SimpleExpression> <ValueExpression> <XPathQuery Type="String">InstanceName</XPathQuery> </ValueExpression> <Operator>Equal</Operator> <ValueExpression> <Value Type="String">$Config/JobID$</Value> </ValueExpression> </SimpleExpression> </Expression> </ConditionDetection> </MemberModules> <Composition> <Node ID="FilterSQLJob"> <Node ID="Script" /> </Node> </Composition> </Composite> </ModuleImplementation> <OutputType>Performance!System.Performance.Data</OutputType> </DataSourceModuleType> </ModuleTypes> <MonitorTypes> <UnitMonitorType ID="GarethCookDownSQLJob.JobHealthMontiorType" Accessibility="Public"> <MonitorTypeStates> <MonitorTypeState ID="JobHealthy" NoDetection="false" /> <MonitorTypeState ID="JobError" NoDetection="false" /> </MonitorTypeStates> <Configuration> <xsd:element minOccurs="1" name="JobID" type="xsd:string" /> <xsd:element minOccurs="1" name="ConnectionString" type="xsd:string" /> </Configuration> <MonitorImplementation> <MemberModules> <DataSource ID="DS" TypeID="GarethCookDownSQLJob.VBScriptFiltered"> <JobID>$Config/JobID$</JobID> <ConnectionString>$Config/ConnectionString$</ConnectionString> </DataSource> <ConditionDetection ID="JobIsHealthy" TypeID="System!System.ExpressionFilter"> <Expression> <Or> <Expression> <SimpleExpression> <ValueExpression> <XPathQuery Type="String">Property[@Name='lastcompleted_runstatus']</XPathQuery> </ValueExpression> <Operator>Equal</Operator> <ValueExpression> <Value Type="Integer">1</Value> </ValueExpression> </SimpleExpression> </Expression> <Expression> <SimpleExpression> <ValueExpression> <XPathQuery Type="String">Property[@Name='lastcompleted_runstatus']</XPathQuery> </ValueExpression> <Operator>Equal</Operator> <ValueExpression> <Value Type="Integer">5</Value> </ValueExpression> </SimpleExpression> </Expression> </Or> </Expression> </ConditionDetection> <ConditionDetection ID="JobIsError" TypeID="System!System.ExpressionFilter"> <Expression> <SimpleExpression> <ValueExpression> <XPathQuery Type="String">Property[@Name='lastcompleted_runstatus']</XPathQuery> </ValueExpression> <Operator>Equal</Operator> <ValueExpression> <Value Type="String">0</Value> </ValueExpression> </SimpleExpression> </Expression> </ConditionDetection> </MemberModules> <RegularDetections> <RegularDetection MonitorTypeStateID="JobHealthy"> <Node ID="JobIsHealthy"> <Node ID="DS" /> </Node> </RegularDetection> <RegularDetection MonitorTypeStateID="JobError"> <Node ID="JobIsError"> <Node ID="DS" /> </Node> </RegularDetection> </RegularDetections> </MonitorImplementation> </UnitMonitorType> </MonitorTypes> </TypeDefinitions> <Monitoring> <Rules> <Rule ID="GarethCookDownSQLJob.CollectJobLastRunState" Enabled="true" Target="MicrosoftSQLServerLibrary!Microsoft.SQLServer.AgentJob" ConfirmDelivery="true" Remotable="true" Priority="Normal" DiscardLevel="100"> <Category>Custom</Category> <DataSources> <DataSource ID="DS" TypeID="GarethCookDownSQLJob.VBScriptFiltered"> <JobID>$Target/Property[Type="MicrosoftSQLServerLibrary!Microsoft.SQLServer.AgentJob"]/JobID$</JobID> <ConnectionString>$Target/Property[Type="MicrosoftSQLServerLibrary!Microsoft.SQLServer.AgentJob"]/OriginatingServer$</ConnectionString> </DataSource> </DataSources> <WriteActions> <WriteAction ID="WriteToDB" TypeID="SC!Microsoft.SystemCenter.CollectPerformanceData" /> </WriteActions> </Rule> </Rules> <Monitors> <UnitMonitor ID="GarethCookDownSQLJob.JobLastRunState" Accessibility="Public" Enabled="true" Target="MicrosoftSQLServerLibrary!Microsoft.SQLServer.AgentJob" ParentMonitorID="Health!System.Health.PerformanceState" Remotable="true" Priority="Normal" TypeID="GarethCookDownSQLJob.JobHealthMontiorType" ConfirmDelivery="true"> <Category>Custom</Category> <AlertSettings AlertMessage="GarethCookDownSQLJob.JobLastRunState_AlertMessageResourceID"> <AlertOnState>Error</AlertOnState> <AutoResolve>true</AutoResolve> <AlertPriority>Normal</AlertPriority> <AlertSeverity>Error</AlertSeverity> <AlertParameters> <AlertParameter1>$Data/Context/Property[@Name='jobname']$</AlertParameter1> </AlertParameters> </AlertSettings> <OperationalStates> <OperationalState ID="UIGeneratedOpStateId63f01a4973e9417c8a22a0de562bbd91" MonitorTypeStateID="JobHealthy" HealthState="Success" /> <OperationalState ID="UIGeneratedOpStateId7f2a282adf164d7d8902b8a86ca2cbac" MonitorTypeStateID="JobError" HealthState="Error" /> </OperationalStates> <Configuration> <JobID>$Target/Property[Type="MicrosoftSQLServerLibrary!Microsoft.SQLServer.AgentJob"]/JobID$</JobID> <ConnectionString>$Target/Property[Type="MicrosoftSQLServerLibrary!Microsoft.SQLServer.AgentJob"]/OriginatingServer$</ConnectionString> </Configuration> </UnitMonitor> </Monitors> </Monitoring> <Presentation> <StringResources> <StringResource ID="GarethCookDownSQLJob.JobLastRunState_AlertMessageResourceID" /> </StringResources> </Presentation> <LanguagePacks> <LanguagePack ID="ENS" IsDefault="false"> <DisplayStrings> <DisplayString ElementID="GarethCookDownSQLJob"> <Name>Gareth CookDown SQL Job</Name> </DisplayString> <DisplayString ElementID="GarethCookDownSQLJob.JobLastRunState" SubElementID="UIGeneratedOpStateId63f01a4973e9417c8a22a0de562bbd91"> <Name>JobHealthy</Name> </DisplayString> <DisplayString ElementID="GarethCookDownSQLJob.JobLastRunState" SubElementID="UIGeneratedOpStateId7f2a282adf164d7d8902b8a86ca2cbac"> <Name>JobError</Name> </DisplayString> <DisplayString ElementID="GarethCookDownSQLJob.JobLastRunState_AlertMessageResourceID"> <Name>Job Last Run State Bad</Name> <Description>Please see the alert context for details. JobName: {0}</Description> </DisplayString> <DisplayString ElementID="GarethCookDownSQLJob.VBScript"> <Name>GarethCookDownSQLJob.VBScript</Name> </DisplayString> <DisplayString ElementID="GarethCookDownSQLJob.VBScriptFiltered"> <Name>GarethCookDownSQLJob.VBScriptFiltered</Name> </DisplayString> </DisplayStrings> </LanguagePack> <LanguagePack ID="ENU" IsDefault="true"> <DisplayStrings> <DisplayString ElementID="GarethCookDownSQLJob"> <Name>Gareth CookDown SQL Job</Name> <Description /> </DisplayString> <DisplayString ElementID="GarethCookDownSQLJob.CollectJobLastRunState"> <Name>GarethCookDownSQLJob.CollectJobLastRunState</Name> </DisplayString> <DisplayString ElementID="GarethCookDownSQLJob.JobHealthMontiorType"> <Name>GarethCookDownSQLJob.JobHealthMontiorType</Name> </DisplayString> <DisplayString ElementID="GarethCookDownSQLJob.JobLastRunState"> <Name>GarethCookDownSQLJob.JobLastRunState</Name> </DisplayString> <DisplayString ElementID="GarethCookDownSQLJob.JobLastRunState" SubElementID="UIGeneratedOpStateId63f01a4973e9417c8a22a0de562bbd91"> <Name>JobHealthy</Name> </DisplayString> <DisplayString ElementID="GarethCookDownSQLJob.JobLastRunState" SubElementID="UIGeneratedOpStateId7f2a282adf164d7d8902b8a86ca2cbac"> <Name>JobError</Name> </DisplayString> <DisplayString ElementID="GarethCookDownSQLJob.JobLastRunState_AlertMessageResourceID"> <Name>Job Last Run State Bad</Name> <Description>Please see the alert context for details.</Description> </DisplayString> <DisplayString ElementID="GarethCookDownSQLJob.VBScript"> <Name>GarethCookDownSQLJob.VBScript</Name> <Description /> </DisplayString> <DisplayString ElementID="GarethCookDownSQLJob.VBScriptFiltered"> <Name>GarethCookDownSQLJob.VBScriptFiltered</Name> </DisplayString> </DisplayStrings> </LanguagePack> </LanguagePacks> </ManagementPack>
