Splunk Search

chain 2 search queries and get the earliest and latest of different fields

known_user
Engager

search string1 - [ field1 ]
search string2 [ field1 field2]
search string3 [ field1 field2]

I want the results of search string 1 to be matched with search string 2 by the common field (which is field 1) and the results of this to be matched with search string 3 where the common field is field 2, then I want to get those results as output with the earliest of field 1 and latest of field 2.
I've tried the subsearch command with join but it doesn't generate the required results. Also tried append.

Please help!

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

It's impossible to tell you how to do this efficiently without knowing more about your data. There are several strategies that would work well, in various situations.

When you say "the earliest of field 1" or "the latest of field 2", you have hinted that there is something other than the field value itself that you need. What is that?

How many records of each type are expected? What is the ratio, what is the relationship? Are they in the same or different indexes and source types?

Assuming that this were the searches for the three different types of records...

   index=foo1 "search terms 1"
   | fields _time index key1 data1  

   index=foo2 "search terms 2"
   | fields _time index key1 key3 data2

   index=foo3 "search terms 3"
   | fields _time index key3 data3

...then you could do something like this...

   (index=foo1 "search terms 1") OR
   (index=foo2 "search terms 2") OR
   (index=foo3 "search terms 3")
   | fields _time index key1 key3 data1 data2 data 3

   | rename COMMENT as "find the _time of the earliest and latest records for each key1 value on each index."
   | eventstats min(_time) as mintime1 max(_time) as maxtime1 by index key1
   | where (index="foo1" AND _time=mintime1) OR (index="foo2" AND _time=maxtime1) OR index="foo3"

   | rename COMMENT as "copy the value of data3 from search 3 onto the search2 record"
   | eventstats latest(data3) as data3 by key3

   | rename COMMENT as "copy the value of data1 from search 1 onto the search2 record.  (there is only one data1 value)"
   | eventstats earliest(data1) as data1 by key1

   | rename COMMENT as "get rid of all but the type 2 record that now has all the data."
   | where index="foo2"

Instead of index, you might use source or sourcetype, or something else. Ideally, right about the place where the fields command is, you narrow it down to a single record type field so that a single simple condition will differentiate between the records.

DalJeanis
SplunkTrust
SplunkTrust

If you gave examples of the various kinds of records, this would be easier to understand. Your choice of square braces around the data has a specific meaning in splunk, which is not what you intend.

0 Karma