Hi, i'm using Splunk since two month and i love it. But i need help.
I have a lot of sensors, sampling per minute. I have a lookup where I can fill in formulas in text format like 'if (sensor1> 200,0, sensor1 / 10)'. Over the period concerned, I want to apply the formula for each sensor every minute.
Here is an expample with four sensors (row* are the values) :
id_sensor | row 1 | row 2 | row 3 | row 4 | formula |
ID1 | 77.2 | 250 | 77.5 | 79.4 | =if(value<200,value/10,false) |
ID2 | 227.29 | 227.18 | 226.59 | 227.1 | =value/10 |
ID3 | 34.1 | 34.8 | 35.9 | 36.1 | =(value*9/5)+32 |
And the results needed
id_sensor | row 1 | row 2 | row 3 | row 4 |
ID1 | 7.72 | 0 | 7.75 | 7.94 |
ID2 | 22.729 | 22.718 | 22.659 | 22.71 |
ID3 | 93.38 | 94.64 | 96.62 | 96.98 |
I can have a lot of rows and colums ...
I saw quite a few "close" answers on this forum, but none that I managed to apply.
In particular, I wish to avoid going through a subsearch which would limit the number of exploitable results.
Thank you very much in advance.
| tstats count where index=_internal (earliest=-2d@d latest=@d) by sourcetype
| streamstats count as Id
| fillnull col1 col2 col3 col4
| foreach col* [ eval <<FIELD>> = count * (random() % 100 + 1) * <<MATCHSTR>>]
| table Id col*
| rex "(?<comment>(?# this is sample data, from here, foreach and case sample logic.))"
| foreach col* [ eval <<FIELD>> = case(Id=1,if(<<FIELD>> < 200,<<FIELD>>/10,<<FIELD>>),
Id=2,<<FIELD>> / 10,
Id=3,(<<FIELD>> * 9 / 5 ) + 32,
true(), <<FIELD>> )]
Here's how it goes.
How many different formulas are there?
If it's about 10 or so, it's easier to identify it by CASE.
Hi To4kawa, and thank you 🙂
The number of formulas will grow over time.
We measure a large number of different equipment and different brands. We must therefore each time imagine the conversion factors necessary to standardize our data.
I will even watch how "Case" works
| tstats count where index=_internal (earliest=-2d@d latest=@d) by sourcetype
| streamstats count as Id
| fillnull col1 col2 col3 col4
| foreach col* [ eval <<FIELD>> = count * (random() % 100 + 1) * <<MATCHSTR>>]
| table Id col*
| rex "(?<comment>(?# this is sample data, from here, foreach and case sample logic.))"
| foreach col* [ eval <<FIELD>> = case(Id=1,if(<<FIELD>> < 200,<<FIELD>>/10,<<FIELD>>),
Id=2,<<FIELD>> / 10,
Id=3,(<<FIELD>> * 9 / 5 ) + 32,
true(), <<FIELD>> )]
Here's how it goes.
What I did with "case".
In my lookup, I created a "case" field and several other fields with different values.
"target" "coef1" "coef2" "coef3" "correction"
For case "0": <<FIELD>> = '<<FIELD>>'
For case "1": <<FIELD>> = ('<<FIELD>>' * coef1 + coef2) * coef3
For case ">": if ('<<FIELD>>'> target, ('<<FIELD>>' * coef1 + coef2) * coef3, correction)
For case "<": if ('<<FIELD>>' <target, ('<<FIELD>>' * coef1 + coef2) * coef3, correction)
...
...
And it does what I want :).