Windows PowerShell Monitor - Output Message History

Question

When we create a Windows PowerShell Monitor, we can output the Message and Statistic data. In the database, we can see the current message data: 
 Orion.APM.MultipleStatisticData (StringData field) 
 Is there a way to see the historical value for those? I had a component go down over the weekend and I'm looking to investigate why, but the why of it would be in the message output, not in the statistic (since it's string not integer).

mesverrum · Accepted Answer

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

Windows PowerShell Monitor - Output Message History

Top Replies