topic Re: Subsearch based on date in Splunk Search

Subsearch based on date

snowmizer — Tue, 29 Jun 2010 04:44:35 GMT

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

Re: Subsearch based on date

bfaber — Tue, 29 Jun 2010 06:39:24 GMT

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

Re: Subsearch based on date

Lowell — Wed, 30 Jun 2010 00:05:32 GMT

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.)

Re: Subsearch based on date

sideview — Wed, 30 Jun 2010 10:38:17 GMT

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.

Re: Subsearch based on date

Paolo_Prigione — Wed, 30 Jun 2010 23:29:05 GMT

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:

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.
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!

Re: Subsearch based on date

gkanapathy — Thu, 18 Nov 2010 21:34:24 GMT

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.