topic Re: Why can't I see stats values from a subsearch? in Splunk Search

Why can't I see stats values from a subsearch?

_dave_b — Fri, 20 Nov 2015 18:22:45 GMT

Hello. I want to extract timestamp data using stats list() and display that data as part of a larger search, so I run it inside of a subsearch.
If I run the search as the main search, like this:

"Connected" | stats list(date_hour) as myHour | eval myTime=mvindex(myHour, 5) | eval myDiff=myTime - 2 | table myDiff

it works fine, and displays a number.
When I run it as part of a subsearch, like this:

("This" OR "That") AND NOT ("Other" OR "Those") | join connectionId [ search "Connected" | stats list(date_hour) as myHour | eval myTime=mvindex(myHour, 5) | eval myDiff=myTime - 2 | fields myDiff] | table myDiff

Nothing is displayed.

The outer search works fine by itself, and the inner search works fine by itself.

What am I doing wrong?

Re: Why can't I see stats values from a subsearch?

Runals — Fri, 20 Nov 2015 19:34:27 GMT

You end your subsearch with | fields myDiff which is the only field that is passed to your main search. In your join though you are saying you want to link the data based on the field connectionID. At a minimum that field would need to be in the results of your subsearch.

One cool little trick Splunk has provided is adding the command | format to the end of a search. This will display results as if they were the results of a subsearch. Can make troubleshooting subsearches a bit easier.

Edit: Not quite sure what your use case is but based on the fields I'm wondering if you wanting to compare something to an average or something similar. I'm wondering if a command like eventstats or even just stats might be a better solution. Even a stats might work if you have a common field like connectionID across 2 different sourcetypes.

Re: Why can't I see stats values from a subsearch?

_dave_b — Fri, 20 Nov 2015 20:00:24 GMT

Thanks! My use case is calculating the difference in times to renew/renegotiate a wireless connection, and in doing so I'm trying to find the time of the most recent connection and the time of the connection before that. Initially I thought Delta could provide the capability to determine those times, but it doesn't seem suitable for my purposes, so I'm using stats list() of the connection times to access the time stamps and find the differences.

Re: Why can't I see stats values from a subsearch?

Runals — Fri, 20 Nov 2015 20:22:18 GMT

This might hold some answers; it seems like you want to know the time difference between two events not the time difference between the start and end of an event/action. If you just want to get something like the start and end of something you could use | stats range(_time) by connectionID or the transaction command creates a new field call duration (more expensive search). Otherwise I think you are likely going to need to use streamstats

https://answers.splunk.com/answers/129084/calculate-the-time-between-a-transaction-events-starttime-and-the-endtime-of-the-previous-event.html