Splunk Search

Can you help me Join 3 source types using OR?

poojak2579
Explorer

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!

0 Karma

PaoloR84
New Member

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?

0 Karma

poojak2579
Explorer

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.

0 Karma

poojak2579
Explorer

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

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

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

0 Karma

poojak2579
Explorer

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.

0 Karma

harishalipaka
Motivator

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
Thanks
Harish
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...