Last used?

michele_adamski
michele_adamski Altair Community Member
edited October 14 in Community Q&A

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! 

Tagged:

Best Answer

  • Anwar Ali_20997
    Anwar Ali_20997
    Altair Employee
    edited October 14 Answer ✓

    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.
     
     

Answers

  • Rebecca_Cronin
    Rebecca_Cronin
    Altair Employee
    edited October 10

    Additional Details:  Running on Monarch Server v.15

  • Anwar Ali_20997
    Anwar Ali_20997
    Altair Employee
    edited October 14 Answer ✓

    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.
     
     
  • Rebecca_Cronin
    Rebecca_Cronin
    Altair Employee
    edited October 14
    Anwar Ali_20997 said:

    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
    michele_adamski Altair Community Member
    edited October 14
    Anwar Ali_20997 said:

    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? 

Categories