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
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
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...
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.
What is your time range for your search? Did you adjust your time picker to search for future events as well?
Its all time i have set currently
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
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"))