topic Re: How to join 2 query from different sources? in Splunk Search

How to join 2 query from different sources?

auaave — Wed, 14 Feb 2018 23:44:57 GMT

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]

Re: How to join 2 query from different sources?

MuS — Thu, 15 Feb 2018 00:20:39 GMT

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]

Re: How to join 2 query from different sources?

auaave — Thu, 15 Feb 2018 00:28:51 GMT

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

Re: How to join 2 query from different sources?

MuS — Thu, 15 Feb 2018 00:48:43 GMT

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?

Re: How to join 2 query from different sources?

auaave — Thu, 15 Feb 2018 01:18:19 GMT

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

Re: How to join 2 query from different sources?

MuS — Thu, 15 Feb 2018 01:44:31 GMT

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)

Re: How to join 2 query from different sources?

auaave — Thu, 15 Feb 2018 02:13:27 GMT

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

Re: How to join 2 query from different sources?

MuS — Thu, 15 Feb 2018 19:07:13 GMT

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

cheers, MuS

Re: How to join 2 query from different sources?

MuS — Sun, 18 Feb 2018 19:57:10 GMT

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

Re: How to join 2 query from different sources?

auaave — Tue, 29 Sep 2020 18:08:12 GMT

@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(BIT_FLAGS=0001 OR BIT_FLAGS=0004 OR BIT_FLAGS=0009)) AS READ count(eval(BIT_FLAGS=0002 )) AS "NOREAD" by LOCATION

Re: How to join 2 query from different sources?

MuS — Mon, 19 Feb 2018 18:05:22 GMT

Updated to get the READ/NOREAD counts for Sorter 😉

Re: How to join 2 query from different sources?

auaave — Tue, 20 Feb 2018 01:15:21 GMT

This works perfectly! Thanks a lot @MuS