Splunk Search

Combination of stat and lookup?

yuwtennis
Communicator

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

Tags (2)
0 Karma

somesoni2
Revered Legend

Try this

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

linu1988
Champion

Hello Yu,
Try the below one.

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

Thanks

0 Karma

linu1988
Champion

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

0 Karma

yuwtennis
Communicator

Hi linu1988.

Thank you for the reply.

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

0 Karma
Get Updates on the Splunk Community!

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...