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

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, an upvote would be appreciated.
0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!