Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials

For Powershell monitor, where is "Multiple Statistics Data" historical data in SWQL?

Hi,

My question is related to the following question from the forum that is now closed, hence this new question. Here is the link to the original:

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

I have a brand new SAM install I built last week, so everything is latest and greatest (2023.2).

One of the solutions in that link provided by @mesverrum and working for @cmartiis (with slight edits by myself):

SELECT
    tolocal(Timestamp) as Timestamp,
    cs.Component.ApplicationID,
    cs.Component.Application.Node.dns as Hostname,
    cs.Component.Application.Name as ApplicationName,
    cs.Component.Name as component,
    cs.ComponentID,
    cs.DynamicEvidence.ColumnLabel as Label,
    cs.DynamicEvidence.StringData as StringData,
    cs.DynamicEvidence.AvgNumericData
FROM Orion.APM.ComponentStatus cs
order by timestamp desc

There is over 15,000 records that covers 5 days. AvgNumericData contains values that make sense. That all makes sense. My issue is StringData is always NULL which is not the expected result.

Am I missing something here? Is my expectation that StringData should contain something other than NULL not correct?

If you have any applications with Windows Powershell Monitor components running, can you run the above query in SWQL Studio and report the contents of StringData?

TIA :-)

Find more posts tagged with

SWQL

solarwinds sam

Accepted answers

robert777

Greetings.

FYI I opened a ticket with Solarwinds support. After some dialog and clarification the support agent confirmed that the reported condition is in fact a bug in the program.

The agent has escalated my ticket to Dev in an effort to get more details / bug report / estimated fix date for the issue.

It appears that, starting with version 2023.2 or so, if you are capturing string metrics in a script monitor using the 'Message' construct, then that data does not get captured to the database.

Btw I checked table Orion.APM.DynamicEvidenceChart table and it does not contain any Message (string) data from script monitors.

Complete agreement that we should always use SWQL Studio whenever possible rather than SQL tables.

I will post new information as it becomes available. Anyone in the community feel free to question or comment.

Cheers.

All comments

ecmel

Hi,

For the latest data, you can use "Orion.APM.MultipleStatisticData" to pull the string value with StringData column.

For historical data, I guess there is a problem in the latest release. I can see string data in "Orion.APM.DynamicEvidence" table for the rows having ColumnType value set to "0" in an installation running Orion version 2020.2.6. Unfortunately I can't see any row having ColumnType value set to "0" in another environment which is running on 2023.2.1.

In addition to this, I see this note in SWQL Studio for "Orion.APM.DynamicEvidence" object: Orion.APM.DynamicEvidence will be removed in a future version of the product. Please use Orion.APM.DynamicEvidenceChart

I also checked Orion.APM.DynamicEvidenceChart but it does not have a StringData column.

In my opinion, there is a bug in the new release but I would suggest raising a ticket to SolarWinds to verify it and ask for a fix.

bucweat

Thanks for the reply!!! This is very helpful. I already have a ticket in but all support has done is verify they are seeing the same result as I am.

BTW if you go looking in the database directly you'll see the same issue with 2023.2, so I don't think this is an issue with SWQL as it is pulling what is in the database. I think the issue is that the stringdata column is not correctly populated in 2023.2.

Where exactly did you find "Orion.APM.DynamicEvidence will be removed in a future version of the product. Please use Orion.APM.DynamicEvidenceChart"? I can't find that in the github repo and my google-fu isn't finding anything like that. Can you provide a link if one is available?

Yeah I also looked at Orion.APM.DynamicEvidenceChart and came to the same conclusion...

ecmel

I saw that message in SWQL Studio:

If you haven't used SWQL Studio before, I would strongly recommend it as you can easily create and test your queries or other API actions. It is coming as part of Orion SDK. I'm sharing several links below:

https://support.solarwinds.com/SuccessCenter/s/article/Download-the-SolarWinds-Orion-SDK?language=en_US

https://thwack.solarwinds.com/resources/b/product-blog/posts/intro-to-swql-studio

bucweat

I have been using SWQL Studio but apparently not looking hard enough :-P I see it now.

This explains why Orion.APM.DynamicEvidence is displayed with strikethru in the tree...and if you uncheck Preferences > Show Obsolete then it doesn't show up in the tree at all. Since I could not find the string "DynamicEvidence" in the source code the documentation must be coming from the database itself.

So Orion.APM.DynamicEvidence is obsolete and going away...frustrating...maybe the lack of updating the stringdata is thte first step...

robert777

Hi All.

Let me chime in on this thread.

