Splunk Search

Do i have to use a join in mstats?

robertlynch2020
Motivator

@sideview 

Hi Nick

I am using a join with mstat, but i am hoping that i dont have to.

However, i cant crack it - any help would be amazing.

Below is the current SPL

 

| mstats min("mx.process.cpu.utilization") as cpuPerc WHERE "index"="metrics_test" AND mx.env=http://mx20267vm:15000 span=10s BY pid  service.type  service.name replica.name
| rename "service.name" as service_name 
| rename "replica.name" as replica_name 
| rename "service.type" as service_type 
| eval Process_Name=((service_name . " # ") . replica_name) 
| sort 0 - _time 
| dedup _time pid 
| join type=left Process_Name _time 
    [| mstats min("mx.replica.status") as Replica WHERE "index"="metrics_test" AND mx.env=http://mx20267vm:15000 span=10s BY "service.name" replica.name service.type 
    | rename "service.name" as service_name 
    | rename "replica.name" as replica_name 
    | eval Process_Name=((service_name . " # ") . replica_name) 
    | table Process_Name, Replica, "service.type", _time 
    | sort 0 - _time 
    | dedup _time Process_Name] 
| table _time Process_Name Replica cpuPerc service_type

 

 

 

I have tored to make it one mstats but that will not work as in this case min("mx.replica.status") as Replica as no pid so Splunk gives me back blank for this field. SO do i have to use a JOIN?

 

 

| mstats mstats min("mx.replica.status") as Replica min("mx.process.cpu.utilization") as cpuPerc WHERE "index"="metrics_test" AND mx.env=http://mx20267vm:15000 span=10s BY pid  service.type  service.name replica.name
| rename "service.name" as service_name 
| rename "replica.name" as replica_name 
| rename "service.type" as service_type 
| eval Process_Name=((service_name . " # ") . replica_name) 
| sort 0 - _time 
| dedup _time pid 
| table _time Process_Name Replica cpuPerc service_type

 

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

sideview
SplunkTrust
SplunkTrust

I think the join might be avoidable.

In general though the rule of thumb is that when one or both sides are generating commands like this, the normal bag of tricks isn't there - ie making a disjunction and normalizing/cajoling with eval, making stats do the work etc.

but!!   the silver lining here is that with both tstats and mstats there is a way to avoid the limits of join and append commands, in that they both have an append=true (at least if prestats=t)

so, lose the join, append=t the second mstats, some TBD conditional eval to make the names work out, and then <handwave> eval and stats and friends to make it work out "like a left join"

i realize there's a bit of handwaving there and someone else might have an intuitive leap to be able to post a full answer without data to play in.

Also, I know that with tstats there are some confusing pitfalls around prestats=t having to do with field names not being what you expect, and I wonder (but dont really know) whether mstats prestat=t has the same ones.

View solution in original post

0 Karma

sideview
SplunkTrust
SplunkTrust

I think the join might be avoidable.

In general though the rule of thumb is that when one or both sides are generating commands like this, the normal bag of tricks isn't there - ie making a disjunction and normalizing/cajoling with eval, making stats do the work etc.

but!!   the silver lining here is that with both tstats and mstats there is a way to avoid the limits of join and append commands, in that they both have an append=true (at least if prestats=t)

so, lose the join, append=t the second mstats, some TBD conditional eval to make the names work out, and then <handwave> eval and stats and friends to make it work out "like a left join"

i realize there's a bit of handwaving there and someone else might have an intuitive leap to be able to post a full answer without data to play in.

Also, I know that with tstats there are some confusing pitfalls around prestats=t having to do with field names not being what you expect, and I wonder (but dont really know) whether mstats prestat=t has the same ones.

0 Karma

robertlynch2020
Motivator

Hi 

1st - thanks for the answer - I think I am very close - but I just cant get to the final push.

I am trying to use append=t prestats=t so I don't have to use a join.
However when I introduce a field "threshold" in the "BY" clause that is in one tstat and not the other and try to use this in the stats...I lose data. In this case, R stops coming in.
Any ideas? I think I am close, but I am just missing something

