Windows PowerShell Monitor - Output Message History

This is a custom query widget I use for that kind of scenario, 

SELECT max(pe.ComponentStatus.timestamp) as Latest, pe.ErrorMessage as Message
FROM Orion.APM.PortEvidence pe
where pe.ComponentStatus.ComponentID = ${componentid}
group by pe.ErrorMessage

order by Latest desc




SELECT pe.ComponentStatus.timestamp as Timestamps, pe.ErrorMessage as Message
FROM Orion.APM.PortEvidence pe
where pe.ComponentStatus.ComponentID = ${componentid}
and pe.errormessage like '%${SEARCH_STRING}%'
order by Timestamps desc

I just confirmed and that query works for messages from SQL monitors, but not for messages in powershell scripts (why would we use the same tables for similar data right?).  So this version does a union between the two tables where these kind of messages might live, depending on the type of components we generate stats and messages from

SELECT max(TimeStamp) as LatestTimestamp 
, cs.Component.Name 
, cs.Component.DetailsUrl as [_linkfor_Name] 
, cs.DynamicEvidence.ColumnLabel as Label 
, cs.DynamicEvidence.StringData as Message 
 
FROM Orion.APM.ComponentStatus cs 
 
where cs.DynamicEvidence.StringData is not null 
and cs.Component.ComponentID = ${componentid}
--and cs.DynamicEvidence.StringData like '%${SEARCH_STRING}%'

group by cs.Component.Name , cs.Component.DetailsUrl, cs.DynamicEvidence.ColumnLabel, cs.DynamicEvidence.StringData

union all (
SELECT max(pe.ComponentStatus.timestamp) as LatestTimestamp
, pe.ComponentStatus.component.Name
, pe.ComponentStatus.Component.DetailsUrl as [_linkfor_Name] 
, '' as Label
, pe.ErrorMessage as Message
FROM Orion.APM.PortEvidence pe
where pe.ComponentStatus.ComponentID = ${componentid}
--and pe.ErrorMessage like '%${SEARCH_STRING}%'

group by pe.ErrorMessage, pe.ComponentStatus.component.Name, pe.ComponentStatus.Component.DetailsUrl
)
 
order by LatestTimestamp desc

You might or might not want to do the max() part, in my case I only am interested in unique values for the messages and how recently they came in, but if you just want every message you can ditch the group by and the max() stuff

I just confirmed and that query works for messages from SQL monitors, but not for messages in powershell scripts (why would we use the same tables for similar data right?).  So this version does a union between the two tables where these kind of messages might live, depending on the type of components we generate stats and messages from

SELECT max(TimeStamp) as LatestTimestamp 
, cs.Component.Name 
, cs.Component.DetailsUrl as [_linkfor_Name] 
, cs.DynamicEvidence.ColumnLabel as Label 
, cs.DynamicEvidence.StringData as Message 
 
FROM Orion.APM.ComponentStatus cs 
 
where cs.DynamicEvidence.StringData is not null 
and cs.Component.ComponentID = ${componentid}
--and cs.DynamicEvidence.StringData like '%${SEARCH_STRING}%'

group by cs.Component.Name , cs.Component.DetailsUrl, cs.DynamicEvidence.ColumnLabel, cs.DynamicEvidence.StringData

union all (
SELECT max(pe.ComponentStatus.timestamp) as LatestTimestamp
, pe.ComponentStatus.component.Name
, pe.ComponentStatus.Component.DetailsUrl as [_linkfor_Name] 
, '' as Label
, pe.ErrorMessage as Message
FROM Orion.APM.PortEvidence pe
where pe.ComponentStatus.ComponentID = ${componentid}
--and pe.ErrorMessage like '%${SEARCH_STRING}%'

group by pe.ErrorMessage, pe.ComponentStatus.component.Name, pe.ComponentStatus.Component.DetailsUrl
)
 
order by LatestTimestamp desc

You might or might not want to do the max() part, in my case I only am interested in unique values for the messages and how recently they came in, but if you just want every message you can ditch the group by and the max() stuff

The query seems to be missing the message during a down condition. Here's what I mean:

The component went down at 8:43 PM on Sunday. The query shows this:

(This assumes UTC - we're UTC -7 - though I did also check if it was toLocal and it's same results). I was expecting to see something like this:

Click more (bottom right)

Turns out, the 'down' condition in my code prevents the statistic value and message from being recorded (not my favorite behavior), but that means I'll just need to update the code to exit critical when errors are encountered and I'll treat, for this alert, critical as down. That said, this query was great. Thank you!