I've got two servers providing me temperature data. Host A has Sensor1 and Sensor2. Host B has Sensor1 and Sensor2.
My goal is a line graph of all four sensors named as their actual room name. As long as I use host=HostA in the base search, my timechart works great with 20min avg.
index=tempmon sourcetype=tempdata host=HostA
| timechart span=20min eval(round(avg(Sensor1),2)) as "Room12", eval(round(avg(Sensor2),2)) as "Room13"
I'm struggling to understand if a subsearch or 'where' statement would help do something like this
index=tempmon sourcetype=tempdata
Where host=HostA
| eval Room12=Sensor1
| eval Room13=Sensor2
Where host=HostB
| eval Room14=Sensor1
| eval Room15=Sensor2
| timechart span=20min avg(Room12), avg(Room13), avg(Room14), avg(Room15)
If I understand your problem correctly - you have two fields (sensor1 and sensor2) which contain your data points but you have also a "classifying" field host effectively giving you four separate data series, right?
And you want to get four separate fields from that to be able to do four dinstinct aggregations for your timechart.
Well, there might be several different possible approaches to this.
One is to just use a set of conditional evals to create synthetic fields from your data as @yuanliu showed. The downside to this method is that it can be tedious to write all those evals and keep track of them, especially if your data is more complicated than just two sensors and two hosts.
Another one is to use the {} notation to dynamically create field names.
A run-anywhere example (not really timecharting much due to just a few input values but showing the idea)
| makeresults format=csv data="_time,sensor1,sensor2,host
1,1,2,host1
1,2,3,host2
2,4,5,host1
2,5,6,host2"
| eval {host}sensor1=sensor1
| eval {host}sensor2=sensor2
| fields - sensor1 sensor2
| timechart avg(host*sensor*) as **
This is easier to maintain because it's happening automagically but the downside is that you have much less control over resulting field names (of course you can rename them manually but that's when we again step into the field of manual fiddling with your data).
It would be much easier to describe the problem with illustrations/mockups of your input and desired output, and a description of the logic between illustrated input and desired output without SPL.
I've got two servers providing me temperature data. Host A has Sensor1 and Sensor2. Host B has Sensor1 and Sensor2.
Let me try to decipher the requirements. You have a dataset like
_time | host | Sensor1 | Sensor2 |
2024-06-05 12:00:00 | HostA | 45.5 | 48.1 |
2024-06-05 12:00:00 | HostB | 50.0 | 45.1 |
2024-06-05 12:20:00 | HostA | 46.8 | 47.4 |
2024-06-05 12:20:00 | HostB | 48.8 | 46.3 |
2024-06-05 12:40:00 | HostA | 45.8 | 48.6 |
2024-06-05 12:40:00 | HostB | 45.3 | 45.4 |
Your psuedo code suggests that Host A has Sensor1 in Room 12 and Sensor2 in Room 13. Host B has Sensor1 in Room 14 and Sensor2 in Room 15. You want something like the following:
_time | avg(Roome12) | avg(Roome13) | avg(Roome14) | avg(Roome15) |
2024-06-05 12:00:00 | 45.5 | 48.1 | 50.0 | 45.1 |
2024-06-05 12:20:00 | 46.8 | 47.4 | 48.8 | 46.3 |
2024-06-05 12:40:00 | 45.8 | 48.6 | 45.3 | 45.4 |
But your data doesn't directly give you the mapping. Is this correct? I lack the imagination of how this physical layout works, but in SPL, you can do something like
index=tempmon sourcetype=tempdata
| eval "Room 12" = if(host == "HostA", Sensor1, null())
| eval "Room 13" = if(host == "HostA", Sensor2, null())
| eval "Room 14" = if(host == "HostB", Sensor1, null())
| eval "Room 15" = if(host == "HostB", Sensor2, null())
| timechart span=20min avg("Room 12"), avg("Room 13"), avg("Room 14"), avg("Room 15")
You can play with the following emulation and compare with real data
| makeresults format=csv data="_time,host,Sensor1,Sensor2
2024-06-05 12:00:00, HostA, 45.5,48.1
2024-06-05 12:00:00, HostB, 50.0,45.1
2024-06-05 12:20:00, HostA,46.8,47.4
2024-06-05 12:20:00, HostB,48.8,46.3
2024-06-05 12:40:00, HostA,45.8,48.6
2024-06-05 12:40:00, HostB,45.3,45.4"
| eval _time = strptime(_time, "%F %T")
``` the above emulates
index=tempmon sourcetype=tempdata
```
On the other hand, maybe you do not want to hard code the mapping. In that case, you can set up a lookup table like
host | Sensor | Room |
hostA | Sensor1 | Room 12 |
hostA | Sensor2 | Room 13 |
hostB | Sensor1 | Room 14 |
hostB | Sensor2 | Room 15 |
Before I move on with SPL, it is important to understand what you really have in raw data. My mockup assumes that you have two separate fields Sensor1 and Sensor2. While this is a possible with some data sources, a more normalized data feed might give the following instead.
_time | host | Sensor | Temperature |
2024-06-05 12:00:00 | HostA | Sensor1 | 45.5 |
2024-06-05 12:00:00 | HostA | Sensor2 | 48.1 |
2024-06-05 12:00:00 | HostB | Sensor1 | 50.0 |
2024-06-05 12:00:00 | HostA | Sensor2 | 45.1 |
2024-06-05 12:20:00 | HostA | Sensor1 | 46.8 |
2024-06-05 12:20:00 | HostA | Sensor2 | 47.4 |
2024-06-05 12:20:00 | HostB | Sensor1 | 48.8 |
2024-06-05 12:20:00 | HostB | Sensor2 | 46.3 |
2024-06-05 12:40:00 | HostA | Sensor1 | 45.8 |
2024-06-05 12:40:00 | HostA | Sensor2 | 48.6 |
2024-06-05 12:40:00 | HostB | Sensor1 | 45.3 |
2024-06-05 12:40:00 | HostB | Sensor2 | 45.4 |
| makeresults format=csv data="_time,host,Sensor,Temperature
2024-06-05 12:00:00, HostA,Sensor1, 45.5
2024-06-05 12:00:00, HostA,Sensor2,48.1
2024-06-05 12:00:00, HostB, Sensor1, 50.0
2024-06-05 12:00:00, HostA,Sensor2,45.1
2024-06-05 12:20:00, HostA,Sensor1,46.8
2024-06-05 12:20:00, HostA,Sensor2,47.4
2024-06-05 12:20:00, HostB,Sensor1,48.8
2024-06-05 12:20:00, HostB,Sensor2,46.3
2024-06-05 12:40:00, HostA,Sensor1,45.8
2024-06-05 12:40:00, HostA,Sensor2,48.6
2024-06-05 12:40:00, HostB,Sensor1,45.3
2024-06-05 12:40:00, HostB,Sensor2,45.4"
| eval _time = strptime(_time, "%F %T")
| table _time,host,Sensor,Temperature
``` the above emulates
index=tempmon sourcetype=tempdata
``````