System Center Orchestrator–SQL Query detailed Logs and behind the Scene

In some cases it can happen that the Log Purge is not working anymore, or not working correctly. In a real bad situation it can happen, that the daily Log Purge will make more troubles, maybe stops your Runbooks from working.

Than you will see a lot of orphaned Runbooks, and in the most cases this can happen when the extended Logging is activated at to many Runbooks. I always see customer, which are using the extended Logging at each Runbook and for ever.

Extended Logging should be used with care and only for testing and Dev Phase, not in production and not persistent.

Get Runbooks with active extended Logging

To get a List of Runbook with active, extended logging, run he following Query.

use Orchestrator 

select Name, LastModified, Enabled  from POLICIES where LogSpecificData = 1 or LogCommonData = 1

See Runbooks, which are doing a lot of Log Entries

It can also be very helpful to see a List of Runbooks, which are producing a lot of Log Entries. To get this List run the following Query.

use Orchestrator 

Select count(instdata.UniqueID) as entries, obj.[Name] as [Action], pol.[Name] as [policy]
From Orchestrator.dbo.OBJECTINSTANCEDATA as instdata
join Orchestrator.dbo.OBJECTINSTANCES as inst on instdata.ObjectInstanceID = inst.UniqueID
join [Orchestrator].[dbo].[OBJECTS] as obj on inst.ObjectID = Obj.UniqueID
join [orchestrator].[dbo].[POLICIES] as pol on pol.UniqueID = obj.ParentID
group by obj.[Name],pol.[Name]
order by entries desc

The result should look like this, and you should check the Runbooks with the most entries.

See Activities, which are executed the most

Now it can be interesting to see a List of Activitis and the executing Count, to get this list, run the following Query

use Orchestrator 

Select Count(ObjectID) as entries, ObjectID, obj.[Name] as [Action], pol.[Name] as [policy]
FROM Orchestrator.dbo.OBJECTINSTANCES as inst
join [Orchestrator].[dbo].[OBJECTS] as obj on inst.ObjectID = Obj.UniqueID
join [orchestrator].[dbo].[POLICIES] as pol on pol.UniqueID = obj.ParentID
group by obj.[Name],pol.[Name], OBJECTID
order by entries desc

The result should look like this, take a look at the most top entries.

So use the mentioned Queries to check your Runbook executions.

Michael Seidl aka Techguy