Merge Data per timestamp

[robertlynch2020]

I am pulling data from multiple locations and a new field threshold has been introduced.

The issue is threshold  is common but has different values depending if it is cpuPerc or memoryCons  etc.. There are 4 of them.

| mstats min("mx.process.cpu.utilization") as cpuPerc min("mx.process.threads") as nbOfThreads min("mx.process.memory.usage") as memoryCons min("mx.process.file_descriptors") as nbOfOpenFiles min("mx.process.up.time") as upTime avg("mx.process.creation.time") as creationTime WHERE "index"="metrics_test" AND mx.env=http://mx20267vm:15000 span=10s BY pid cmd service.type host.name service.name replica.name component.name threshold 

On option I have if i want to display all the thresholds differently is to write a 3 joins - but this is heavy on CPU.

| mstats min("mx.process.cpu.utilization") as cpuPerc WHERE "index"="metrics_test" AND mx.env=http://mx20267vm:15000 span=10s BY pid cmd service.type host.name service.name replica.name component.name threshold 
| rename service.name as service_name 
| rename threshold as T_cpuPerc 
| join _time service.name replica.name 
    [| mstats min("mx.process.threads") as nbOfThreads WHERE "index"="metrics_test" AND mx.env=http://mx20267vm:15000 span=10s BY pid cmd service.type host.name service.name replica.name component.name threshold 
    | rename service.name as service_name 
    | rename threshold as T_nbOfThreads ] 
| join _time service.name replica.name 
    [| mstats min("mx.process.memory.usage") as memoryCons WHERE "index"="metrics_test" AND mx.env=http://mx20267vm:15000 span=10s BY pid cmd service.type host.name service.name replica.name component.name threshold 
    | rename service.name as service_name 
    | rename threshold as T_memoryCons ] 
| join _time service.name replica.name 
    [| mstats min("mx.process.file_descriptors") as nbOfOpenFiles WHERE "index"="metrics_test" AND mx.env=http://mx20267vm:15000 span=10s BY pid cmd service.type host.name service.name replica.name component.name threshold 
    | rename service.name as service_name 
    | rename threshold as T_nbOfOpenFiles ]

I am trying this way but I am not sure how to merge the rows by time in the end - any ideas.

| mstats min("mx.process.cpu.utilization") as cpuPerc min("mx.process.threads") as nbOfThreads min("mx.process.memory.usage") as memoryCons min("mx.process.file_descriptors") as nbOfOpenFiles min("mx.process.up.time") as upTime avg("mx.process.creation.time") as creationTime WHERE "index"="metrics_test" AND mx.env=http://mx20267vm:15000 span=10s BY pid cmd service.type host.name service.name replica.name component.name threshold 
| rename service.name as service_name 
| search service_name IN (*cache*) 
| eval threshold_nbOfThreads=if(isnull(nbOfThreads),"",$threshold$) 
| eval threshold_memoryCons=if(isnull(memoryCons),"",$threshold$) 
| eval threshold_nbOfOpenFiles=if(isnull(nbOfOpenFiles),"",$threshold$) 
| table _time threshold threshold_nbOfOpenFiles threshold_memoryCons threshold_nbOfThreads

The issue is the data is now on different rows  - where I need them to be on the same by _time.

SO in the image below, we have 3 lines for each time. How can i get them to merge per timestamp?

[robertlynch2020 · Accepted solution]

Got it with stats at the end - 

| mstats min("mx.process.threads") as nbOfThreads min("mx.process.memory.usage") as memoryCons min("mx.process.file_descriptors") as nbOfOpenFiles WHERE "index"="metrics_test" AND mx.env=http://mx20267vm:15000 span=10s BY pid cmd service.type host.name service.name replica.name component.name threshold 
| rename service.name as service_name 
| search service_name IN (*cache*) 
| eval threshold_nbOfThreads=if(isnull(nbOfThreads),"",$threshold$) 
| eval threshold_memoryCons=if(isnull(memoryCons),"",$threshold$) 
| eval threshold_nbOfOpenFiles=if(isnull(nbOfOpenFiles),"",$threshold$) 
| table _time service_name threshold_nbOfOpenFiles threshold_memoryCons threshold_nbOfThreads 
| stats values(threshold_nbOfOpenFiles) as threshold_nbOfOpenFiles values(threshold_memoryCons) as threshold_memoryCons values(threshold_nbOfThreads) as threshold_nbOfThreads by _time service_name

Thanks to 

https://community.splunk.com/t5/Splunk-Search/Combine-multiple-rows-in-one-with-a-common-key/m-p/422...