2021-11-11 15_39_33-Untitled - Paint.png

2021-11-11 15_38_26-Untitled - Paint.png

| mstats append=t prestats=t  min("mx.replica.status") WHERE "index"="metrics_test" AND mx.env=http://mx20267vm:15000 span=10s BY "service.name" replica.name service.type 
| mstats append=t prestats=t min("mx.process.cpu.utilization")  WHERE "index"="metrics_test" AND mx.env=http://mx20267vm:15000 span=10s BY "service.name" replica.name service.type threshold

| stats min("mx.replica.status") as R min("mx.process.cpu.utilization") as C by _time "service.name" replica.name service.type threshold

 

0 Karma

robertlynch2020
Motivator

Hi

In the end this was very tricky, but the below code is what I used.

Please note this was for the full example.  I would suggest reading this answer as well as there are lots of hints and tricks that have to be applied to get it to work - To be honest its a lot of work - but it does work.

https://community.splunk.com/t5/Splunk-Search/What-exactly-are-the-rules-requirements-for-using-quot...

 

 

| mstats append=t prestats=t min("mx.replica.status") min("mx.process.resources.status") WHERE "index"="metrics_test" AND mx.env=http://mx20267vm:15000 span=10s BY service.name replica.name service.type 
| eval threshold = "" 
| eval pid="" 
| eval cmd="" 
| eval host.name="" 
| eval component.name="" 
| mstats append=t prestats=t min("mx.process.threads") min("mx.process.memory.usage") min("mx.process.file_descriptors") min("mx.process.cpu.utilization") min("mx.process.up.time") avg("mx.process.creation.time") 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 replica.name as replica_name 
| rename "service.type" as service_type 
| stats min("mx.process.resources.status") as Resources min("mx.process.up.time") as upTime avg("mx.process.creation.time") as creationTime min("mx.replica.status") as Replica min("mx.process.threads") as nbOfThreads min("mx.process.memory.usage") as memoryCons min("mx.process.file_descriptors") as nbOfOpenFiles min("mx.process.cpu.utilization") as cpuPerc by _time pid cmd service_type host.name service_name replica_name component.name threshold 
| eval T_NbOfThreads=if(isnull(nbOfThreads),"",threshold) 
| eval T_MemoryCons=if(isnull(memoryCons),"",threshold) 
| eval T_NbOfOpenFiles=if(isnull(nbOfOpenFiles),"",threshold) 
| eval T_CpuPerc=if(isnull(cpuPerc),"",threshold) 
| eval Process_Name=((service_name . " # ") . replica_name) 
| sort 0 - _time Process_Name 
| streamstats last(Replica) as Replica 
| streamstats last(Resources) as Resources 
| where cmd !="" 
| stats values(Resources) as Resources values(Replica) as Replica values(cpuPerc) as cpuPerc values(nbOfThreads) as nbOfThreads values(memoryCons) as memoryCons values(nbOfOpenFiles) as nbOfOpenFiles values(upTime) as upTime values(creationTime) as creationTime values(T_NbOfOpenFiles) as T_NbOfOpenFiles values(T_MemoryCons) as T_MemoryCons values(T_CpuPerc) as T_CpuPerc values(T_NbOfThreads) as T_NbOfThreads by _time pid cmd Process_Name service_type host.name service_name replica_name component.name 
| eval Status=(Resources * Replica) 
| eval Status=if((Status == 4),2,if((Status == 0),0,1)) 
| eval Replica=case((Process_Name == "xmlserver # xmlserver"),"2",(Process_Name == "zookeeper # zookeeper"),"2",(Process_Name == "fileserver # fileserver"),"2",true(),Replica) 
| dedup _time pid 
| sort 0 - _time pid 
| table _time Resources Replica pid cmd service_type host.name service_name replica_name component.name cpuPerc nbOfThreads memoryCons nbOfOpenFiles upTime creationTime

 

 

 

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...