My shop makes regular use of script monitors. We pull string and numeric data back into Solarwinds using the Message: and Statistic: constructs. This data gets (got) stored in the Orion.APM.DynamicEvidence table in the StringData field.

We've created consoles for our user community which display the data collected. Message (string) data retrieved with SWQL queries to APM.DynamicEvidence.

Last week we upgraded Solarwinds from 2022.x to 2023.3 - and our data widgets stopped working for Message data.

My best guess at this point about what happened...

Note this section from the 2023.2 Release Notes:

--------------------------------------------------------------

Improved performance of APM DynamicEvidence

As part of performance improvements, the APM_DynamicEvidence_DetailData table has been removed. The new APM_DynamicEvidence_Current table contains the last value for both numeric and string values including the row and schema id. As a result of this change, scripts referencing the APM_DynamicEvidence_DetailData table will no longer work.

--------------------------------------------------------------

Notice the release notes reference changes to the APM_DynamicEvidence_DetailData table. However... the APM_DynamicEvidence table also has a line through it in SWQL Studio with a note about table has been depreciated. I did not find any Message string data in the APM_DynamicEvidence_Current table.

Most likely there was some kind of disconnect in the halls of Solarwinds development where some functionality of the APM_DynamicEvidence table was removed (by mistake?) but not incorporated into other tables.

I have a ticket in with Solarwinds Support to verify the bug, or at least find out where SW now stores Message data from script monitors. I will update this thread when I hear something. Cheers.

KMSigma.SWI

@robert777 - I'm running 2023.4 (currently in RC) and this is what the API is providing me via SWQL Studio:

Orion.APM.DynamicEvidenceDetailData

Orion.APM.DynamicEvidence

From the casual observer (me), it looks like you should stop using Orion.APM.DynamicEvidenceDetailData and instead be using Orion.APM.DynamicEvidenceCurrent and move from Orion.APM.DynamicEvidence to Orion.APM.DynamicEvidenceChart.

Note: The above are all from the API (SWQL) and not the database. You should be using the API as much as possible to insulate you from any underlying database changes.

robert777

Greetings.

FYI I opened a ticket with Solarwinds support. After some dialog and clarification the support agent confirmed that the reported condition is in fact a bug in the program.

The agent has escalated my ticket to Dev in an effort to get more details / bug report / estimated fix date for the issue.

It appears that, starting with version 2023.2 or so, if you are capturing string metrics in a script monitor using the 'Message' construct, then that data does not get captured to the database.

Btw I checked table Orion.APM.DynamicEvidenceChart table and it does not contain any Message (string) data from script monitors.

Complete agreement that we should always use SWQL Studio whenever possible rather than SQL tables.

I will post new information as it becomes available. Anyone in the community feel free to question or comment.

Cheers.

bucweat

Hi,

