Splunk Search
Highlighted

How to join 2 query from different sources?

Communicator

Hi Guys,

I have 2 queries that I have to combine. I haven't done this before and I'm really struggling. 😞
1st query: coming from 2 sources with the same value of Locations
2nd query: from 3rd source with different data set
I used |append to combine the results of my 2 queries but it's not working.

Based on my below query, I would like to have this result:
Location-------READ---NOREAD--READ%
ASRS------------100-------0------------100%
Conveyors------100-------0------------100%
Sorter------------100-------0------------100%

Thank you!

index="main" source="FC_READ" OR source="FC_NOREAD"
| eval LOCATION=case(like(LOCATIONTEXT,"%RS%"),"RS", like(LOCATIONTEXT,"%Induct%") or like(LOCATIONTEXT,"%Receiving%"),"Conveyors") 
| chart count(eval(source="FC_READ")) AS READ, count(eval(source="FC_NOREAD")) AS NOREAD by LOCATION 
| eval "READ%"=round(READ/(READ+NOREAD)*100,2) 
| append 
    [|makeresults |search source="DEST_MSG" LOCATION=0001 
    | stats count(eval(BIT=0001 OR BIT=0004 OR BIT=0009)) AS READ count(eval(BIT=0002 )) AS "NOREAD" by LOCATION 
    | eval "READ%"=round(((READ*100)/(READ+NOREAD)),2) 
    | replace "0001" with SORTER]
0 Karma
Highlighted

Re: How to join 2 query from different sources?

SplunkTrust
SplunkTrust

why is there a makeresults in the second query

| append 
     [**|makeresults** |search source="DEST_MSG" LOCATION=0001 
     | stats count(eval(BIT=0001 OR BIT=0004 OR BIT=0009)) AS READ count(eval(BIT=0002 )) AS "NOREAD" by LOCATION 
     | eval "READ%"=round(((READ*100)/(READ+NOREAD)),2) 
     | replace "0001" with SORTER]
Highlighted

Re: How to join 2 query from different sources?

Communicator

@MuS, I was just trying to see if adding make results would show the results of the second query. Anyway, with or without "makeresults" the result of my second query doesn't show. 😞

0 Karma
Highlighted

Re: How to join 2 query from different sources?

SplunkTrust
SplunkTrust

Makeresults simply creates an event for you, nothing more.

Okay baby steps first:
Does the second search

search source="DEST_MSG" LOCATION=0001 
  | stats count(eval(BIT=0001 OR BIT=0004 OR BIT=0009)) AS READ count(eval(BIT=0002 )) AS "NOREAD" by LOCATION 

return anything?

0 Karma
Highlighted

Re: How to join 2 query from different sources?

Communicator

@MuS, ok, thanks for the info. Yes, the second query returns the data of the sorter.

0 Karma
Highlighted

Re: How to join 2 query from different sources?

SplunkTrust
SplunkTrust

okay give this a try:

index="main" ( source="FC_READ" OR source="FC_NOREAD" ) ( source="DEST_MSG" LOCATION=0001 ) 
| eval LOCATION=case(like(LOCATIONTEXT,"%RS%"),"RS", like(LOCATIONTEXT,"%Induct%") or like(LOCATIONTEXT,"%Receiving%"),"Conveyors") 
| stats count(eval(source="FC_READ" OR (BIT=0001 OR BIT=0004 OR BIT=0009))) AS READ, count(eval(source="FC_NOREAD" OR BIT=0002)) AS NOREAD by LOCATION 
| eval "READ%"=round(READ/(READ+NOREAD)*100,2), LOCATION=if(LOCATION="0001", "SORTER", LOCATION)
0 Karma
Highlighted

Re: How to join 2 query from different sources?

Communicator

@MuS, I tried it but it still doesn't return the result of the sorter.
I added "OR" between the 2 groups of sources.

0 Karma
Highlighted

Re: How to join 2 query from different sources?

SplunkTrust
SplunkTrust

@auaave, let's take that offline - I will contact you.

cheers, MuS

0 Karma
Highlighted

Re: How to join 2 query from different sources?

SplunkTrust
SplunkTrust

Hi auaave,

after indexing your csv I was able to get it working with this SPL:

Your base search here to get all events
| eval LOCATION=case(like(REQLOCATIONTEXT,"%ASRS%"),"ASRS", like(REQLOCATIONTEXT,"%Induct%") or like(REQLOCATIONTEXT,"%Receiving%"),"Conveyors", LOCATION="1", "Sorter", isnotnull(LOCATION), LOCATION, 1=1, "unknonw") 
| stats count(eval(source="mfc_read.csv" OR (BIT_FLAGS="1" OR BIT_FLAGS="4" OR BIT_FLAGS="9"))) AS READ, count(eval(source="mfc_noread.csv" OR (BIT_FLAGS="2"))) AS NOREAD by LOCATION 
| eval "READ%"=round(READ/(READ+NOREAD)*100,2)

The problem was that we lost LOCATION=1 after the first stats, so I extended the first eval and it worked 😉

Hope this helps and all suitcases are tracked correctly now 🙂

cheers, MuS

View solution in original post

Highlighted

Re: How to join 2 query from different sources?

Communicator

@MuS, thanks a lot for your reply. The query was able to add the row for the sorter, the only problem is, it's not counting the "READ" and "NOREAD" events, instead it counted all the events under "READ" field. I can't insert the below codes to the query to count the "READ" and "NOREAD" events.

| stats count(eval(BITFLAGS=0001 OR BITFLAGS=0004 OR BITFLAGS=0009)) AS READ count(eval(BITFLAGS=0002 )) AS "NOREAD" by LOCATION

0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.