Splunk Search

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

fmatera
Explorer

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

Tags (2)
0 Karma

woodcock
Esteemed Legend

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 *
0 Karma

spavin
Path Finder

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.

fmatera
Explorer

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

0 Karma

spavin
Path Finder

Sure - update the last line to change it

from: | rename minid as id

to:

| table _time, did, callerid, extension_number

0 Karma
Get Updates on the Splunk Community!

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

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars this month. This ...

They're back! Join the SplunkTrust and MVP at .conf24

With our highly anticipated annual conference, .conf, comes the fez-wearers you can trust! The SplunkTrust, as ...

Enterprise Security Content Update (ESCU) | New Releases

Last month, the Splunk Threat Research Team had two releases of new security content via the Enterprise ...