Splunk Enterprise

Merge Data per timestamp

robertlynch2020
Motivator

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_0-1634052561713.png

 

 

Labels (1)
Tags (2)
0 Karma
1 Solution

robertlynch2020
Motivator

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

 

 

View solution in original post

0 Karma

robertlynch2020
Motivator

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

 

 

0 Karma
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...