Splunk Search

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

idofwasim
Explorer

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

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

BahadirS
Path Finder

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.

 

index=index1 sourcetype=Error | table err_msg, id, nid

| join type=left nid [ search index=index1 sourcetype=Node | table address, id, resultid | rename id as nid]

| join type=left resultid [ search index=index1 sourcetype=Result | table id , model | rename id as resultid]

 

 

View solution in original post

BahadirS
Path Finder

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.

 

index=index1 sourcetype=Error | table err_msg, id, nid

| join type=left nid [ search index=index1 sourcetype=Node | table address, id, resultid | rename id as nid]

| join type=left resultid [ search index=index1 sourcetype=Result | table id , model | rename id as resultid]

 

 

idofwasim
Explorer

@BahadirS  Thank you so much , it worked 🙂 

AnilPujar
Path Finder

@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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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
0 Karma

idofwasim
Explorer

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

0 Karma

idofwasim
Explorer

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

0 Karma
Get Updates on the Splunk Community!

Splunk Education - Fast Start Program!

Welcome to Splunk Education! Splunk training programs are designed to enable you to get started quickly and ...

Five Subtly Different Ways of Adding Manual Instrumentation in Java

You can find the code of this example on GitHub here. Please feel free to star the repository to keep in ...

New Splunk APM Enhancements Help Troubleshoot Your MySQL and NoSQL Databases Faster

Splunk Observability has two new enhancements to make it quicker and easier to troubleshoot slow or frequently ...