Dashboards & Visualizations

How do you display columns with an inner join?

mmdacutanan
Explorer

I need to run a report that gives me phone numbers that appeared >=2 within the same minute and with the corresponding CallID and then I need to do an inner join to include another data called CV7. I was able to accomplish that with this query:

index=ABC sourcetype=ABC_MainReportLog  "Entered Phone Number" Phone!=1234567890
| dedup CallID
| table CallID Phone _time
| join type=inner CallID 
   [ search index=ABC sourcetype=ABC_core_MainReportLog "\|RemoteApplicationData\|" CV7=*
  | dedup CallID]
| fields Phone, State, CallID
| bucket _time span=1m
| stats count(CallID) as Count by _time Phone State
| where Count >=2

The query above gives me _time Phone State and Count. But I also need to display the CallID. How do I do that?

Thanks in advance!

Tags (1)
0 Karma

MuS
SplunkTrust
SplunkTrust

Hi mmdacutanan,

you last stats does not use CallID in any other way than the count, therefore you 'lose' it. Just add it to the stats either as values() or in the by clause:

index=ABC sourcetype=ABC_MainReportLog "Entered Phone Number" Phone!=1234567890
| dedup CallID
| table CallID Phone _time
| join type=inner CallID 
[ search index=ABC sourcetype=ABC_core_MainReportLog "\|RemoteApplicationData\|" CV7=*
| dedup CallID]
| fields Phone, CV7, CallID
| bucket _time span=1m
| stats count(CallID) as Count by _time Phone CV7 CallID
| where Count >=2

But you should not use join at all, for various reasons.

Try this instead:

index=ABC ( sourcetype=ABC_MainReportLog "Entered Phone Number" Phone!=1234567890 ) OR ( index=ABC sourcetype=ABC_core_MainReportLog "\|RemoteApplicationData\|" CV7=* )
| bucket _time span=1m
| stats count(sourcetype) AS count values(*) AS * by _time CallID
| where count = 2

This might need some modifications to match your events, but give a starting point. Also might be worth to read this answer https://answers.splunk.com/answers/129424/how-to-compare-fields-over-multiple-sourcetypes-without-jo...

Hope this helps ...

cheers, MuS

mmdacutanan
Explorer

Hi MuS,

Thank you for taking time to look at my question. For your first suggestion (still using join), that was what I was doing initially, I had CallID included in the 'stats count 'but it was still not giving me the CallID 😞 That was why I went ahead and posted my question here in Splunk Answers.

Your second suggestion does give me data including the CallID but output isn't quite right.

Maybe raw data will help??

Raw data for main search looks data below. The data before the first | delimiter is my CallID and the Phone is data after the second | delimiter. (I did field extractions so that CallID and Phone are automatically detected):

XXXXXXXYYYYYYYZZZZZZ|Entered Phone Number|2021212324|2018-09-26 18:57:12.000
ZZZZZMMMMMMTTTTTT|Entered Phone Number|3031323334|2018-09-26 18:57:11.531

Now I was attempting to join the main search with subsearch because I needed CV7. Raw Data for the join subsearch looks like data below. Again I did a field extraction to automatically extract CV7 which in the data below is called CALLVARIABLE7. Data before the first | delimiter is my CallID:

XXXXXXXYYYYYYYZZZZZZ|RemoteApplicationData|("CALLVARIABLE1"="","CALLVARIABLE2"="152574786091","CALLVARIABLE3"="2021212324,N,0000000,000,0","CALLVARIABLE4"="000,000,0,0000000,S,4,1,071011,N,0","CALLVARIABLE5"="Z1525740000786091","CALLVARIABLE6"="2021212324,8889991234,8889991234","CALLVARIABLE7"="L_SPEAK_FREELY","CALLVARIABLE8"="A,D,01,C,0,0,0,0,1,0,00,002,0,G,0,1,0,0","CALLVARIABLE9"="","CALLVARIABLE10"="N,,","APPLICATIONDATA"="10user.XfrReasonR10user.EndPtCodeX10user.LstPrmptPS139410user.ANIMatch`T")|2018-09-26 19:03:11.808

The end result really is I only want to see Phone CallID CV7 if count is >=2 (meaning phone # appeared more than once within the same minute).

Any suggestion is much appreciated!

0 Karma

mmdacutanan
Explorer

minor correction to the query:

index=ABC sourcetype=ABC_MainReportLog "Entered Phone Number" Phone!=1234567890
| dedup CallID
| table CallID Phone _time
| join type=inner CallID
[ search index=ABC sourcetype=ABC_core_MainReportLog "|RemoteApplicationData|" CV7=*
| dedup CallID]
| fields Phone, CV7, CallID
| bucket _time span=1m
| stats count(CallID) as Count by _time Phone CV7
| where Count >=2

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 ...