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

[idofwasim]

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

[BahadirS · Accepted solution]

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]

@BahadirS  Thank you so much , it worked 🙂 

[AnilPujar]

@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

[ITWhisperer]

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

[idofwasim]

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

[ITWhisperer]

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

[idofwasim]

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