Combination of stat and lookup?

[yuwtennis]

Hi!

I would like have some advice with the search command.

If I have 3 records like below and each record contains some fields A,B

_time A B
5/13/2014 05:00 5/13/2014 00:00 4
5/13/2014 01:00 5/13/2014 03:00 3
5/11/2014 00:00 5/12/2014 01:00 2

Above records is in summary index.

My goal is to search 3 of field B where the time of field A is the latest.
And want to implement this in search command.

I know I can do this by changing the _time to A
So,

index=xxxx
| eval _time = strptime(xxxxxx)
| stats latest(B)

But I already have a stat using the current _time

index=xxxx
| stats sum(B)

Somehow I want to combine two stat in very effective manner.
So if something like vlookup of excel is possible it would be easy but its not possible.

index=xxxx
| stat latest(A)
| lookup [latest(A)] OUTPUT field B <- Dont want to create the lookup table.

Any idea of how to implement this with splunk ver 5.0.5?

Thanks,
Yu

[somesoni2]

Try this

index=xxxxx | eventstats latest(A) as latestA | where A=latestA

[linu1988]

Hello Yu,
Try the below one.

index=xxxx [|search index=xxxx | stat latest(A) as A]|table _time,B

Thanks

[linu1988]

As i see you

"Dont want to create the lookup table.". And even you run on million records there will be only one value which is the latest A. And as i see you require the A from dynamic set of record not static lookup will help. You may need to calculate the latest A on basis of day or hour and use |outputlookup and append the values to lookup file. Then you use the inputlookup to find B. or you may also MAP command to achieve the same

[yuwtennis]

Hi linu1988.

Thank you for the reply.

Since the I am trying to execute this for millions of event,
subsearch is not good.