Altair RISE

A program to recognize and reward our most engaged community members

Nominate Yourself Now!

Last used?

Does anyone know if there's any way to pull a complete report of Standard processes that are defined in Datawatch Automator versus the last time there was actually a report?

I'm trying to remove unused processes. I've run a distribution audit report but that's not giving me quite what I want.

Thanks!

Find more posts tagged with

English

Monarch

Accepted answers

AnwarAli

Hi Michelle.

The easiest way to get this information would be to query the Monarch Server database directly. It also depends on the Job Log history in your instance as this is the only way to determine what ran and when.

The following query should work for you, I am not sure what version of v15 youre using so tested it quickly on a 14.3 instance successfully;

SELECT SP_FirstLastData.ProcessType

, SP_FirstLastData.ProcessID

, SP_FirstLastData.ProcessName

, SP_FirstLastData.FirstPumpJobID

, SP_FirstLastData.LastPumpJobID

, SP_FirstLastData.HistoricJobCount

, SP_FirstLastData.FirstProcessed

, SP_FirstLastData.LastProcessed

, FirstPumpJobs.Status AS FirstJobStatus

, FirstJobStatusText = CASE FirstPumpJobs.Status

WHEN 0 THEN 'ProcessResolvingWebAddresses'

WHEN 20 THEN 'RunningPreProcessScript'

WHEN 30 THEN 'ProcessReady'

WHEN 40 THEN 'ProcessAwaitingRetry'

WHEN 50 THEN 'ProcessFailed'

WHEN 60 THEN 'ProcessCancelled'

WHEN 70 THEN 'Ready'

WHEN 80 THEN 'Deferred'

WHEN 90 THEN 'Running'

WHEN 91 THEN 'ExportFailedWaitingRetry'

WHEN 100 THEN 'Completed'

WHEN 110 THEN 'Failed'

WHEN 120 THEN 'Cancelled'

WHEN 130 THEN 'DispatcherHostFailure'

WHEN 140 THEN 'Orphan'

ELSE 'Unknown Status'

END

, LastPumpJobs.Status AS LastJobStatus

, LastJobStatusText = CASE LastPumpJobs.Status

WHEN 0 THEN 'ProcessResolvingWebAddresses'

WHEN 20 THEN 'RunningPreProcessScript'

WHEN 30 THEN 'ProcessReady'

WHEN 40 THEN 'ProcessAwaitingRetry'

WHEN 50 THEN 'ProcessFailed'

WHEN 60 THEN 'ProcessCancelled'

WHEN 70 THEN 'Ready'

WHEN 80 THEN 'Deferred'

WHEN 90 THEN 'Running'

WHEN 91 THEN 'ExportFailedWaitingRetry'

WHEN 100 THEN 'Completed'

WHEN 110 THEN 'Failed'

WHEN 120 THEN 'Cancelled'

WHEN 130 THEN 'DispatcherHostFailure'

WHEN 140 THEN 'Orphan'

ELSE 'Unknown Status'

END

FROM ( SELECT 'SP' AS ProcessType

, PJ.PumpTaskId AS ProcessID

, PT.PumpTaskName AS ProcessName

, COUNT(*) AS HistoricJobCount

, MIN(PJ.PumpJobId) AS FirstPumpJobID

, MAX(PJ.PumpJobId) AS LastPumpJobID

, MIN(PJ.ProcessedTime) AS FirstProcessed

, MAX(PJ.ProcessedTime) AS LastProcessed

FROM dbo.PumpJobs AS PJ

LEFT OUTER JOIN dbo.PumpTasks AS PT ON PJ.PumpTaskId = PT.PumpTaskId

GROUP BY PJ.PumpTaskId, PT.PumpTaskName) AS SP_FirstLastData

INNER JOIN dbo.PumpJobs AS FirstPumpJobs ON SP_FirstLastData.FirstPumpJobID = FirstPumpJobs.PumpJobId

INNER JOIN dbo.PumpJobs AS LastPumpJobs ON SP_FirstLastData.LastPumpJobID = LastPumpJobs.PumpJobId

The query will return a list of all the processes that have run (successful or otherwise) in your instance. For each process, you will get the first and last datetime it ran as well as the status of the first and last job. So you will have a high level 'history' of what ran - again though, it is based on your Job Log history so will only cover that overall period of content.

The following query will tell you, for all of your standard processes, which have no history (i.e. have no record of running in the period of time your logs cover) and are therefore potentially redundant.

