topic Re: Search without join for multiple index in Splunk Search

Search without join for multiple index

vikashperiwal — Thu, 31 Oct 2019 17:21:12 GMT

Hi,

I have a requirement where I have 2 Index, I want to display the raw data, Below is the query I tried but I am not able to show complete data.

Fields A B C D E F G H I J L M P Q R S T U V W X Y Z A1 A2 A3 A4 A5 are not seen in a table from index= abc, guide me if there is some wrong with my approach

( index=abc sourcetype=ISUP_EVENT_ACCESS_VW earliest=-8h@h latest=-4h@h ) OR (index=csvlookups source="*SWITCH*") 
| fields - _raw 
| eval N=coalesce(N,OPC) 
| eval O=coalesce(O,DPC) 
| eval K=coalesce(K,CIC) 
| search N=* AND O=* AND K=* 
| eventstats values(OPC) as OPC values(DPC) as DPC values(CLLI) as CLLI values(ADMININF) as ADMININF values(ADNUM) as ADNUM values(TRKGRSIZ) as TRKGRSIZ values(NETNAME) as NETNAME values(SWITCH) as SWITCH values(MEMNAME) as MEMNAME values(RTESET) as RTESET values(CIC) as CIC by N O K 
| eval Call="Outbound" 
| fields **A B C D E F G H I J L M P Q R S T U V W X Y Z A1 A2 A3 A4 A5** N OPC O DPC K CIC CLLI ADMININF ADNUM TRKGRSIZ NETNAME SWITCH MEMNAME RTESET Call 
| table **A B C D E F G H I J L M P Q R S T U V W X Y Z** A1 A2 A3 A4 A5 N OPC O DPC K CIC CLLI ADMININF ADNUM TRKGRSIZ NETNAME SWITCH MEMNAME RTESET Call 
| search OPC=* AND DPC=* 
| rename K as "TCIC" N as "Originating_Point_Code" O as "Destination_Point_Code"

Re: Search without join for multiple index

gcusello — Thu, 31 Oct 2019 17:27:44 GMT

Hi vikashperiwal,
why do you used fields - _raw?
in this way you haven't che content of event's any more!

Ciao.
Giuseppe

Re: Search without join for multiple index

vikashperiwal — Fri, 01 Nov 2019 04:35:05 GMT

the only reason i included fields - _raw is because my query is getting auto finalize because of large number of events

Re: Search without join for multiple index

vikashperiwal — Fri, 01 Nov 2019 04:43:22 GMT

even after removing fields - _raw and kepping earliest-1 hr , i am having same issue ...not able to show fields A B C D E F G H I J L M P Q R S T U V W X Y Z A1 A2 A3 A4 A5 along with other fields

Re: Search without join for multiple index

gcusello — Fri, 01 Nov 2019 09:24:25 GMT

Hi vikashperiwal,,
at first which Mode are you using? try with Verbose.

If you simplify your search in this way you still haven't fields?

(index=abc sourcetype=ISUP_EVENT_ACCESS_VW earliest=-8h@h latest=-4h@h) OR (index=csvlookups source="*SWITCH*") 
| eval N=coalesce(N,OPC), O=coalesce(O,DPC), K=coalesce(K,CIC) 
| search N=* O=* K=* 
| eventstats values(OPC) as OPC values(DPC) as DPC values(CLLI) as CLLI values(ADMININF) as ADMININF values(ADNUM) as ADNUM values(TRKGRSIZ) as TRKGRSIZ values(NETNAME) as NETNAME values(SWITCH) as SWITCH values(MEMNAME) as MEMNAME values(RTESET) as RTESET values(CIC) as CIC by N O K 
| eval Call="Outbound" 
| table A B C D E F G H I J L M P Q R S T U V W X Y Z A1 A2 A3 A4 A5 N OPC O DPC K CIC CLLI ADMININF ADNUM TRKGRSIZ NETNAME SWITCH MEMNAME RTESET Call 
 | search OPC=* DPC=* 
 | rename K as "TCIC" N as "Originating_Point_Code" O as "Destination_Point_Code"

Ciao.
Giuseppe

Re: Search without join for multiple index

vikashperiwal — Fri, 01 Nov 2019 10:58:47 GMT

Thanks for your time and effort .. Its really appreciated

This is query is working for me now.... will ask the user to validate the data.

Re: Search without join for multiple index

vharshit — Wed, 06 Nov 2019 10:44:12 GMT

When we run the query in fast mode for different time ranges(say Last 24 hours, All Time), it returns different results everytime. Is it some bug?