topic Re: Extract fields from multiple events based off of min( id ) and max(id) by a common field. in Splunk Search

Extract fields from multiple events based off of min( id ) and max(id) by a common field.

fmatera — Sat, 02 Mar 2019 16:53:18 GMT

I would like to extract the time, did, and callerid from the event with the min(id) by apiid
Additionally, extract extension_number from max(id) by apiid
sample data:

_time                    id             apiid            did           callerid  extension_number
2019-03-02 09:42:05 533481479   vejf7t2gehtq3cttuml4    18008112345 10118997050 11111103003
2019-03-02 09:42:05 533481480   vejf7t2gehtq3cttuml4    18008112345 10118997050 11111103003
2019-03-02 09:42:05 533481481   vejf7t2gehtq3cttuml4    18008112345 10118997050 11111102000
2019-03-02 09:59:34 533489343   a54pdykd7ytwjbmmo7yi    18008112345 10008556468 11111103003
2019-03-02 09:59:34 533489344   a54pdykd7ytwjbmmo7yi    18008112345 10008556468 11111102000
2019-03-02 09:59:34 533489345   a54pdykd7ytwjbmmo7yi    18008112345 10008556468 11111103003
2019-03-02 09:59:54 533489611   a54pdykd7ytwjbmmo7yi    18008112345 10008556468 11111103003
2019-03-02 09:59:54 533489612   a54pdykd7ytwjbmmo7yi    18008112345 10008556468 11111103003
2019-03-02 09:59:54 533489613   a54pdykd7ytwjbmmo7yi    18008112345 10008556468 11111100063
2019-03-02 09:59:55 533489614   a54pdykd7ytwjbmmo7yi    18008112345 10008556468 11111100063
2019-03-02 10:00:23 533489836   4tvdwnrwenhw73k4ivbu    18008112345 18086965170 11111103003
2019-03-02 10:00:23 533489837   4tvdwnrwenhw73k4ivbu    18008112345 18086965170 11111102000
2019-03-02 10:00:23 533489838   4tvdwnrwenhw73k4ivbu    18008112345 18086965170 11111103003
2019-03-02 10:00:43 533489949   4tvdwnrwenhw73k4ivbu    18008112345 18086965170 11111103003
2019-03-02 10:00:43 533489950   4tvdwnrwenhw73k4ivbu    18008112345 18086965170 11111103003
2019-03-02 10:00:43 533489951   4tvdwnrwenhw73k4ivbu    18008112345 18086965170 11111100063
2019-03-02 10:00:43 533489952   4tvdwnrwenhw73k4ivbu    18008112345 18086965170 11111100063

I have been able to extract min and max by apiid with
base search | stats max(id) as maxid min(id) as minid by apiid

and also tried this so those values would not dispaly
base search | eventstats max(id) as maxid min(id) as minid by apiid

I have been able to get the min(id) values but not sure how to combine those with max(id) fields I am looking for

base search| eventstats max(id) as maxid min(id) as minid by apiid
| where id=minid | table _time,did, callerid

Re: Extract fields from multiple events based off of min( id ) and max(id) by a common field.

spavin — Sun, 03 Mar 2019 00:59:14 GMT

Hi @fmatera,

Try the following:

base search...
| eventstats max(id) as maxid min(id) as minid by apiid 
| stats values(eval(if(id==minid, _time, NULL))) as _time, 
    values(did) as did,
    values(callerid) as callerid,
    values(eval(if(id==maxid, extension_number, NULL))) as extension_number
    by minid
| rename minid as id

This search uses stats to get all the values from the event that has id=minid, and the extension number from the event that has id=maxid.

Re: Extract fields from multiple events based off of min( id ) and max(id) by a common field.

fmatera — Mon, 04 Mar 2019 12:17:00 GMT

Thanks, @spavin, that worked well. Is there any way to exclude minid from the results table?

Re: Extract fields from multiple events based off of min( id ) and max(id) by a common field.

spavin — Tue, 29 Sep 2020 23:29:24 GMT

Sure - update the last line to change it

from: | rename minid as id

to:

| table _time, did, callerid, extension_number

Re: Extract fields from multiple events based off of min( id ) and max(id) by a common field.

woodcock — Tue, 12 Nov 2019 02:17:19 GMT

Like this:

|makeresults | eval _raw=" _time                     id                apiid             did           callerid     extension_number
2019-03-02 09:42:05    533481479    vejf7t2gehtq3cttuml4    18008112345    10118997050    11111103003
2019-03-02 09:42:05    533481480    vejf7t2gehtq3cttuml4    18008112345    10118997050    11111103003
2019-03-02 09:42:05    533481481    vejf7t2gehtq3cttuml4    18008112345    10118997050    11111102000
2019-03-02 09:59:34    533489343    a54pdykd7ytwjbmmo7yi    18008112345    10008556468    11111103003
2019-03-02 09:59:34    533489344    a54pdykd7ytwjbmmo7yi    18008112345    10008556468    11111102000
2019-03-02 09:59:34    533489345    a54pdykd7ytwjbmmo7yi    18008112345    10008556468    11111103003
2019-03-02 09:59:54    533489611    a54pdykd7ytwjbmmo7yi    18008112345    10008556468    11111103003
2019-03-02 09:59:54    533489612    a54pdykd7ytwjbmmo7yi    18008112345    10008556468    11111103003
2019-03-02 09:59:54    533489613    a54pdykd7ytwjbmmo7yi    18008112345    10008556468    11111100063
2019-03-02 09:59:55    533489614    a54pdykd7ytwjbmmo7yi    18008112345    10008556468    11111100063
2019-03-02 10:00:23    533489836    4tvdwnrwenhw73k4ivbu    18008112345    18086965170    11111103003
2019-03-02 10:00:23    533489837    4tvdwnrwenhw73k4ivbu    18008112345    18086965170    11111102000
2019-03-02 10:00:23    533489838    4tvdwnrwenhw73k4ivbu    18008112345    18086965170    11111103003
2019-03-02 10:00:43    533489949    4tvdwnrwenhw73k4ivbu    18008112345    18086965170    11111103003
2019-03-02 10:00:43    533489950    4tvdwnrwenhw73k4ivbu    18008112345    18086965170    11111103003
2019-03-02 10:00:43    533489951    4tvdwnrwenhw73k4ivbu    18008112345    18086965170    11111100063
2019-03-02 10:00:43    533489952    4tvdwnrwenhw73k4ivbu    18008112345    18086965170    11111100063"
| multikv forceheader=1 copyattrs=f
| eval _time = strptime(time, "%Y-%m-%d %H:%M:%S")

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"

| eventstats min(id) AS min_id max(id) AS max_id
| multireport
[where id == min_id
| table time, did, and callerid]
[where id == max_id 
| table extension_number ]
| stats values(*) AS *