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

[oribit]

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?

[oribit]

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"

[to4kawa]

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"

[oribit]

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.

[oribit]

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.

[richgalloway]

Please share the current query.