topic Re: How to count by Week not using Splunk Timestamp in Splunk Search

How to count by Week not using Splunk Timestamp

mjd555 — Tue, 22 Dec 2015 12:12:15 GMT

Problem

I want to be able to create a timechart that outlines the company's incident count by week.

The issue I have is many incidents are created in one week but then resolved in the following week. That final event is then shown in the following weeks figures.

The way I have gotten around this before when searching a specific timeframe is by creating a start & end timestamp and having the Dates_Created field fall between the two times. However I am unsure how to use this in a week by week case.

Example:

| eval startstamp=strftime(relative_time(now(),"-mon@mon"),"%Y-%m-%d %H:%M:%S") 
| eval endstamp=strftime(relative_time(now(), "-1s"),"%Y-%m-%d %H:%M:%S")  
| where Dates_Created >= startstamp AND Dates_Created < endstamp

Query

This query currently shows me all events that have occurred on a week by week basis. However I want it to shows all tickets that were created (Dates_Created) on a week by week basis.

| index="Respond" sourcetype=Ticket queue="Incident"  earliest=-42d@d latest=now 
| dedup ticket   
| eval week_month=strftime(_time, "%V") 
| bucket span=7d _time
| chart count by week_month

Any help will be greatly appreciated

Re: How to count by Week not using Splunk Timestamp

HiroshiSatoh — Tue, 22 Dec 2015 12:56:18 GMT

Try This!

 index="Respond" sourcetype=Ticket queue="Incident" 
       [|gentimes start=-42|eval Dates_Created=strftime(starttime,"%Y-%m-%d*")|fields Dates_Created]
 | dedup ticket   
 | eval week_month=strftime(_time, "%V") 
 | bucket span=7d _time
 | chart count by week_month

※Date_Created is the field of string.

Re: How to count by Week not using Splunk Timestamp

mjd555 — Tue, 22 Dec 2015 13:06:16 GMT

Hello, I'm afraid that is still returning the same values as before

Re: How to count by Week not using Splunk Timestamp

jkat54 — Tue, 29 Sep 2020 08:16:19 GMT

It cant be this simple can it? Narrowing your search to just those that have Date_Created= (something):

 | index="Respond" sourcetype=Ticket queue="Incident"  earliest=-42d@d latest=now Date_Created=*
 | dedup ticket   
 | eval week_month=strftime(_time, "%V") 
 | bucket span=7d _time
 | chart count by week_month

If not, then I need an example of your Date_Created field data so that I can give you proper command. It will be something like this:

...| eval _time=strptime(Date_Created, "%+") | ... | timechart ... <- after the eval, _time will be Date_Created instead... and then when you feed it into your timechart, _time will still = Date_created.

Re: How to count by Week not using Splunk Timestamp

mjd555 — Tue, 22 Dec 2015 13:13:02 GMT

Afraid not as there is always data within the Dates_Created field. An example of data would be:

2015-12-11 04:58:19

The above ticket was created on this date, however it was resolved today so there was an event created on today's date.

Re: How to count by Week not using Splunk Timestamp

HiroshiSatoh — Tue, 22 Dec 2015 13:14:18 GMT

Make sure the search statement on the "Search job inspector".

Re: How to count by Week not using Splunk Timestamp

dcarmack_splunk — Tue, 22 Dec 2015 13:54:13 GMT

Is there any way to filter out completed/closed events? For example:

index="Respond" sourcetype=Ticket queue="Incident" status!="closed"

Re: How to count by Week not using Splunk Timestamp

dcarmack_splunk — Tue, 29 Sep 2020 08:12:35 GMT

why not just do

... | bucket span=7d _time | stats dc(ticket) AS ticket_count by _time

Re: How to count by Week not using Splunk Timestamp

woodcock — Tue, 22 Dec 2015 14:54:04 GMT

You can use the concurrency command and then count "concurrencies" at any given time:

http://docs.splunk.com/Documentation/Splunk/6.2.0/SearchReference/Concurrency