Field exists with multiple hosts

[scottmkirkland]

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)

[PickleRick]

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).

[yuanliu]

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

Can you clarify which raw input format is yours?

Emulation of the second format is

| 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
``````