topic Re: Use data from two indexes, with separate events in Reporting

Use data from two indexes, with separate events

morganj1 — Tue, 13 Apr 2021 12:34:40 GMT

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.

Re: Use data from two indexes, with separate events

gcusello — Tue, 13 Apr 2021 12:41:06 GMT

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

Re: Use data from two indexes, with separate events

morganj1 — Tue, 13 Apr 2021 14:00:24 GMT

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

Re: Use data from two indexes, with separate events

ITWhisperer — Tue, 13 Apr 2021 14:44:48 GMT

| 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

Re: Use data from two indexes, with separate events

morganj1 — Tue, 13 Apr 2021 15:25:52 GMT

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

Re: Use data from two indexes, with separate events

gcusello — Tue, 13 Apr 2021 15:27:05 GMT

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