How to use subquery values in a where clause?

[Staale]

Im trying to do this: 

aid=0 Overflowexception msg="Print completed" @t<first | [search Overflowexception | stats min(@t) as first by username]

which does not work. I want to find the first(min) time the users experiences an overflow, and find if ther has been a completed print before this first overflow. What am I doing wrong ?

I can do it manually like this:

1. aid=0 Overflowexception   --to find users that has gotten the error
2. aid=0 Overflowexception username=Staale | sort @t  --to find the first error for user
3. aid=0 username=Staale msg="Print completed" @t<2023-06-29T06:32:53.900387z  --time received from the above

But this is very timeconsuming and I want to do it all in one command. I usually uses SQL, so I guess my approach is to SQL like. Any ideas is appreciated.

-=Staale=

[PickleRick]

The answer to "what am I doing wrong" is you're thinking about this wrong 😉

Or rather - you're trying to implement SQL solution in SPL and that's not how it works. There is of course the map-based solution which @richgalloway already showed. But it 's again not very "splunky".

The splunk way to solve such problem would be to either aggregate and reorder the data properly with stats/streamstats/eventstats and then be a bit creative with conditions.

For example (the thing to remember here is that by default Splunk returns results in reverse chronological order; might be a bit syntactically incorrect in places since I'm writing it without splunk instance at hand):

1. You search for either overflows or completed prints and categorize the events

aid=0 (OverflowException OR msg="Print Completed")
| eval overflow=if(match(_raw,"%OverflowException%"),1,null())
| eval completed=if(msg="Print Completed",1,null())

2. Since normally Splunk sorts in reverse chronological order and you need to analyze your data in "direct" chronological order, you have to resort your events (if your @t contains different timestamp, you might want to reorder by that field instead of _time)

| sort _time

3. Now you mark all those events for which there was already a "Print Completed" message

| streamstats current=f last(completed) as wascompleted by username

 (if you expect more than one exception per username you could fiddle with reset_after)

4. And now you can find only those events where the exception actually happened

| where overflow=1

The "wascompleted" field - if present - will tell you that there was a "Print completed" event for that user before the overflow exception.

[richgalloway]

A subsearch is not unlike a macro - the contents are executed then the results replace the original text and are themselves executed.  That means the output of a subsearch must make sense on the context in which it is used.  So, if the subsearch follows a pipe then the results of the subsearch must be a valid SPL command.

Try this query

aid=0 Overflowexception 
``` Find the time of the first event for each user ```
| stats min(_time) as firstTime by username
``` Run a search for each username found above ```
| map maxsearches=0 search="aid=0 msg=\"Print completed\" _time<$firstTime$"

BTW, the subject of this post references the where command, but I don't see a where in the queries.

[Staale]

Thanx for your response, but when I try to run it, I get some errors  :

• if I use 'last 4 hours', I get 'The search result count(58) exceeds max (10), using max. 
• Unable to run 'aid=0 msg=Print'.

Any more ideas.

Thanx

[richgalloway]

I've corrected the query.  Please try again.

[Staale]

Sooo close 🙂

Now the error says 

Unable to run query 'aid=0 msg="Print completed"  _time<"1688126733.8056037"',

but this applies only to the first completed print, right? not pre username ?

[richgalloway]

Interesting that firstTime is quoted.  I don't see that in my tests.  Not sure what to do about that.