Splunk Search

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

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

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

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

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

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

SplunkTrust
SplunkTrust
Please share the current query.
---
If this reply helps you, an upvote would be appreciated.
0 Karma