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:

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 @cmarti is (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 :-)





Parents
  • 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.

  •    - 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.

  • 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  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.

  • We are currently on solarwinds version 4.2. Even though the statistical data of the Powershell component gives a successful output, it shows N/A and does not return the data. The Orion.APM.DynamicEvidenceCurrent table is empty. What can I do? Can anyone help?

  • Good day nilayvekediler.

    The community may be able to offer some assistance.  However, a couple of things.

    1. Recommend that you submit your question in a separate thread.  The thread above has pretty much closed.
    2. Can you clarify your Solarwinds version?  Solarwinds version 2023.4.2 recently hit the streets, so that version is very new.  Solarwinds APM 4.2 is over 10 years old, so that's way out of date.  Probably limited help available for a version that old.

    Cheers.

  • For code formatting, use "Insert > Code" and paste in the code.  I corrected it for you,  .

  • Anyone who's on this thread, take a look at Adventures in PIVOTing - how to do a poor man's pivot in SWQL , where I work with a similar case to extract this kind of data.

  • I should also add that I think we've discussed what you are asking about in Multi-Statistic Data (Script Components) and pivot example .  Take a look at the example (it's very general) and see if that doesn't answer most of your questions.

  • Excuse me , i have a question that in ApInsight for Active Directory have User and Computer Events. So how can i use SWQL to query like this overtime time for a week, i try to used this query but it's not work. Please, helpme.

    SELECT
    c.ComponentName AS NAME,
    SUM(cs.ComponentStatisticData) AS Total
    FROM
    [dbo].[APM_CurrentStatistics] c
    JOIN
    [dbo].[APM_CurrentStatistics] cs ON c.ComponentID = cs.ComponentID
    WHERE
    cs.ComponentName = 'Attempted to logon using explicit credentials event'
    AND LastTimeUp >= DATEADD(WEEK, -1, GETDATE())
    AND LastTimeUp < GETDATE()
    GROUP BY
    c.ComponentName;


  • I'm not seeing 'Attempted to logon using explicit credentials event' listed in the above table.  I'm not as well versed in the AppInsight statistics - they may be stored differently.  Do you get any results if you remove the component name from your WHERE clause?

  • i don't get any results if i remove component name, I am using Orion.APM.CurrentStatistic DB and I want to export data from Component of User Event or something else in AppInsight for a week like this, but this DB(Orion.APM.CurrentStatistic) don't have data of time to statiscal. I try to use INNER JOIN from another DB but it's not working. Do you have any idea for this? Thank you so much.

  • You really shouldn't query the database for this information - you should be using the API to get this info.  The database can change, but queries via the API are much more stable.  We go over some basics on how to query it in SolarWinds Platform API .

    SELECT TOP 1000 [Application].Node.Caption AS [Node]
          , [Application].Node.DetailsUrl AS [Node_Link]
          , [Application].DisplayName AS [Application]
          , [Application].DetailsUrl AS [Application_Link]
          , [Application].Components.DisplayName AS [Component]
          , [Application].Components.DetailsUrl AS [Component_Link]
          , [Application].Components.ChartEvidence2.ObservationTimestamp
          , [Application].Components.ChartEvidence2.AvgStatisticData AS [Metric]
    FROM Orion.APM.Application AS [Application]
    WHERE [Application].Template.Name = 'AppInsight for Active Directory'
      -- The below is where you call pull out one of the specific metrics
      AND [Application].Components.DisplayName = 'Attempted to logon using explicit credentials event'
      AND [Application].Components.ChartEvidence2.ObservationTimestamp >= ADDDAY(-7, GETUTCDATE())
      AND [Application].Components.ChartEvidence2.AvgStatisticData IS NOT NULL
    ORDER BY [Application].Node.Caption, [Application].[Name], [Application].Components.Name
      , [Application].Components.ChartEvidence2.ObservationTimestamp DESC

    The above is using the API to pull this data.  If you need additional help, you should ask questions over in SolarWinds SDK .

Reply
  • You really shouldn't query the database for this information - you should be using the API to get this info.  The database can change, but queries via the API are much more stable.  We go over some basics on how to query it in SolarWinds Platform API .

    SELECT TOP 1000 [Application].Node.Caption AS [Node]
          , [Application].Node.DetailsUrl AS [Node_Link]
          , [Application].DisplayName AS [Application]
          , [Application].DetailsUrl AS [Application_Link]
          , [Application].Components.DisplayName AS [Component]
          , [Application].Components.DetailsUrl AS [Component_Link]
          , [Application].Components.ChartEvidence2.ObservationTimestamp
          , [Application].Components.ChartEvidence2.AvgStatisticData AS [Metric]
    FROM Orion.APM.Application AS [Application]
    WHERE [Application].Template.Name = 'AppInsight for Active Directory'
      -- The below is where you call pull out one of the specific metrics
      AND [Application].Components.DisplayName = 'Attempted to logon using explicit credentials event'
      AND [Application].Components.ChartEvidence2.ObservationTimestamp >= ADDDAY(-7, GETUTCDATE())
      AND [Application].Components.ChartEvidence2.AvgStatisticData IS NOT NULL
    ORDER BY [Application].Node.Caption, [Application].[Name], [Application].Components.Name
      , [Application].Components.ChartEvidence2.ObservationTimestamp DESC

    The above is using the API to pull this data.  If you need additional help, you should ask questions over in SolarWinds SDK .

Children
No Data