SELECT 'SP' AS ProcessType
, [PT].[PumpTaskName] AS ProcessName
, [PT].[Description] AS ProcessDescription
FROM [dbo].[PumpTasks] [PT]
WHERE [PT].[PumpTaskId] NOT IN (SELECT DISTINCT PumpTaskID FROM [dbo].[PumpJobLogs])

The queries assume your database was built with the expected schemas etc - any issues might require some minor tweaking of same.

Hope this helps.

All comments

Rebecca_Cronin

Additional Details: Running on Monarch Server v.15

AnwarAli

Hi Michelle.

SELECT SP_FirstLastData.ProcessType

, SP_FirstLastData.ProcessID

, SP_FirstLastData.ProcessName

, SP_FirstLastData.FirstPumpJobID

, SP_FirstLastData.LastPumpJobID

, SP_FirstLastData.HistoricJobCount

, SP_FirstLastData.FirstProcessed

, SP_FirstLastData.LastProcessed

, FirstPumpJobs.Status AS FirstJobStatus

, FirstJobStatusText = CASE FirstPumpJobs.Status

WHEN 0 THEN 'ProcessResolvingWebAddresses'

WHEN 20 THEN 'RunningPreProcessScript'

WHEN 30 THEN 'ProcessReady'

WHEN 40 THEN 'ProcessAwaitingRetry'

WHEN 50 THEN 'ProcessFailed'

WHEN 60 THEN 'ProcessCancelled'

WHEN 70 THEN 'Ready'

WHEN 80 THEN 'Deferred'

WHEN 90 THEN 'Running'

WHEN 91 THEN 'ExportFailedWaitingRetry'

WHEN 100 THEN 'Completed'

WHEN 110 THEN 'Failed'

WHEN 120 THEN 'Cancelled'

WHEN 130 THEN 'DispatcherHostFailure'

WHEN 140 THEN 'Orphan'

ELSE 'Unknown Status'

END

, LastPumpJobs.Status AS LastJobStatus

, LastJobStatusText = CASE LastPumpJobs.Status

WHEN 0 THEN 'ProcessResolvingWebAddresses'

WHEN 20 THEN 'RunningPreProcessScript'

WHEN 30 THEN 'ProcessReady'

WHEN 40 THEN 'ProcessAwaitingRetry'

WHEN 50 THEN 'ProcessFailed'

WHEN 60 THEN 'ProcessCancelled'

WHEN 70 THEN 'Ready'

WHEN 80 THEN 'Deferred'

WHEN 90 THEN 'Running'

WHEN 91 THEN 'ExportFailedWaitingRetry'

WHEN 100 THEN 'Completed'

WHEN 110 THEN 'Failed'

WHEN 120 THEN 'Cancelled'

WHEN 130 THEN 'DispatcherHostFailure'

WHEN 140 THEN 'Orphan'

ELSE 'Unknown Status'

END

FROM ( SELECT 'SP' AS ProcessType

, PJ.PumpTaskId AS ProcessID

, PT.PumpTaskName AS ProcessName

, COUNT(*) AS HistoricJobCount

, MIN(PJ.PumpJobId) AS FirstPumpJobID

, MAX(PJ.PumpJobId) AS LastPumpJobID

, MIN(PJ.ProcessedTime) AS FirstProcessed

, MAX(PJ.ProcessedTime) AS LastProcessed

FROM dbo.PumpJobs AS PJ

LEFT OUTER JOIN dbo.PumpTasks AS PT ON PJ.PumpTaskId = PT.PumpTaskId

GROUP BY PJ.PumpTaskId, PT.PumpTaskName) AS SP_FirstLastData

INNER JOIN dbo.PumpJobs AS FirstPumpJobs ON SP_FirstLastData.FirstPumpJobID = FirstPumpJobs.PumpJobId

INNER JOIN dbo.PumpJobs AS LastPumpJobs ON SP_FirstLastData.LastPumpJobID = LastPumpJobs.PumpJobId

The queries assume your database was built with the expected schemas etc - any issues might require some minor tweaking of same.

Hope this helps.

Rebecca_Cronin

Hi Michelle.

The easiest way to get this information would be to query the Monarch Server database directly. It also depends on the Job Log history in your instance as this is the only way to determine what ran and when.

The following query should work for you, I am not sure what version of v15 youre using so tested it quickly on a 14.3 instance successfully;

SELECT SP_FirstLastData.ProcessType

, SP_FirstLastData.ProcessID

