Merge Data per timestamp

robertlynch2020 — Tue, 12 Oct 2021 15:33:55 GMT

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.

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?

Re: Merge Data per timestamp

robertlynch2020 — Tue, 12 Oct 2021 15:37:42 GMT

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/422336

topic Re: Merge Data per timestamp in Splunk Enterprise

Merge Data per timestamp

Re: Merge Data per timestamp