Can you help me Join 3 source types using OR?

[poojak2579]

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!

[PaoloR84]

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?

[poojak2579]

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.

[poojak2579]

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

[kamlesh_vaghela]

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

[poojak2579]

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.

[harishalipaka]

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