How do I get all values from one index, but only the latest of one field from another index ?

[lassel]

I hope this is an easy question, but I can't figure out how to get this to work. I am still in a learning process.

To make it easier on you I changed my example to the splunk _internal and _audit tables.
But I had to add the username to the _internal sourcetype=splunk_web_access
It wont make a lot of sense, it is just an example.

index=_internal sourcetype=splunk_web_access | table _time, username, clientip
index=_audit search=* action=search info=granted | table _time, username, search

So what I want is to find display the all requests from a clientip along with the LATEST search that user made.
I need to join on username on both indexes and the for the _audit index find the nearest previous event that matches the timestamp on _internal.

I pseudo terms I want to subquery the audit table something like this:

_internal.username == _audit.username && _audit._time <= _internal.time && top 1 from _audit

Actually in what I am working on I am joining with a kv-lookup, but I can't find a way around getting a better correlation key than user and timestamp.

Hope you can help.

[aljohnson_splun]

I think you are really just wanting to use stats:

(index=_internal OR index=_audit) user=* earliest=-1h
| stats list(_raw) as requests, latest(search) as latest_search by user

Say that the user field in _internal was actually username_internal, you could use eval coalesce to make them a single field.

(index=_internal username_internal=*) OR (index=_audit user=*)  earliest=-1h
| eval user = coalesce(username_internal, user)
| stats list(_raw) as requests, latest(search) as latest_search by user

[aljohnson_splun]

note however that _raw is getting events from both indexes so I'm not sure if that is really what you want?

[lassel]

Thank you very much for your reply. I learned about stats and quite a few other things today, but I still can't get it to work ... and I feel like I have been though all permutations. Hope you can give it another shot.

In my dataset one side is a kvstore. But I can't get it to work with just indexes either.

Let me change the example a little, to make it easier to reproduce what I see:
Create a csv, with timestamp, username and bytes:

 index=_internal sourcetype=splunk_web_access username=* | fields _time, username, bytes | outputlookup createinapp=true request_bytes.csv

I can test that the request_bytes.csv looks good and works with stats:

|inputlookup request_bytes.csv | table _time, username, bytes | stats latest(bytes) by username

And this is what I want to enrich. I am not interested in data from both, just what I get from _audit and the bytes from lookup.

index=_audit search=* action=search info=granted | table _time, user, search

Join that with all searches and for each search show the bytes from latest previous request.
This is my best shot. It doesn't work, but I hope it shows the intent:

( index=_audit search=* action=search info=granted) OR ([|inputlookup request_bytes.csv]) | eval user = coalesce(user, username) | stats list(_raw) latest(bytes) as lb by user | table _time, user, search, bytes

Thank you

[lassel]

I rephrased the quetion in sql terms here:
http://answers.splunk.com/answers/228419/subselect-latest-value-from-lookup-releative-to-ev.html