It would be great if anyone could help me to join data from 3 source types:
1) sourcetype_1 has fields AA,MM,CC,Amt1
2) sourcetype_2 has fields AA,mm,ss
3) sourcetype_3 has fields SS,cc,Amt2
Step 1 - Join first and second source type where sourcetype_1.AA == sourcetype_2.AA && sourcetype_1.MM=sourcetype_2.mm
Step 2 - Join result of step 2 and third source type where sourcetype_3.cc= step1.CC and sourcetype_3.SS = Step1.ss
In my final result, I want to group by OO, CC and SS and show all the CC and SS where Amt2 is lesser than Amt
Note - There is a many-to-many relationship between some of the required fields.
Thanks in advance!
I've used makeresults so the query is a little messy! Basically:
Step 1 -
join by AA, MM (being a subsearch, it is evaluated first) ; you end with two rows, one the join AA,MM and one(or many, in real case) sourcetype3 rows
Step 2 - you now have CC and SS in common, joining by one of them (or even both) should do the work
| makeresults annotate=true | eval sourcetype="sourcetype_3" | eval SS = "dummy3" | eval CC = "dummy1" | eval Amt2 = "dummy5" |append [| makeresults annotate=true | eval sourcetype="sourcetype_1" | eval AA = "1000" | eval MM = "9999" | eval CC = "dummy1" | eval Amt1 = "dummy6" | append [| makeresults annotate=true | eval sourcetype="sourcetype_2" | eval AA = "1000" | eval MM = "9999" | eval SS = "dummy3"] | stats values(*) as * by AA, MM] |stats values(*) as * by CC
Cleaned from makeresults will be something like
index=xyz sourcetype=sourcetype_3 | append [index=xyz sourcetype=sourcetype_1 OR sourcetype=sourcetype_2 | stats values(*) as * by AA, MM] |stats values(*) as * by CC, SS
Currently I am using this query to join first two sourcetypes but not sure how to join third one.
index=xyz (sourcetype=sourcetype_1 ) OR (sourcetype=sourcetype_2 )
|rename mm AS MM
|stats dc(sourcetype) as cnt values(ss) AS ss values(CC) AS CC by OO MM
Can you please try the following search to get related events from all 3 sourcetype?
sourcetype_1 | rename MM as mm | join AA, mm [ search sourcetype_2 ] | rename CC as cc, ss as SS | join ss, CC [ search sourcetype_3 ] | table OO,CC,ss, Amt,Amt2
Another question, in which sourcetype Amt and Amt2 field coming?
BY grouping by OO, CC and SS what you what to achieve?
Thanks for the reply. But I cannot use JOIN due to its limitation since the data has millions of records. We can only use OR (sourcetype_1 OR sourcetype_2 OR sourcetype_3)
Amt1 is coming from sourcetype_1 and
Amt2 is coming from sourcetype_3
In the final result, I want to see the Amt1 and Amt2 at CC and ss level.
can you try like this
index=xxxxxxxx sourcetype_1 OR sourcetype_2 OR sourcetype_3
index=_internal sourcetype="splunkd" OR sourcetype="splunk_web_access" | streamstats count by status, idx, sourcetype | stats values(idx) AS idx, values(status) AS status, values(sourcetype) AS sourcetype | mvexpand status | eval Status = if(match(idx,status), "MATCH", "NO MATCH") | table status, idx, Status