Splunk Search

Subsearch based on date

snowmizer
SplunkTrust
SplunkTrust

I'm new to creating subsearches. I need to combine fields from two different sourcetypes based on a date. Event one have a field "Modified time", event 2 is just using _time. I need to find event one and do a query for event 2 where _time="Modified time".

Can anyone give me a quick tip on how to do this?

Thanks

Tags (1)
1 Solution

Paolo_Prigione
Builder

Hi, I see a couple other choices depending on the "full" use-case. In both cases, you will first need to conver ModifiedTime to epoch (as nick said):

...| eval ModifiedTime=mktime(ModifiedTime)

then:

  1. use the "map" command

    .... | map search="search sourcetype::eventtwo starttimeu::$ModifiedTime$ endtimeu::$ModifiedTime$"

    You could also use other fields from event one to refine the search for event two.

  2. use the "join" command

    "remap" modifiedTime to _time on event one (and backup the original _time, if needed):

    *... | eval orig_time=_time | eval _time=ModifiedTime*

    then join on _time

    *... | join _time [search sourcetype::eventtwo ...]*

    This will add the fields from eventtwo to the results of eventone.

Good luck!

View solution in original post

Paolo_Prigione
Builder

Hi, I see a couple other choices depending on the "full" use-case. In both cases, you will first need to conver ModifiedTime to epoch (as nick said):

...| eval ModifiedTime=mktime(ModifiedTime)

then:

  1. use the "map" command

    .... | map search="search sourcetype::eventtwo starttimeu::$ModifiedTime$ endtimeu::$ModifiedTime$"

    You could also use other fields from event one to refine the search for event two.

  2. use the "join" command

    "remap" modifiedTime to _time on event one (and backup the original _time, if needed):

    *... | eval orig_time=_time | eval _time=ModifiedTime*

    then join on _time

    *... | join _time [search sourcetype::eventtwo ...]*

    This will add the fields from eventtwo to the results of eventone.

Good luck!

sideview
SplunkTrust
SplunkTrust

this may help point you in the right direction.
If you want to manually control what comes out of the subsearch you have to restrict to 1 row, restrict to 1 field in that row,

<search for event 2> earliest=[search <search for event 1> | head 1 | eval search=yourModifiedTimeField | fields search] 

That will search for event 1, and get the yourModifiedTimeField value, and then run the search for event 2 with earliest=123132841 or whatever the value of yourModifiedTimeField is.

NOTE: this example will only work as written if your 'modifiedTime' field is an epochTime value, ie number of seconds since 1/1/1970.
If its not then either:

a) you'll have to use the convert command, or the eval command with the new fancy time operators like ctime,strftime etc, to turn it into an epochTime value. (its not that bad once you get used to it)

b) Or if the field value is a string time and you dont feel like learning how to use convert/eval/etc you can specify a timeformat="%M/%D/%Y:%H:%M:%S" string, but it has to be an exact match, and the timeformat term (somewhat bizarrely) has to be before the earliest= or it wont work.

gkanapathy
Splunk Employee
Splunk Employee

I would recommend against the syntax earliest=[ ... | eval search=XXXX | fields search ] in favor of [ ... | eval earliest=XXXX | fields earliest] because the latter will work as expected even if you have more than one result from the subsearch.

0 Karma

Lowell
Super Champion

Also clarify if event 2 is the event coming from your subsearch? What do you want to do with your events that you've matched up? It's also possible that you don't need to use subsearch for this at all. But more specific information is needed. (Please be sure to use the "edit" link to add additional details to your existing question.)

0 Karma

bfaber
Communicator

Any chance you can give us some examples of the events you are searching over?

Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

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

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...