correlated subsearch

[composite]

Hi,
I'm looking for a way to do an equivalent of a SQL correlated subquery in Splunk. [I did look at Splunk for SQL users - it doesn't have an answer].

I have events of the form: _time user=A type=B count=N. I'd like to express the following SQL in Splunk:
Select type, sum(count) from T
where _time=(Select max(_time) from T as T' where T'.type=T.type and T'.user=T.user)
group by type
where T._time + 24h < current_time()

Is there a way to do this?

Thank you

[sideview · Accepted solution]

I'm no master of correlated subqueries, but this might be it.

I'm reading your SQL as saying "find me most recent entries in T for each combination of type and user, and give me the type and the sum of the count field from those entries. Then group these by type and show me just the rows earlier than 24 hours ago.

... latest=-24h | eventstats max(_time) as maxTime by user type | where _time=maxTime | stats sum(count) as count by type

If my interpretation is wrong (as it very well may be) can you write it out in english?