Splunk Search

Group my data per week

NicoloPunzalan2
Engager

Hi All,

I am currently having trouble in grouping my data per week. My search is currently configured to be in a relative time range (3 months ago), connected to service now and the date that I use is on the field opened_at. Only data that has a date in its opened_at within 3 months ago should only be fetched. I had successfully grouped them by month and year but I am having trouble in grouping them per week. Is it possible to group them by week? Please see details below. Thank you.

Current setup of the table.
Month No. of SRs No. of INCs Total
Dec/2017 172 99 271

Jan/2018 543 243 788

Feb/2018 439 213 654

Mar/2018 160 105 265

My current query:
...search|
| eval created_at=strptime(opened_at,"%d/%m/%Y %H:%M:%S")
| eval Month=strftime(created_at,"%b/%Y")
| eval date_month=strftime(created_at,"%m")
| eval date_year=strftime(created_at,"%Y")
| eval epoch = strptime(opened_at,"%d/%m/%Y %H:%M:%S")
| eval diff = (now() - epoch)/60
| where diff < 131400
| stats count(eval(u_service_req="true")) as "Number of SRs", count(eval(u_service_req!="true")) as "No. of Incidents", count(ticket_number) as "Total" by Month date_month date_year
| sort+ date_year date_month | fields- date_year date_month

Tags (3)
0 Karma
1 Solution

p_gurav
Champion

Can you try something like:

| eval week=strftime(created_at, "%Y-%U")

And then also group by week.

View solution in original post

0 Karma

bangalorep
Communicator

Hello!
Try this run anywhere command

| makeresults 
| eval opened_at = "9/01/2018 12:23:35" 
| eval created_at=strptime(opened_at,"%d/%m/%Y %H:%M:%S") 
| eval Month=strftime(created_at,"%b/%Y") 
| eval date_month=strftime(created_at,"%m") 
| eval date_year=strftime(created_at,"%Y") 
| eval epoch = strptime(opened_at,"%d/%m/%Y %H:%M:%S") 
| eval week=strftime(created_at, "%w") 
| eval diff = (now() - epoch)/60 
| where diff < 131400 
| stats count(eval(u_service_req="true")) as "Number of SRs", count(eval(u_service_req!="true")) as "No. of Incidents", count(ticket_number) as "Total" by week Month date_month date_year 
| sort+ date_year date_month 
| fields- date_year date_month

Hope this helps!

0 Karma

p_gurav
Champion

Can you try something like:

| eval week=strftime(created_at, "%Y-%U")

And then also group by week.

0 Karma
Get Updates on the Splunk Community!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...