topic Re: Group my data per week in Splunk Search

Group my data per week

NicoloPunzalan2 — Tue, 29 Sep 2020 18:29:52 GMT

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

Re: Group my data per week

p_gurav — Thu, 15 Mar 2018 05:25:35 GMT

Can you try something like:

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

And then also group by week.

Re: Group my data per week

bangalorep — Thu, 15 Mar 2018 05:38:09 GMT

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!