Splunk Search

_time not giving correct results in an eval condition

rijinc
Explorer

I am using the below query to show the number of plans in a particular month.

However, there are approved dates of future month like in this below query there are approved dates in October as well. I am using a streamstats to show the cumulative trend.

My issue here is when i am using _time=approved date i am not able to see the future months value . Its giving me all the results till today. however i have approved dates which are in oct , nov 2017 as well

*approved_date is converted in epoch time in the datamodel

Query:
index="some" | eval _time=Approved_Date 
|  timechart span=1mon dc(ID) as dc_plan 
| streamstats max(dc_plan) as cumulative 
| fields - dc_plan

Am I performing the right approach or is there any other way to show the cumulative view of the ID ?
Your help required here

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

Okay, your code doesn't look like it will give you the data that you seek.

dc(ID) is the count of distinct IDs that had a given approved_date, in this case spanned to the month. Therefore, dc_plan is the number of unique ID values that had an approved date in that month.

UNLESS your events are approved multiple times, and continue getting a record each month saying they have been approved that month, then | streamstats max(dc_plan) as cumulative makes no sense whatsoever - it is in no way a cumulative anything. It is just the highest number of distinct IDs that happened to be approved in any month up to now.

In order to straighten this out, we would need to know the structure of the underlying data.

Here's a quick guess at one way to make this work.

If each plan has one approve date, and you want a cumulative count of how many different IDs there have been at any given month, then here is one approach...

   index="some"

   | rename COMMENT as "bin the Approved_Date at the month level and assign to _time"
   | eval _time=relative_time(Approved_Date,"@mon")

   | rename COMMENT as "find the first Approved_Date for each ID"
   | stats min(_time) as Time by ID

   | rename COMMENT as "find all the different months in the data and create one record for each"
   | eventstats values(Time) as alltimes
   | mvexpand alltimes

   | rename COMMENT as "kill any months before this ID was first approved"
   | where _time <= alltimes

   | rename COMMENT as "move the months date to _time and then calculate the cumulative dc of IDs"
   | eval _time = alltimes
   | stats dc(ID) as dc_plan by _time

   | rename COMMENT as "present as a timechart"
   | timechart span=1m max(dc_plan) as dc_plan
0 Karma

rijinc
Explorer

Thanks for the update provided...

Even the above code limits to the data till August
_time is not giving me the results of future months like Sep , Oct etc...

0 Karma

hmuni
New Member

The search should have timeframe that goes till the future date. If you are using date and time range till future date then try the following:

Look at attributes MAX_DAYS_AGO and MAX_DAYS_HENCE in props.conf (where you define your sourcetype)
http://docs.splunk.com/Documentation/Splunk/6.4.0/Admin/Propsconf

 MAX_DAYS_AGO = <integer>
 * Specifies the maximum number of days past, from the current date, that an
   extracted date can be valid. Splunk still indexes events with dates older
   than MAX_DAYS_AGO with the timestamp of the last acceptable event. If no 
   such acceptable event exists, new events with timestamps older than MAX_DAYS_AGO 
   will use the current timestamp.
 * For example, if MAX_DAYS_AGO = 10, Splunk applies the timestamp of the last 
   acceptable event to events with extracted timestamps older than 10 days in 
   the past. If no acceptable event exists, Splunk applies the current timestamp.
 * Defaults to 2000 (days), maximum 10951.
 * IMPORTANT: If your data is older than 2000 days, increase this setting.

 MAX_DAYS_HENCE = <integer>
 * Specifies the maximum number of days in the future from the current date
   that an extracted date can be valid. Splunk still indexes events with dates 
   more than MAX_DAYS_HENCE in the future with the timestamp of the last acceptable 
   event. If no such acceptable event exists, new events with timestamps after 
   MAX_DAYS_HENCE will use the current timestamp.
 * For example, if MAX_DAYS_HENCE = 3, Splunk applies the timestamp of the last 
   acceptable event to events with extracted timestamps more than 3 days in the 
   future. If no acceptable event exists, Splunk applies the current timestamp.
 * The default value includes dates from one day in the future.
 * If your servers have the wrong date set or are in a timezone that is one
   day ahead, increase this value to at least 3.
 * Defaults to 2 (days), maximum 10950.
 * IMPORTANT: False positives are less likely with a tighter window, change
              with caution.
0 Karma

pradeepkumarg
Influencer

What is your time range for your search? Did you adjust your time picker to search for future events as well?

0 Karma

rijinc
Explorer

Its all time i have set currently

0 Karma

MuS
SplunkTrust
SplunkTrust

Is it approved_date or Approved_Date ? Also check if the value of the filed is numeric and not a string, other wise use strptime() http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/DateandTimeFunctions#strptime.28X... to read the string into a number.

Hope this helps ...

cheers, MuS

0 Karma

rijinc
Explorer

Its *Approved_date

I have used this in a epoch time in the datamodel as below :
if(isnull(strptime(Approval_Date,"%d/%m/%y")), "", strptime(Approval_Date,"%d/%m/%y"))

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...