topic Re: Do i have to use a join in mstats? in Splunk Search

Do i have to use a join in mstats?

robertlynch2020 — Wed, 10 Nov 2021 16:16:23 GMT

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

Re: Do i have to use a join in mstats?

sideview — Wed, 10 Nov 2021 17:42:12 GMT

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.

Re: Do i have to use a join in mstats?

robertlynch2020 — Thu, 11 Nov 2021 15:47:02 GMT

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

| 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

Re: Do i have to use a join in mstats?

robertlynch2020 — Mon, 15 Nov 2021 09:55:21 GMT

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-tstats/m-p/319801

| 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