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
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]
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]
@BahadirS Thank you so much , it worked 🙂
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
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
@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.
Can you provide some sample (anonymised) events showing when it doesn't work?
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