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
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 ...
I have used this in a epoch time in the datamodel as below :
if(isnull(strptime(ApprovalDate,"%d/%m/%y")), "", strptime(ApprovalDate,"%d/%m/%y"))
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 MAXDAYSAGO and MAXDAYSHENCE in props.conf (where you define your sourcetype)
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.
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 approveddate, in this case spanned to the month. Therefore, `dcplan` 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
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...