Filter event data using conditions within the data

[subhrangshu]

Hello,

I have some data which in the below form:

JOB	EVENT	TYPE	TIME
1	1	A	20
1	1	B	15
1	1	C	10
1	2	A	15
1	2	B	10
1	2	C	20

I want to filter the data only for those event which has the greater value of Type A.

So, here in my example, Event 1 has value of A=20 and Event 2 has value of A=15. So, here Event 1 has value of A greater than value of A in Event 2. So I want to see the results of Event 1 only. My result should be something like below:

JOB-NO	EVENT	TYPE	TIME
1	1	A	20
1	1	B	15
1	1	C	10

Thanks in advance.

[richgalloway · Accepted solution]

A subsearch should do the job.  The subsearch looks for the highest value of A and returns the event number.  Then the main search returns the results with that event number.

index=foo [ index=foo TYPE="A" 
  | search TYPE="A" 
  | stats max(TIME) as MAXTIME by EVENT
  | sort - MAXTIME
  | head 1
  | return EVENT
  ]

[subhrangshu]

Thanks @richgalloway 

Your solution works as I wanted. Just one more add on query to it, when we return EVENT, suppose I want to use the EVENT value in some other way in my main search. For example:

If event =1, then my main search should be something like index=FOO AND source=some path\1\log.txt

If event =2, then my main search should be something like index=FOO AND source=some path\2\log.txt

Basically, I want to use the event value returned into my main search with some modification as stated above.

Thanks again.

[richgalloway]

That changes the subsearch slightly.

index=foo [ index=foo TYPE="A" 
  | search TYPE="A" 
  | stats max(TIME) as MAXTIME by EVENT
  | sort - MAXTIME
  | head 1
  | eval source=case(EVENT=1,"some path", EVENT=2, "some other path", 1==1, "*")
  | return source
  ]

[isoutamo]

Hi

you can use 

<your base query>
| stats values(job) as job values(event) as event max(time) as time by type
| table job event type time

r. Ismo