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!

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