none
queries about runbooks RRS feed

  • Question

  • Is there a query to find out runbooks that not running?

    And a query to find out runbook orphaned instance?

    Thanks


    Thanks

    Thursday, December 5, 2019 1:07 AM

Answers

  • Hi,

    There is a SQL query to find all running runbooks, you can modify it to show the opposite, that is all runbooks that do not have a running status.

    Find running runbooks:

    SELECT
    J.[RunbookId],
    P.[Name],
    A.[Computer]
    FROM
    [Orchestrator].[Microsoft.SystemCenter.Orchestrator.Runtime.Internal].[Jobs] J
    INNER JOIN [dbo].POLICIES P ON J.RunbookId = P.UniqueID
    INNER JOIN [dbo].[ACTIONSERVERS] A ON J.RunbookServerId = A.UniqueID
    WHERE
    J.[StatusId] = 1
    ORDER BY
    P.[Name] DESC


    Find runbooks that do not have the status running:

    SELECT
    J.[RunbookId],
    P.[Name],
    A.[Computer]
    FROM
    [Orchestrator].[Microsoft.SystemCenter.Orchestrator.Runtime.Internal].[Jobs] J
    INNER JOIN [dbo].POLICIES P ON J.RunbookId = P.UniqueID
    INNER JOIN [dbo].[ACTIONSERVERS] A ON J.RunbookServerId = A.UniqueID
    WHERE
    J.[StatusId] != 1
    ORDER BY
    P.[Name] DESC

    Runbook status IDs in Orchestrator:

    You can find orphaned runbook instances by using the Orchestrator Health Checker tool, here's an example of how orphaned runbook instances look like:


    Best regards,
    Leon


    Blog: https://thesystemcenterblog.com LinkedIn:

    • Proposed as answer by Andreas BaumgartenMVP Thursday, December 5, 2019 6:46 PM
    • Marked as answer by msloy Friday, December 6, 2019 2:41 AM
    Thursday, December 5, 2019 9:44 AM
  • Hi,

    can you please check those:

    "Check runbook job status" from
    Orchestrator SQL Database Queries

    Can you please specify what do you exactly mean with "orpahned"?

    Regards,


    (Please take a moment to "Vote as Helpful" and/or "Mark as Answer" where applicable. This helps the community, keeps the forums tidy, and recognizes useful contributions. Thanks!) Blog: https://blog.pohn.ch/ Twitter: @StoyanChalakov

    • Proposed as answer by Andreas BaumgartenMVP Thursday, December 5, 2019 6:46 PM
    • Marked as answer by msloy Friday, December 6, 2019 2:52 AM
    Thursday, December 5, 2019 9:59 AM
  • Hi,

    Found a document which seemed helpful for our question, please refer to the link below.

    DB Query To Stop Running Runbooks in SCORCH and Clear Their Orphans

    https://social.technet.microsoft.com/wiki/contents/articles/43466.db-query-to-stop-running-runbooks-in-scorch-and-clear-their-orphans.aspx

    Check and remove orphaned Runbooks


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Friday, December 6, 2019 2:23 AM
    Moderator
  • I meant to mark both columns, but yes ”normally” the running instances column can be used to identify orphaned instances.
    Do note though that runbooks can run multiple times simultaneously (if configured so), then they will also show multiple running instances.

    Orphaned instances usually happen from ”Monitor runbooks”.


    Blog: https://thesystemcenterblog.com LinkedIn:


    • Edited by Leon Laude Friday, December 6, 2019 2:59 AM
    • Marked as answer by msloy Friday, December 6, 2019 2:59 AM
    Friday, December 6, 2019 2:56 AM

All replies

  • Hi,

    There is a SQL query to find all running runbooks, you can modify it to show the opposite, that is all runbooks that do not have a running status.

    Find running runbooks:

    SELECT
    J.[RunbookId],
    P.[Name],
    A.[Computer]
    FROM
    [Orchestrator].[Microsoft.SystemCenter.Orchestrator.Runtime.Internal].[Jobs] J
    INNER JOIN [dbo].POLICIES P ON J.RunbookId = P.UniqueID
    INNER JOIN [dbo].[ACTIONSERVERS] A ON J.RunbookServerId = A.UniqueID
    WHERE
    J.[StatusId] = 1
    ORDER BY
    P.[Name] DESC


    Find runbooks that do not have the status running:

    SELECT
    J.[RunbookId],
    P.[Name],
    A.[Computer]
    FROM
    [Orchestrator].[Microsoft.SystemCenter.Orchestrator.Runtime.Internal].[Jobs] J
    INNER JOIN [dbo].POLICIES P ON J.RunbookId = P.UniqueID
    INNER JOIN [dbo].[ACTIONSERVERS] A ON J.RunbookServerId = A.UniqueID
    WHERE
    J.[StatusId] != 1
    ORDER BY
    P.[Name] DESC

    Runbook status IDs in Orchestrator:

    You can find orphaned runbook instances by using the Orchestrator Health Checker tool, here's an example of how orphaned runbook instances look like:


    Best regards,
    Leon


    Blog: https://thesystemcenterblog.com LinkedIn:

    • Proposed as answer by Andreas BaumgartenMVP Thursday, December 5, 2019 6:46 PM
    • Marked as answer by msloy Friday, December 6, 2019 2:41 AM
    Thursday, December 5, 2019 9:44 AM
  • Hi,

    can you please check those:

    "Check runbook job status" from
    Orchestrator SQL Database Queries

    Can you please specify what do you exactly mean with "orpahned"?

    Regards,


    (Please take a moment to "Vote as Helpful" and/or "Mark as Answer" where applicable. This helps the community, keeps the forums tidy, and recognizes useful contributions. Thanks!) Blog: https://blog.pohn.ch/ Twitter: @StoyanChalakov

    • Proposed as answer by Andreas BaumgartenMVP Thursday, December 5, 2019 6:46 PM
    • Marked as answer by msloy Friday, December 6, 2019 2:52 AM
    Thursday, December 5, 2019 9:59 AM
  • Hi,

    Found a document which seemed helpful for our question, please refer to the link below.

    DB Query To Stop Running Runbooks in SCORCH and Clear Their Orphans

    https://social.technet.microsoft.com/wiki/contents/articles/43466.db-query-to-stop-running-runbooks-in-scorch-and-clear-their-orphans.aspx

    Check and remove orphaned Runbooks


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Friday, December 6, 2019 2:23 AM
    Moderator
  • Thank, why you circled the red column? I thought the running instances Column if older than 1, means it may have orphaned instance.

    Thanks

    Friday, December 6, 2019 2:51 AM
  • I meant to mark both columns, but yes ”normally” the running instances column can be used to identify orphaned instances.
    Do note though that runbooks can run multiple times simultaneously (if configured so), then they will also show multiple running instances.

    Orphaned instances usually happen from ”Monitor runbooks”.


    Blog: https://thesystemcenterblog.com LinkedIn:


    • Edited by Leon Laude Friday, December 6, 2019 2:59 AM
    • Marked as answer by msloy Friday, December 6, 2019 2:59 AM
    Friday, December 6, 2019 2:56 AM