, SP_FirstLastData.ProcessName

, SP_FirstLastData.FirstPumpJobID

, SP_FirstLastData.LastPumpJobID

, SP_FirstLastData.HistoricJobCount

, SP_FirstLastData.FirstProcessed

, SP_FirstLastData.LastProcessed

, FirstPumpJobs.Status AS FirstJobStatus

, FirstJobStatusText = CASE FirstPumpJobs.Status

WHEN 0 THEN 'ProcessResolvingWebAddresses'

WHEN 20 THEN 'RunningPreProcessScript'

WHEN 30 THEN 'ProcessReady'

WHEN 40 THEN 'ProcessAwaitingRetry'

WHEN 50 THEN 'ProcessFailed'

WHEN 60 THEN 'ProcessCancelled'

WHEN 70 THEN 'Ready'

WHEN 80 THEN 'Deferred'

WHEN 90 THEN 'Running'

WHEN 91 THEN 'ExportFailedWaitingRetry'

WHEN 100 THEN 'Completed'

WHEN 110 THEN 'Failed'

WHEN 120 THEN 'Cancelled'

WHEN 130 THEN 'DispatcherHostFailure'

WHEN 140 THEN 'Orphan'

ELSE 'Unknown Status'

END

, LastPumpJobs.Status AS LastJobStatus

, LastJobStatusText = CASE LastPumpJobs.Status

WHEN 0 THEN 'ProcessResolvingWebAddresses'

WHEN 20 THEN 'RunningPreProcessScript'

WHEN 30 THEN 'ProcessReady'

WHEN 40 THEN 'ProcessAwaitingRetry'

WHEN 50 THEN 'ProcessFailed'

WHEN 60 THEN 'ProcessCancelled'

WHEN 70 THEN 'Ready'

WHEN 80 THEN 'Deferred'

WHEN 90 THEN 'Running'

WHEN 91 THEN 'ExportFailedWaitingRetry'

WHEN 100 THEN 'Completed'

WHEN 110 THEN 'Failed'

WHEN 120 THEN 'Cancelled'

WHEN 130 THEN 'DispatcherHostFailure'

WHEN 140 THEN 'Orphan'

ELSE 'Unknown Status'

END

FROM ( SELECT 'SP' AS ProcessType

, PJ.PumpTaskId AS ProcessID

, PT.PumpTaskName AS ProcessName

, COUNT(*) AS HistoricJobCount

, MIN(PJ.PumpJobId) AS FirstPumpJobID

, MAX(PJ.PumpJobId) AS LastPumpJobID

, MIN(PJ.ProcessedTime) AS FirstProcessed

, MAX(PJ.ProcessedTime) AS LastProcessed

FROM dbo.PumpJobs AS PJ

LEFT OUTER JOIN dbo.PumpTasks AS PT ON PJ.PumpTaskId = PT.PumpTaskId

GROUP BY PJ.PumpTaskId, PT.PumpTaskName) AS SP_FirstLastData

INNER JOIN dbo.PumpJobs AS FirstPumpJobs ON SP_FirstLastData.FirstPumpJobID = FirstPumpJobs.PumpJobId

INNER JOIN dbo.PumpJobs AS LastPumpJobs ON SP_FirstLastData.LastPumpJobID = LastPumpJobs.PumpJobId

The query will return a list of all the processes that have run (successful or otherwise) in your instance. For each process, you will get the first and last datetime it ran as well as the status of the first and last job. So you will have a high level 'history' of what ran - again though, it is based on your Job Log history so will only cover that overall period of content.

The following query will tell you, for all of your standard processes, which have no history (i.e. have no record of running in the period of time your logs cover) and are therefore potentially redundant.

SELECT 'SP' AS ProcessType
, [PT].[PumpTaskName] AS ProcessName
, [PT].[Description] AS ProcessDescription
FROM [dbo].[PumpTasks] [PT]
WHERE [PT].[PumpTaskId] NOT IN (SELECT DISTINCT PumpTaskID FROM [dbo].[PumpJobLogs])

The queries assume your database was built with the expected schemas etc - any issues might require some minor tweaking of same.

Hope this helps.

A.

Hi @Michele Adamski - did you have a chance to review the response shared by Anwar?

michele_adamski

Hi Michelle.

The easiest way to get this information would be to query the Monarch Server database directly. It also depends on the Job Log history in your instance as this is the only way to determine what ran and when.

The following query should work for you, I am not sure what version of v15 youre using so tested it quickly on a 14.3 instance successfully;

