cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 9

Where is "Multiple Statistics Data" historical data in SWQL?

Jump to solution

Hello,

I have a powershell component setup that pulls the last modified time for a file and stores as a variable where I can see it in the console.

2020_10_07_10_21_44_Microsoft_Edge.png

I need to pull historical data of what this value has been over the last 24 hours. 

I've looked in Orion.APM.StatisticsUsage, but it's not there. I've looked in APM.ComponentStatus, but that's not the info I need. I can see the current value in "Orion.APM.MultipleStatisticsData", but I need the historical data.

2020_10_07_10_29_28_SWQL_Studio.png

Where in the SWQL database is this data?

Thanks in advance..

 

0 Kudos
1 Solution

I dug through my pile of old SWQL queries and came across this one that looks like it should help.

 

 

SELECT top 100 tolocal(TimeStamp) as Timestamp
, cs.Component.Application.Node.Caption
, cs.Component.Application.Name
, 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.Application.Name like '%zxy%'
--and cs.Component.Application.Node.Caption='abc'
--and cs.DynamicEvidence.ColumnLabel = '123'

- Marc Netterfield, Github

View solution in original post

9 Replies

Probably just a left join on componentid=componentid and datetime=datetime, but it's friday and I am about done for the week so I can't muster the motivation to test it just now.

- Marc Netterfield, Github
0 Kudos

Can you elaborate?  There is an implicit join between Orion.APM.ComponentStatus and Orion.APM.DynamicEvidence.  Why do you need to do an explicit join to get a component statistic result in the same row and the component message?

0 Kudos

I dug through my pile of old SWQL queries and came across this one that looks like it should help.

 

 

SELECT top 100 tolocal(TimeStamp) as Timestamp
, cs.Component.Application.Node.Caption
, cs.Component.Application.Name
, 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.Application.Name like '%zxy%'
--and cs.Component.Application.Node.Caption='abc'
--and cs.DynamicEvidence.ColumnLabel = '123'

- Marc Netterfield, Github

View solution in original post

@mesverrum , Is there any SWQL query magic to get the statistic result and the message result from the same poll time to appear in the same row?  The dynamic evidence table stores the statistic and message for that statistic in 2 different rows in the database.

0 Kudos

Like the lady on the Calgon soap commercials used to say.. "YES! YES! YES!" 🙂 

Thank you for that, it's exactly what I needed.  Putting in my criteria gave the results I was looking for.

results.png

Thank you soooo much for this Marc!

0 Kudos
Level 13

Try taking a look at the Orion.APM.DynamicEvidence table. You can tie this back to Orion.APM.CurrentComponentStatus and from there to Orion.APM.Component or Orion.APM.Application. Note the DynamicEvidence table keeps the statistics and messages as 2 separate rows where the NumericData columns of one will be null and the StringData column of the other is null.

 

SELECT ComponentID, ApplicationID, Availability, TimeStamp, ComponentStatusID, LastTimeUp, CS.ErrorCode, CS.ErrorMessage, PercentCPU, PercentMemory, PercentVirtualMemory, IsFallbackUsed, PrimaryPollingProtocol,
ComponentStatusID, ColumnSchemaID, RowNumber, ColumnType, ColumnDisabled, ColumnName, ColumnLabel, ColumnThresholdWarning, ColumnThresholdCritical, ColumnThresholdOperator, MinNumericData, AvgNumericData, MaxNumericData, StringData, ErrorCode, OSErrorCode, StatusCode, StatusCodeType, ErrorMessage, RecordCount, Archive
FROM Orion.APM.CurrentComponentStatus CS
JOIN Orion.APM.DynamicEvidence DE ON DE.ComponentStatusID = CS.ComponentStatusID
ORDER BY TimeStamp

Preciate that info.  When I compare the values from swql, I removed a few columns from the output and added a "where" for the componentID...

swql.png

.. against the component monitor it pulls the current value..

com.png

I also waited until the updated time of the file has changed and then searched for StringData = '10/08/2020 07:18:10' and it didn't find it.

newstring.png

 

 

0 Kudos
Level 12

I was trying to do this too a few weeks ago and basically I found this thread.  Basically it says create an alert action that generates a message in the SolarWinds event log table if you want to store SAM component messages historically.  I think its pretty ghetto given the sophistication of the product but thats all I found.  Sorry!  If you come up with something better let me know! 

 

 

https://thwack.solarwinds.com/t5/SAM-Discussions/SAM-How-to-show-last-x-number-of-statistic-messages...

Hello,

Thank you for replying with this info.  I saw that post earlier when I was searching before I posted, and thought it was pretty good for the time it was posted back in 2014.  I would think that information is already stored in the database somewhere the current value is available.

If nothing is available I will have to use that option, but I would think *hope* that that data's already being stored somewhere. 🙂

 

'Preciate it.

0 Kudos