Use data from two indexes, with separate events

[morganj1]

Hi, I have two indexes from two different sources, but I want to use them together, preferably in a table.

index1 contains data on hardware units in a rack, with fields ESN (serial number) and UnitTemperature.
index2 contains external temperature data, with fields Thermocouple (which are just numbered 1 to 8 ) and Temperature. Thermocouples 1-4 are at the back and 5-8 are at the front of the rack.

I have a lookup table to get the number of the two thermocouples (front and back) closest to each unit from their ESN.

Now what I want is to make a table with columns:
ESN, Unit Temperature, Closest Front Thermocouple Temperature, Closest Back Thermocouple Temperature.

Is there any easy way to do this?

Thanks.

[morganj1]

Thanks for your quick response!

I'm afraid this doesn't really work for me, I don't have the ESN field in both indexes. Sorry, it's quite hard to word what I actually want.

Is there any way of sort of using index2 as a lookup table?

So, I have a lookup table which gives me TC_Back and TC_Front from the ESN. So these will be number 1-4 or 5-8, respectively. Is there a way I can input this number into index2 and receive the latest Temperature for the corresponding Thermocouple number?

Something like:
index=index1 | lookup lookup_table ESN TC_Back | eval TempBack=function([index=index2 |  latest(Temperature) | where Thermocouple=TC_Back])

I hope this makes sense...

[gcusello]

Hi @morganj1,

I'm not sure to completely understand your need, but I think that you could use eval command to distinguish your temparature:

| eval index2_temperature=if(index=index2,temperature,""), index1_temperature=if(index=index1,temperature,"")

in this way you could have a variable that contains temperature from a specific index and use it in your stats command.

Ciao.

Giuseppe

[ITWhisperer · Accepted solution]

| makeresults 
| eval _raw="ESN,UnitTemperature
1234,20
2345,23
4567,56"
| multikv forceheader=1 
| append 
    [| makeresults 
    | eval _raw="Thermocouple,Temperature
1,10
2,11
3,12
4,13
5,31
6,32
7,33
8,34"
    | multikv forceheader=1 
    ]
``` Replace above with index=index1 OR index=index2 ```


    | append
    [| makeresults 
    | eval _raw="ESN,ClosestFrontThermocouple,ClosestBackThermocouple
1234,5,1
2345,6,2
4567,7,3"
    | multikv forceheader=1
    ``` Replace this with inputlookup of your lookup table ```
    ]
| fields - _* linecount
``` The above makes up some dummy data and should be replaced with your search of both indexes and appending your lookup table ```

``` Gather closest thermocouple ids for each ESN ```
| eventstats values(ClosestBackThermocouple) as ClosestBackThermocouple values(ClosestFrontThermocouple) as ClosestFrontThermocouple by ESN
``` Evaluate whether thermocouple is front or back ```
| eval ClosestBackThermocouple=if(Thermocouple<5,Thermocouple,ClosestBackThermocouple)
| eval ClosestFrontThermocouple=if(Thermocouple>4,Thermocouple,ClosestFrontThermocouple)
``` Gather closest temperatures from thermocouples ```
| eventstats values(Temperature) as ClosestBackThermocoupleTemperature by ClosestBackThermocouple
| eventstats values(Temperature) as ClosestFrontThermocoupleTemperature by ClosestFrontThermocouple
``` Just keep the original ESN events ```
| where isnotnull(UnitTemperature)
``` Just keep the required fields ```
| fields ESN UnitTemperature ClosestFrontThermocoupleTemperature ClosestBackThermocoupleTemperature

[morganj1]

Wow this is actually exactly what I wanted! Thank you very much, I'm surprised you even understood what I was asking 🙂

[gcusello]

Hi @morganj1,

if you have the ESN field in both indexes you can correlate data from both the indexes using the stats command, some thing like this:

index=index1 OR index=index2
| stats values(UnitTemperature) AS UnitTemperature values(ClosestFrontThermocoupleTemperature) AS ClosestFrontThermocoupleTemperature values(ClosestBackThermocoupleTemperature) AS ClosestBackThermocoupleTemperature BY ESN

if ENS has a different name in one index, you have to rename it.

If you have more values for Temperature, you have to define the function to use: min, max, avg, etc...

If you have spaces in the field names, rename them.

Ciao.

Giuseppe