Splunk Search

Showing in a table results missing in a query from a set

oribit
Observer

Hi,

I’m trying to perform a query in Splunk that not sure if it’s even possible… I have my query over data with a format like:

01 Jan aaa ...
02 Jan bbb ...
01 Jan ccc ...
02 Jan aaa ...

The query is extracting the value "aaa", "bbb", "ccc" into a field and taking the date of the last appearance. Then it's displayed using a table showing the letters and the date. The problem is that the possible values of the three letters would also contain "ddd" and "eee" (and it could be more or less) that they are not found in the query. So, I would like to add this "ddd" and "eee" in the table with date "never" (or some similar value). Would it be this possible?

Labels (3)
0 Karma

oribit
Observer

This is more or less the full query:

index=myindex source=mysource | search my_text | rex field=RawMsg "(?<TimeAndPlace>.*)\~(?<process>.*)" | rex field=TimeAndPlace "(?<place>[a-z]{3})" | stats latest(_time) as Latest by place | eval dayssince=floor((now() - Latest)/86400) | eval Latest=strftime(Latest,"%Y-%m-%dT%H:%M:%S") | table place,dayssince,Latest | rename dayssince AS "Days Since"

0 Karma

to4kawa
Ultra Champion

index=myindex source=mysource my_text | rex field=RawMsg "(?<TimeAndPlace>.*)\~(?<process>.*)" | rex field=TimeAndPlace "(?<place>[a-z]{3})" | stats latest(_time) as Latest by place | eval dayssince=floor((now() - Latest)/86400) | eval Latest=strftime(Latest,"%Y-%m-%dT%H:%M:%S") | table place,dayssince,Latest

|join place [|makeresult |eval place=split("aaa,bbb,ccc,ddd,eee",",") |mvexpand place |table place]|eval dayssince=if(isnull(dayssince),"missing", dayssince)| rename dayssince AS "Days Since"

0 Karma

oribit
Observer

Many thanks for the answer! I'm not sure if something wrong in our system, but the query is never finishing. I know that we have a huge amount of data, but with the original query after a few minutes I have the results, while the new one (after the join) it's getting stuck... although I don't understand why. It looks like everything after the join is happening over the results found, so it shouldn't take much more time.

0 Karma

oribit
Observer

So, it seems that the "join" is creating a lot of jobs, that they are executed really fast. I'm not an expert in Splunk at all, but I would expect that the subquery in the join would be executed only for each value found, that it should be only one per "place" (letters) because it's filtered by latest, but clearly my understanding is wrong.

0 Karma

richgalloway
SplunkTrust
SplunkTrust
Please share the current query.
---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

Get the T-shirt to Prove You Survived Splunk University Bootcamp

As if Splunk University, in Las Vegas, in-person, with three days of bootcamps and labs weren’t enough, now ...

Introducing the Splunk Community Dashboard Challenge!

Welcome to Splunk Community Dashboard Challenge! This is your chance to showcase your skills in creating ...

Wondering How to Build Resiliency in the Cloud?

IT leaders are choosing Splunk Cloud as an ideal cloud transformation platform to drive business resilience,  ...