Problems charting statistic data via a custom chart?

jbiggley

We made a quick and dirty little PowerShell script that we are executing on all of our polling engines via a PowerShell component monitor.

$ScriptBlock = { ( netstat -anob ) | Where-Object { $_ -like '*svchost.exe*' } | Measure-Object | Select-Object -ExpandProperty Count}

$count = Invoke-Command -ComputerName '${Node.Caption}' -Credential '${CREDENTIAL}' -ScriptBlock $ScriptBlock

Write-Host "Statistic:" $count

Write-Host "Message: Total connections " $count

(A reminder to max sure your template is set to x64 and the component is set to local execution with impersonation!)

When we try and graph the statistic data though, it fails.  But it only fails for the new, custom chart widget (the one that let's you dynamically shift the time period, slide the period focus, etc.)  It works using the old-style Multiple Object Chart, but that chart has a limited set of data summation options.

Has anyone else run into the same or similar problems with graphing this data series?  I verified that we could see the data series via this SWQL query and it works just like any of the other data series.  (Yes, our component is called svchost.exe connection count)

SELECT

cs.component.application.node.caption

,cs.ComponentStatisticData

,CONCAT('http:\\monitoring.cardinalhealth.net',cs.component.DetailsURL) as URL

FROM Orion.APM.CurrentStatistics cs

WHERE

ComponentName = 'svchost.exe connection count'

ORDER BY cs.ComponentStatisticData DESC

xtraspecialj

For anybody else that runs into this issue it is because the Custom Charts can only plot statistics that are integer values.  If you look in the SWQL table Orion.APM.DynamicEvidence you will see that the Avg, Min, and Max NumericData columns are all double values.  Simply multiply whichever NumericData column you are using in your SWQL query by 1 and that changes the double value to an integer type value.  Inversely, multiplying an integer by 1.0 in SWQL will convert it to a double.

If you are using SQL then just convert the field to an integer.  (You really shouldn't be using SQL though unless you have to.  SWQL should be used whenever possible as it will not break if/when SolarWinds makes a database schema change, whereas all of your custom items using SQL as a data source could break if they did that.  I only use SQL when my query requires a function that SWQL doesn't have)

Example

SELECTde.ComponentStatus.Component.Application.Node.Caption AS NodeCaption,de.ColumnLabel,de.AvgNumericData * 1 AS ScriptStatistic,de.ComponentStatus.TimeStampFROMOrion.APM.DynamicEvidence deWHEREde.ColumnLabel = 'Whatever Column Label you want'ANDde.ComponentStatus.TimeStamp >= ${FromTime}ANDde.ComponentStatus.TimeStamp <= ${ToTime}

The problem I'm having now is that the custom chart is not handling the time value properly.  The Orion.APM.ComponentStatus table in SWQL stores the TimeStamp values in local time, so if I use the ${FromTime} and ${ToTime} variables in my WHERE clause and choose Last 2 Hours as my timeframe, everything is fine, but as soon as I change it to something bigger like Last 24 or Last 7 it starts really screwing up the time.  Last 7 Days makes the chart go twelve hours into the future and then go last 7 days from there, whereas Last 24 Hours makes the chart lose (I guess...) current data and only show me data up until last night...  If I change the variables to ${FromTimeUTC} and ${ToTimeUTC} I can get it to work for Last 24 Hours, but the second I change the time range to something bigger or smaller it skews it again.  I've tried changing the TimeStamp fields to UTC time and that makes it worse.

I really am stumped on how this chart is handling the timestamp values and it's really getting me frustrated.