Splunk Search

How to count by Week not using Splunk Timestamp

mjd555
Path Finder

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

0 Karma

woodcock
Esteemed Legend

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

0 Karma

dcarmack_splunk
Splunk Employee
Splunk Employee

why not just do

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

0 Karma

dcarmack_splunk
Splunk Employee
Splunk Employee

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

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

jkat54
SplunkTrust
SplunkTrust

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.

0 Karma

mjd555
Path Finder

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.

0 Karma

HiroshiSatoh
Champion

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.

0 Karma

mjd555
Path Finder

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

0 Karma

HiroshiSatoh
Champion

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

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 ...