I am always having to modify the standard queries in HAT, so I finally broke down and created a new query in HAT.
This shows me the last 100 orchestrations run.
SELECT top 100
dateadd(minute, @UtcOffsetMin, [ServiceInstance/StartTime]) as [StartTime], — can’t use ‘as [ServiceInstance/StartTime]‘ since this prevents SQL from using index on that column (conflicts with ORDER BY)
dateadd(minute, @UtcOffsetMin, [ServiceInstance/EndTime]) as [EndTime], — can’t use ‘as [ServiceInstance/EndTime]‘ since this prevents SQL from using index on that column (conflicts with ORDER BY)
FROM dbo.dtav_ServiceFacts sf WITH (READPAST)
where [Service/Type] like ‘%Orchestration%’
ORDER BY sf.[ServiceInstance/StartTime] desc
The only other thing that you need to do is near the top of the trq file, you need to replace the title with:
<anyType _locID=”1″ xsi:type=”xsd:string”>Most recent 100 Orchestrations</anyType>
Save the file back in the Tracking folder as Top100Orchestrations.trq, restart HAT, and you are good to go.