topic Re: compare the custom dates with the _time in Splunk Search

compare the custom dates with the _time

praveenkpatidar — Tue, 29 Sep 2020 09:06:15 GMT

Hello,

I have ticket data like below

ID Open_date Close_date
1 01/01/2016 02/01/2016
2 01/01/2016 01/01/2016
3 02/01/2016 03/01/2016
4 02/01/2016 03/01/2016
5 02/01/2016 03/01/2016
6 03/01/2016 04/01/2016

I want to populate the data on the basis of timeline.

If I choose Jan Month in timeline The open date and closed date should have compared with timeline and counts should be visible

Like for Jan Selection
Date Open Count Closed Count
1-Jan-16 2 1
2-Jan-16 3 1
3-Jan-16 1 3
4-Jan-16 0 1

Is it any way to do this I am literally searching all options. I have data model however any other approach is also welcomed.

Thanks
Praveen

Re: compare the custom dates with the _time

somesoni2 — Tue, 15 Mar 2016 14:53:46 GMT

The original data is in CSV or it's ingested in Splunk? If it's ingested, which column _time field is based on?

Re: compare the custom dates with the _time

praveenkpatidar — Tue, 15 Mar 2016 23:21:26 GMT

Thanks somesoni2
Sorry, I missed that to mention.

So _time is created date. So I guess the Open Count is now resolved. In the same way I have to do Closed Count.
All data is ingested in Splunk.

Re: compare the custom dates with the _time

praveenkpatidar — Tue, 29 Sep 2020 09:06:56 GMT

Some more progress on this.

Below both searches giving me results what I want. The only issue is joining the result into one result.
It should join all entities.

Closed Trend
index="idx" host=Incidents | eval _time = strptime ( resolved_at, "%d/%m/%Y %H:%M:%S") | chart count(number) as closed by _time bins=100

Open Trend
index="idx" host=Incidents | eval _time = strptime ( sys_created_on, "%d/%m/%Y %H:%M:%S") | chart count(number) as open by _time bins=100

Re: compare the custom dates with the _time

DMohn — Wed, 16 Mar 2016 08:59:46 GMT

The key to your success would be the streamstats command. I am not sure if it is the most pretty or effective solution, but it gives you the wanted results:

 your_base_search
   | streamstats count as opencount by Open_date
   | streamstats count as closecount by Close_date
   | timechart span=1d max(opencount) as "Tickets opened", max(closecount) as "Tickets Closed"

Your data needs to have the event timestamp as the creation day for this search to work, maybe you need to tune this a bit by adding an eval _time= .... to your search to achieve this.