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
Career Survey
First 500 qualified respondents will receive a $20 gift card! Tell us about your professional Splunk journey.

Can’t make it to .conf25? Join us online!

Get Updates on the Splunk Community!

Community Content Calendar, September edition

Welcome to another insightful post from our Community Content Calendar! We're thrilled to continue bringing ...

Splunkbase Unveils New App Listing Management Public Preview

Splunkbase Unveils New App Listing Management Public PreviewWe're thrilled to announce the public preview of ...

Leveraging Automated Threat Analysis Across the Splunk Ecosystem

Are you leveraging automation to its fullest potential in your threat detection strategy?Our upcoming Security ...