For the ticket I submitted 01368039 all I got was when I asked about null in StringData was "I do not know if it is correct or not, but I get the same results in my lab, so two out of two may be right. Unfortunately, SWQL is out of our scope of support and you will need to go to thwack.com if you have questions about SWQL." :-( That is what led to the original post above...

I would love to have a table with timestamp, StringData and NumericData (or Message and Statistic data). Looking forward to hearing what you hear...

robert777

The support agent now has asked for a complete diagnostic upload. In my environment we have to 'sanitize' anything sent to a vendor which basically means change all of the host names and IP addresses.

I need to ask around my shop to see if we have a script or something to sanitize log/diagnostic output, or if I need to write something myself.

I'll keep everyone posted as to results.

tvminser

Hello Robert!

I hate to ask 'Captain Obvious" questions, but I find if they aren't asked, the answers tend to hide there the longest.

So my question is, are your PowerShell monitors actually returning String\Message values when you test them in the Solarwinds Application Monitor? I only ask because it was mentioned that there doesn't seem to be any StringData populating into your database tables.

We recently had the same issue where updating our Solarwinds platform from 2022.X to 2023.X caused a few monitors to break and we had to craft some new PowerShell scripts that return both a Message and a Statistic value. What we found was that there was two tables where the results were being stored; the Orion.APM.DynamicEvidenceCurrent and Orion.APM.MultipleStatisticData. The Orion.APM.MultipleStatisticData tables stores both values in the same row, however, it does not have a TimeStamp column. The Orion.APM.DynamicEvidenceCurrent does have a TimeStamp column, but it stores the Message and Statistic values on separate rows. One row would have either a NumericData value and 'NULL' for StringData; or it would have a StringData value, and a 'NULL' NumericData value.

robert777

Greetings.

Thanks for your reply. I appreciate all of the input from the community.

Regarding your question, the issue for which we opened a ticket with solarwinds support involves shell script monitors (bash) on Linux servers, not powershell monitors (note that I have another thread open for a powershell script monitor that relates to powershell versions and certificate chain of trust between management server and monitored endpoint).

Regarding the issue at hand, yes it seems that in 2023.x 'Message' (string) data from script monitors has been moved from APM.DynamicEvidence to APM.DynamicEvidenceCurrent. Caveat: DynamicEvidenceCurrent only stores the latest data. If your use case requires historical Message data I'm not sure if that gets stored anywhere. As of the latest release Statistic (numeric) data still gets stored in DynamicEvidence (current and historical) and also DynamicEvidenceCurrent (latest only).

Concur that in DynamicEvidence and DynamicEvidenceCurrent, string and numeric data are stored on different lines. So, to get string data we use (pseudocode, not strict SQL/SWQL)...

SELECT d.StringData

FROM Orion.APM.DynamicEvidenceCurrent AS d

WHERE (c.Name LIKE 'NameOfComponentMonitor') AND (d.NumericData IS NULL)

note: in the example 'c' is aliased to APM.Component

To return numeric data something a little different:

SELECT d.NumericData

FROM Orion.APM.DynamicEvidenceCurrent AS d

WHERE (c.Name LIKE 'NameOfComponentMonitor') AND (d.NumericData IS NOT NULL)

Note that DynamicEvidenceCurrent stores only the latest data. I'm not sure what the long-term plan is for storing historical data from script monitors.

Any community member feel free to question, comment or correct as needed. Cheers.

bucweat

Hi all,

Everyones comments seem to line up with what I've seen. I started using Solarwinds with 2023.X so never seen working history data here...

Here is a working query I use (sorry for the formatting I don't see how to format code in this forum wish they used markdown). Note the join of Orion.APM.DynamicEvidenceCurrent to itself to combine NumericData and StringData. This is only for current data.

SELECT
dec1.ComponentID,
dec1.server,
n.NodeName as client,
c.Application.Name as app,
si.ShortDescription as node_status,
c.Application.StatusDescription as app_status,
c.StatusDescription as cmpnt_status,
c.Application.CurrentStatus.LastSuccessfulPoll as LSP,
secondDiff(c.Application.CurrentStatus.LastSuccessfulPoll, GETUTCDATE()) as LSP_s,
secondDiff(c.Application.LastModified, GETUTCDATE()) as LM_s,
alert.LastTimeUp as LastTimeUp,
alert.IsActiveAlert,
dec1.Label,
dec1.NumericData,
dec2.StringData
FROM (
select
dec.OrionSite.Host as server,
decs.Label,
dec.ComponentID,
dec.ColumnSchemaID,
dec.TimeStamp,
dec.NumericData
from Orion.APM.DynamicEvidenceCurrent dec
join orion.apm.DynamicEvidenceColumnSchema decs on dec.ColumnSchemaID = decs.ColumnSchemaID
where dec.NumericData is not null
order by columnschemaid
) dec1
join (
select
dec.OrionSite.Host as server,
decs.Label,
dec.ComponentID,
dec.ColumnSchemaID,
dec.TimeStamp,
dec.StringData
from Orion.APM.DynamicEvidenceCurrent dec
join orion.apm.DynamicEvidenceColumnSchema decs on dec.ColumnSchemaID = decs.ColumnSchemaID
where dec.stringdata is not null
order by columnschemaid
) dec2
on dec1.ComponentID = dec2.ComponentID and dec1.ColumnSchemaID = dec2.ColumnSchemaID+1
JOIN orion.apm.Component c on c.ComponentID = dec1.ComponentID
JOIN Orion.Nodes n on n.NodeID = c.Application.NodeID
JOIN Orion.StatusInfo si on n.Status = si.StatusId
left join (
SELECT
a.Node.DNS,
ca.ApplicationID,
a.Name as AppName,
ca.Componentid as Componentid,
ca.ComponentName as CompName,
ca.StatusOrErrorDescription,
ca.Component.Status as status,
case when ca.Component.Status = 1 then false else true end as IsActiveAlert,
ca.LastTimeUp
FROM Orion.APM.ComponentAlert ca
join orion.apm.Application a on a.ApplicationID = ca.ApplicationID
where ca.componentname like 'msi_%'
) alert
on alert.Componentid = dec1.ComponentID
where 1=1

EDIT: thanks to @KMSigma.SWI for the code formatting...not sure that option was available when I originally submitted this as I looked for it...for those looking for it check out the insert menu. Also, I removed the last line of my query (the where part) as that was specific to my situation.