How to join 2 query from different sources?

[auaave]

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]

[MuS · Accepted solution]

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

[auaave]

@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

[MuS]

Updated to get the READ/NOREAD counts for Sorter 😉

[auaave]

This works perfectly! Thanks a lot @MuS

[MuS]

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]

[auaave]

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

[MuS]

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?

[auaave]

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

[MuS]

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)

[auaave]

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

[MuS]

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

cheers, MuS