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
Works?
Thanks for the reply.
I can not use join or append command because of maxout limit of 50000 records.
I think I can only use OR but not sure how to join all 3 using OR.
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
|where cnt=2
|mvexpand SS
|mvexpand CC
@poojak2579
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.
hi @pooja
can you try like this
index=xxxxxxxx sourcetype_1 OR sourcetype_2 OR sourcetype_3
Eg:-
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