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

MuS
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

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

Updated to get the READ/NOREAD counts for Sorter 😉

0 Karma

auaave
Communicator

This works perfectly! Thanks a lot @MuS

0 Karma

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

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

auaave
Communicator

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

0 Karma

MuS
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

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

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

cheers, MuS

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...