Transform Undp Table Result (Sum)

I am struggling with a Universal Device Poller Where the get request returns a table. The table will have many rows of values like:

Row ID Value

1.0 0

1.1 0

1.2 0

1.3 12

1.4 29

1.5 0

1.6 55

1.7 0

1.8 0

I am really not interested in the individual values, but I would really like to get a total of all of those values. I tried creating a transform and using the columnsum({PollerName}) transform but that is not working. When I test the transform I see several rows of values instead of just one value. Is there a way to get a total of all of the values in the Value column?

Find more posts tagged with

transform

UnDP

Accepted answers

sum_giais

Hi there @k1gaudineer -

It's possible. It doesn't appear to work the way one may expect, but the functionality is there using this ColumnSum transform formula. Below is the syntax of the formula to use in the transform. Replace ifMtu with the column header of your data. I was testing this a bit and found that it ends up doing a column sum, however a separate row of data is produced for every one. If you figure out a way around that let me know...

columnsum( {ifMtu} )

Below are some helpful links about it...

https://www.solarwinds.com/documentation/en/flarehelp/npm/npm_2020-2/content/npm-universal-device-poller-transform-results-definition-sw388.htm?cshid=OrionPHUniversalDevicePollerTransformResultsDefinition

https://www.solarwinds.com/documentation/en/flarehelp/npm/npm_2020-2/content/npm-universal-device-poller-common-calculations-sw391.htm

All comments

chenash

You can use swql/sql to sum all values from a specific undp (using unique poller name on the query), this will return the total of all values from a specific undp.

Orion 2020.2 has built in SWQL builder in the modern dashboards that allows you to do it very easy.

k1gaudineer

chenash,

Thanks for the reply, but the example I gave is just one UnDP. In my actual scenario, I have 10 UnDp's that return tables that I need to sum the value column. I am wanting to use this transformed value as the basis for an alert trigger not in a dashboard chart or resource. It would be a lot easier if the ColumnSum would work because then all I need to do is test for the delta between two polling cycles on the total value. SWQL for me is completely confusing and I just haven't seen much documentation on SWQL that helps me figure it out.

chenash

Hi,

If i understood correctly, you want to "take" the values of 10 different uNDPs and SUM the values of all the uNDPs together, then create an alert on that value ?

If so, Im afraid its achievable only via SQL/SWQL.

unless someone else did managed to achieve that with the transform feature of the undp tool which im pretty sure impossible

k1gaudineer

chenash,

That's close, What I have is 10 UnDPs. Each UnDP returns a table of values. I only want a total value for each UnDP so I would then create 10 transforms (one for each UnDP). The Orion Admin Guide for UnDP explains that the ColumnSum transform function is the way to get a column total. Once the transform for each UnDP is created I would then use the transforms in a custom poller alert using the OR logic

(transform-1 OR transform-2 OR transform-3 etc.... value is greater than {value}) trigger these alert actions

The problem is that when I use the ColumnSum() transform function I am still receiving a table of data instead of a single value.

sum_giais

Hi there @k1gaudineer -

columnsum( {ifMtu} )

Below are some helpful links about it...

https://www.solarwinds.com/documentation/en/flarehelp/npm/npm_2020-2/content/npm-universal-device-poller-common-calculations-sw391.htm

k1gaudineer

sum_giais,

Thank you for your reply. Thats the understanding I was missing. You are spot on... the entire column is being summed and for every row that was part of that sum the ColumnSum shows up in a separate column next to every row. (that's a lot to say).... That's not what I was expecting but at least the sum is there. I can work with that...

clarv02

@k1gaudineer could you provide an example of a SWQL query to do what you're describing? This seems to be exactly what I need.

Thanks!!