Splunk Search

How to join 2 query from different sources?

auaave
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
1 Solution

MuS
Legend

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

MuS
Legend

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

0 Karma

MuS
Legend

Updated to get the READ/NOREAD counts for Sorter 😉

0 Karma

auaave
Communicator

This works perfectly! Thanks a lot @MuS

0 Karma

MuS
Legend

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

MuS
Legend

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

auaave
Communicator

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

0 Karma

MuS
Legend

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

auaave
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

MuS
Legend

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

cheers, MuS

0 Karma
Get Updates on the Splunk Community!

Announcing Scheduled Export GA for Dashboard Studio

We're excited to announce the general availability of Scheduled Export for Dashboard Studio. Starting in ...

Extending Observability Content to Splunk Cloud

Watch Now!   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to leverage ...

More Control Over Your Monitoring Costs with Archived Metrics GA in US-AWS!

What if there was a way you could keep all the metrics data you need while saving on storage costs?This is now ...