topic Re: result from subquery with different source types in Splunk Search

How to export a result with stats count of err_msg by Node and model?

idofwasim — Wed, 09 Feb 2022 19:05:12 GMT

I have 3 different sourcetype like Result , Node and error under same index.

Result has id , model

Node has address, id, resultid (which is key to id in result)

Error has err_msg, id, nid (which is key to id in Node)

I want to export a result with stats count of err_msg by Node and model. I tried with joins and subquery with IN operator from other query but no luck.

index= index1 sourcetype = Node

[ search index= index1 sourcetype = Error | stats count by err_msg ]

| stats count by id,err_msg

Re: result from subquery with different source types

ITWhisperer — Wed, 09 Feb 2022 15:42:10 GMT

Try something like this

index=index1 (sourcetype="Error" OR sourcetype="Result" OR sourcetype="Node") | eval nid=if(sourcetype="Node",id,nid) | eval resultId=if(sourcetype="Result",id,resultId) | eventstats values(model) as model by resultId | eventstats values(model) as model values(address) as address by nid | where sourcetype="Error" | stats count by nid model

Re: result from subquery with different source types

idofwasim — Thu, 10 Feb 2022 11:39:44 GMT

@ITWhisperer Thanks for helping out. it almost works . I can see the stats count of err_msg and node . but when I add model, it doesnt show any results. any help.

Re: result from subquery with different source types

ITWhisperer — Thu, 10 Feb 2022 12:40:28 GMT

Can you provide some sample (anonymised) events showing when it doesn't work?

Re: How to export a result with stats count of err_msg by Node and model?

AnilPujar — Thu, 10 Feb 2022 12:55:45 GMT

@idofwasim

index=index1 (sourcetype="Error" OR sourcetype="Result" OR sourcetype="Node") | eval cid=coalesce(id,resultid,nid) | stats values(model) values(address) values(err_msg) by cid sourcetype | search sourcetype="Error" | rename values(*) as * | eval err_count=mvcount(err_msg)

after this you can customize or take stats as you want

Re: result from subquery with different source types

idofwasim — Thu, 10 Feb 2022 15:38:06 GMT

result source type

id model

100 aaa

101 bbb

node source type

id address resultid

200 AAA 100

201 BBB 100

202 AAA 101

203 CCC 101

Error source type

id err_msg nid

300 500 error 200

301 500 error 201

302 404 Error 202

If i run with stats count by nid err_msg then it give the count of err by node.

but if i add model at the end of stats (stats count by nid model err_msg) it returns blank

How to export a result with stats count of err_msg by Node and model?

BahadirS — Thu, 10 Feb 2022 16:08:28 GMT

You might want to consider more relational database approach since your data seems to have normalized database structure and you want to analyze more. This way you would get a good structured data to play with.

Re: How to export a result with stats count of err_msg by Node and model?

idofwasim — Sat, 12 Feb 2022 14:05:29 GMT

@BahadirS Thank you so much , it worked 🙂