SELECT SP_FirstLastData.ProcessType

, SP_FirstLastData.ProcessID

, SP_FirstLastData.ProcessName

, SP_FirstLastData.FirstPumpJobID

, SP_FirstLastData.LastPumpJobID

, SP_FirstLastData.HistoricJobCount

, SP_FirstLastData.FirstProcessed

, SP_FirstLastData.LastProcessed

, FirstPumpJobs.Status AS FirstJobStatus

, FirstJobStatusText = CASE FirstPumpJobs.Status

WHEN 0 THEN 'ProcessResolvingWebAddresses'

WHEN 20 THEN 'RunningPreProcessScript'

WHEN 30 THEN 'ProcessReady'

WHEN 40 THEN 'ProcessAwaitingRetry'

WHEN 50 THEN 'ProcessFailed'

WHEN 60 THEN 'ProcessCancelled'

WHEN 70 THEN 'Ready'

WHEN 80 THEN 'Deferred'

WHEN 90 THEN 'Running'

WHEN 91 THEN 'ExportFailedWaitingRetry'

WHEN 100 THEN 'Completed'

WHEN 110 THEN 'Failed'

WHEN 120 THEN 'Cancelled'

WHEN 130 THEN 'DispatcherHostFailure'

WHEN 140 THEN 'Orphan'

ELSE 'Unknown Status'

END

, LastPumpJobs.Status AS LastJobStatus

, LastJobStatusText = CASE LastPumpJobs.Status

WHEN 0 THEN 'ProcessResolvingWebAddresses'

WHEN 20 THEN 'RunningPreProcessScript'

WHEN 30 THEN 'ProcessReady'

WHEN 40 THEN 'ProcessAwaitingRetry'

WHEN 50 THEN 'ProcessFailed'

WHEN 60 THEN 'ProcessCancelled'

WHEN 70 THEN 'Ready'

WHEN 80 THEN 'Deferred'

WHEN 90 THEN 'Running'

WHEN 91 THEN 'ExportFailedWaitingRetry'

WHEN 100 THEN 'Completed'

WHEN 110 THEN 'Failed'

WHEN 120 THEN 'Cancelled'

WHEN 130 THEN 'DispatcherHostFailure'

WHEN 140 THEN 'Orphan'

ELSE 'Unknown Status'

END

FROM ( SELECT 'SP' AS ProcessType

, PJ.PumpTaskId AS ProcessID

, PT.PumpTaskName AS ProcessName

, COUNT(*) AS HistoricJobCount

, MIN(PJ.PumpJobId) AS FirstPumpJobID

, MAX(PJ.PumpJobId) AS LastPumpJobID

, MIN(PJ.ProcessedTime) AS FirstProcessed

, MAX(PJ.ProcessedTime) AS LastProcessed

FROM dbo.PumpJobs AS PJ

LEFT OUTER JOIN dbo.PumpTasks AS PT ON PJ.PumpTaskId = PT.PumpTaskId

GROUP BY PJ.PumpTaskId, PT.PumpTaskName) AS SP_FirstLastData

INNER JOIN dbo.PumpJobs AS FirstPumpJobs ON SP_FirstLastData.FirstPumpJobID = FirstPumpJobs.PumpJobId

INNER JOIN dbo.PumpJobs AS LastPumpJobs ON SP_FirstLastData.LastPumpJobID = LastPumpJobs.PumpJobId

The query will return a list of all the processes that have run (successful or otherwise) in your instance. For each process, you will get the first and last datetime it ran as well as the status of the first and last job. So you will have a high level 'history' of what ran - again though, it is based on your Job Log history so will only cover that overall period of content.

The following query will tell you, for all of your standard processes, which have no history (i.e. have no record of running in the period of time your logs cover) and are therefore potentially redundant.

SELECT 'SP' AS ProcessType
, [PT].[PumpTaskName] AS ProcessName
, [PT].[Description] AS ProcessDescription
FROM [dbo].[PumpTasks] [PT]
WHERE [PT].[PumpTaskId] NOT IN (SELECT DISTINCT PumpTaskID FROM [dbo].[PumpJobLogs])

The queries assume your database was built with the expected schemas etc - any issues might require some minor tweaking of same.

Hope this helps.

A.

Anwar,

This was exactly what I needed. Thank you so much for your help!

One thing - my job log disposition is set to completed=366, failed=91, cancelled=91. Using the queries you shared, I should capture any reports that have no history in the past year. Is that correct?