topic Re: How do I dynamically set earliest from subsearch? in Splunk Search

How do I dynamically set earliest from subsearch?

richarddicaire — Wed, 13 Sep 2017 17:37:26 GMT

Hi folks, been all over this site and google, not finding a working solution.

I'm trying to perform a search using a subsearch to populate earliest=

| tstats min(_indextime) as firstTime, max(_indextime) as lastTime where earliest=[ | rest /services/admin/indexes splunk_server=localhost
| search title=syslog | eval dy = (frozenTimePeriodInSecs/86400) | eval earli="-" . dy . "d@d" |fields earli ] index=syslog by index
| eval delta = (lastTime - firstTime) | eval yr = floor(delta/86400/365)| eval dy = (delta/86400) % 365 | eval actual_ret = yr . " years, " . dy . " days"
| eval lastTime=strftime(lastTime,"%Y-%m-%d %H:%M:%S"), firstTime=strftime(firstTime,"%Y-%m-%d %H:%M:%S") | fields index, firstTime, lastTime, delta, actual_ret
| join index [| rest /services/admin/indexes splunk_server=localhost | eval yr = floor(frozenTimePeriodInSecs/86400/365)
| eval dy = (frozenTimePeriodInSecs/86400) % 365 | eval ret = yr . " years, " . dy . " days" | eval index=title
| stats avg(currentDBSizeMB) as currentDBSizeMB, avg(maxTotalDataSizeMB) as maxTotalDataSizeMB, max(frozenTimePeriodInSecs) as frozenTimePeriodInSecs, max(ret) by index
| eval pct_data=(currentDBSizeMB/maxTotalDataSizeMB) * 100] | eval pct_ret = (delta/frozenTimePeriodInSecs)*100

and get error

Invalid value "(" for time term 'earliest'

Also tried subsearch

earliest=[ | rest /services/admin/indexes splunk_server=localhost
| search title=syslog | eval dy = (frozenTimePeriodInSecs/86400) | fields dy ]

with same result. Both subsearches by themselves return correct results:

| rest /services/admin/indexes splunk_server=localhost
| search title=syslog | eval dy = (frozenTimePeriodInSecs/86400) | eval earli="-" . dy . "d@d" |fields earli

returns

-365d@d

and

| rest /services/admin/indexes splunk_server=localhost
| search title=syslog | eval dy = (frozenTimePeriodInSecs/86400) | fields dy

returns

365

How can I get the subsearch value to be used by "earliest="?

Re: How do I dynamically set earliest from subsearch?

DalJeanis — Wed, 13 Sep 2017 17:58:48 GMT

earliest has to be in either relative form, or in epoch form. Try this.,,,

 earliest=[ | rest /services/admin/indexes splunk_server=localhost
| search title=syslog 
| eval search = "-".tostring(floor(frozenTimePeriodInSecs/86400))."d@d" | table search]

NOTE - When you get to the end of a subsearch, the subsearch will return either whatever is in the field search. If there is no such field, the subsearch will implicitly invoke the format command and return whatever fields (field1, field2...) and values are in each record in the format...

( ( field1="value1fromrecord1" AND field2="value2fromrecord1"... ) OR  ( field1="value1fromrecord2" AND field2="value2fromrecord2"... )  OR ...) )

... which in your case, with one field named dy and a value we'll call 1234, meant it returned...

( ( dy="1234" ) )

Re: How do I dynamically set earliest from subsearch?

richarddicaire — Wed, 13 Sep 2017 18:22:35 GMT

This worked. It looks like it does the same thing as my first subsearch attempt, how do these differ?

Re: How do I dynamically set earliest from subsearch?

DalJeanis — Wed, 13 Sep 2017 18:35:39 GMT

@richarddicaire - I already explained that after the horizontal bar. You can play with the format command at the end of any search to see what exactly will be returned. In this specific case, your original code returned something that looked like ( ( dy="1234" ) ) hence, the message that "(" was an invalid value.

Re: How do I dynamically set earliest from subsearch?

richarddicaire — Wed, 13 Sep 2017 18:48:07 GMT

I understood that, thats the result returned from my second subsearch attempt, I was asking about the difference between between your working result, and my first subsearch attempt.

After checking with format, I'm seeing my first subsearch returned ( ( earli="-365d@d" ) )

Thanks for the explanation, and using format as well.

Re: How do I dynamically set earliest from subsearch?

DalJeanis — Wed, 13 Sep 2017 22:10:52 GMT

@richarddicaire - ah, I misunderstood. Yes, format is great to play with. I very often end up using format with unique arguments, then using | rex field=search mode=sed "some rex" to change the results in field search to exactly what I need to return.