topic Re: How to search for two source types, each in different time ranges without using join or a subsearch? in Splunk Search

How to search for two source types, each in different time ranges without using join or a subsearch?

pradeepkumarg — Thu, 25 Sep 2014 17:25:25 GMT

In my below query, I want to load sourcetypeA for last 13 weeks, however I want to restrict sourcetypeB for last 7 days without using earliest

The below trick now()-_time is not working for me. I'm getting " Comparator '<' has an invalid term on the left hand side. " error

index=my_index (sourcetype=sourcetypeA  AND FILE_ID=100002 ) OR (sourcetype=sourcetypeB AND ((now()-_time)<691220) )

I don't want to filter after the base query, as the data in sourcetypeB is very huge and is drastically hindering the performance of the query

Using the second query (sourcetypeB) as sub query or Join is not an option currently for me

Is there a way I can achieve this?

Thanks,
Pradeep

Re: How to search for two source types, each in different time ranges without using join or a subsearch?

ppablo — Thu, 25 Sep 2014 17:40:10 GMT

Hi @gpradeepkumarreddy

I know you said you want a solution without earliest, but I thought this post might be helpful with your desired result. Would this work for you?

http://answers.splunk.com/answers/153336/is-it-possible-to-use-earliest-twice-in-one-search.html

Re: How to search for two source types, each in different time ranges without using join or a subsearch?

pradeepkumarg — Thu, 25 Sep 2014 17:57:05 GMT

Thanks much Pat. I never thought it would work and didn't give it a try. Looks like its working 🙂

You can convert this to answer, I'll accept it.

Re: How to search for two source types, each in different time ranges without using join or a subsearch?

ppablo — Thu, 25 Sep 2014 17:59:52 GMT

No problem Pradeep! I saw that post the other day and bookmarked it since I thought it would be helpful for other people. It came in handy much sooner than expected 🙂

Patrick

Re: How to search for two source types, each in different time ranges without using join or a subsearch?

gkanapathy — Fri, 26 Sep 2014 01:39:20 GMT

This is a situation where multisearch will be useful:

| multisearch [ search sourcetype=sourceA FileID=XYZABC ] [ search sourcetype=sourceB earliest=-7d ]

Re: How to search for two source types, each in different time ranges without using join or a subsearch?

gkanapathy — Fri, 26 Sep 2014 01:48:59 GMT

And it's useful because of the multisearches will search only over the specified time ranges. If you use earliest/latest in a single base search more than once, you will have to scan the span of the widest time range of all the clauses. This may or may not make a big difference depending on your searches and data distribution.

Re: How to search for two source types, each in different time ranges without using join or a subsearch?

MuS — Fri, 26 Sep 2014 05:40:26 GMT

Just a point to add here:
Searching each time range separately has the earliest and latest times set correctly, but searching them with an OR in between made it so that it windowed the search by the range of the time picker in the search bar. So if that were set to, say, "All Time," it would search over the entire contents of your sourcetypes just to pull out data between those two date ranges. By the same token, if it were set to "Today", it would cut off entries outside of today and give you an incomplete answer. (And if it were set to something that didn't overlap with either of the date ranges in the search, it would give you an error.)

Re: How to search for two source types, each in different time ranges without using join or a subsearch?

gkanapathy — Fri, 26 Sep 2014 06:51:26 GMT

This is why I recommend multisearch as a better solution.

Re: How to search for two source types, each in different time ranges without using join or a subsearch?

ppablo — Mon, 29 Sep 2014 16:49:00 GMT

Awesome, thanks for this answer and explanation @gkanapathy. I'll definitely recommend this search command to folks with similar use cases from now on.

Re: How to search for two source types, each in different time ranges without using join or a subsearch?

jeffland — Thu, 01 Dec 2016 09:56:33 GMT

Just for clarification (I misunderstood the answer until I double checked what happens), the searches will only restrict to explicitly given time ranges (via earliest and latest) and otherwise use the widest possible time range. For example, doing this:

| multisearch [search a] [search b earliest=-7d@d latest=-6d@d]

with a global timespan of "Today" will not restrict search a to "Today". Instead, search a will run from -7d@d up to now (search b will use the explicit time range given). So to use multisearch correctly, you should probably always define earliest and latest per search.