How to transform values from influx to grafana

Grafana
1

Hi,
I use icinga2+influx+grafana and I have a specific measurement which icinga stores 0 for device UP and 3 for device DOWN.

The problem I’m facing is the graph on grafana looks like this for a UP device:

image
image1307×412 12.8 KB

which sounds the opposite. So my question is:
Is there a way to transform this value to something like (UP = 1 / DOWN = 0) for producing a more-intuitive graph?

I mean, if it’s possible to show 1 for UP and 0 for DOWN, so downtimes would be shown as valleys, that would look more intuitive

Any help would be appreciated
Thanks

2

It is a task for primitive math (+,-,x,/,abs) on the InfluxQL level. Target is to get this transformation:
3 → 0
0 → 1

Use:

ABS(("field"/3)-1)

Test:
for field value = 3: ABS((3/3)-1) = 0
for field value = 0: ABS((0/3)-1) = 1

Note: you will need subquery if you need timegrouping, example with Grafana macros:

SELECT 
  MEAN(status) 
FROM (
  SELECT 
    ABS(("field"/3)-1) AS status 
  FROM "measurement" 
  WHERE $timeFilter
) 
GROUP BY time($__interval)

Some query polishing still may be needed. Of course they may be created also other more/less complicated “transformations” with the math and InfluxDB functions.

3

Hi @jangaraj,
First, let me thank you for helping me out.

Sounds good your suggestion, so let me ask a second question:
I’m pretty new to Grafana, and I have already tried to customize the query, but I’m not getting it. When I click on the pencil icon to edit the InfluxQL query, I can edit it, but I didn’t find out how to persist it (I mean, when I click back on editor icon, the interactive query builder keeps the original query).

I’m sorry it’s a dumb question, but I didn’t find how to persist custom InfluxQL queries on Grafana. Could you point me to any doc explaining how to do it?

another point is, trying to test the custom query via influx console, trying the query below works

SELECT state
FROM dummy
WHERE hostname = ‘myhost’ AND service = ‘myservice’
LIMIT 1

but when I run the query below I gt “ERR: unsupported call: abs”

SELECT ABS(state)
FROM dummy
WHERE hostname = ‘myhost’ AND service = ‘myservice’
LIMIT 1

any clue to this?
I’m using influx version 1.1.1

Thank you

4

You can’t toggle between editor modes. Use always RAW mode, where you have to write query manually. UI mode is only for basic simple queries.

I guess your InfluxDB 1.1.1 is very old (current version is 1.8.x) and it doesn’t support ABS function.

5

That helps me a lot
Thank you very much @jangaraj