retrieve custom SAM module value by SWQL

i have a custom SAM module (in powershell) that return multiple value in one filed.

it check my last backup job. if job was done it return successful with statistics number 10. if job was failed it return 0.

1- how can i retrieve the single value from "multivaluestatistics" field?

2- how solarwinds interpret field "statisticdata", because i have one node on multivaluestatistics that return 0 but the result ( i guess) is shown as successful with value 10?

Find more posts tagged with

swql query

sam

Comments

adam.beedell

Which value?
There is a multivaluestatistics table where they can be addressed

bucweat

I really wish more folks would provide the working example code/sql when asking these questions...

Here is my example using Orion.APM.ComponentAlert:

SELECT
ca.ApplicationID, 
ca.ComponentID,
ca.ApplicationAvailability, 
ca.ComponentName, 
ca.ComponentType,
cd.Name as componentTypeName, 
ca.ComponentAvailability, 
ca.StatisticData, 
ca.ComponentMessage, 
ca.StatusOrErrorDescription, 
ca.DisplayType, 
ca.LastTimeUp, 
ca.MultiValueMessages, 
ca.MultiValueStatistics, 
ca.PercentApplicationAvailability, 
ca.PercentComponentAvailability, 
ca.WindowsEventMessages
FROM Orion.APM.ComponentAlert ca
join Orion.APM.ComponentDefinition cd on cd.ComponentType = ca.ComponentType
where ca.ComponentType = 45 --'Windows PowerShell Monitor'

One issue I've found with is there is no timestamp associated with the data...LastTimeUp AFAICT is last time component was up and I wanted last time component was polled which would be timestamp of data. So I ended up going with Orion.APM.DynamicEvidenceCurrent. It is more complicated as you have to join the table to itself to get NumericData and StringData in the same record. There is also a bunch of other stuff provided by the query that makes it look more complicated.

 SELECT
    dec1.ComponentID,
    dec1.server,
    n.NodeName as client,
    c.Application.Name as app,
    c.Application.DetailsUrl as url,
    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,
    dec1.Label,
    dec1.NumericData,
    dec1.ThresholdWarning,
    dec1.ThresholdCritical,
    dec2.StringData
FROM (
    select 
        dec.OrionSite.Host as server,
        decs.Label,
        dec.ComponentID,
        dec.ColumnSchemaID,
        decs.ThresholdWarning,
        decs.ThresholdCritical,
        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,
        decs.ThresholdWarning,
        decs.ThresholdCritical,
        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

I use the following in my powershell script application to output results

Write-Host "Message.Label: some text goes here."
Write-Host "Statistic.Label: 12345"

The values identified by "Label" are split out to individual records so you can query for a specific one. Label is the data item name ("Label" in this example), NumericData is the number associated with Statistic.Label (12345), and StringData is the string associated with Message.Label ("some text goes here."). My app has only one component...if you have more you'll want to include component name in your query.

soheilamiri

sorry, here is my SWQL code

SELECT    
         [table].ComponentName
        ,[table].ComponentMessage
        ,[table].MultiValueStatistics

FROM Orion.APM.ComponentAlert AS [table]
-- find component ID from Orion.APM.CurrentStatistics table
WHERE [table].ApplicationID = 35

soheilamiri

thanks @bucweat

data with timestamp are saved on "Orion.APM.DynamicEvidenceChart". thats awesome