topic Re: Unable to change bar chart to ONLY show the number of events for just the previous business quarter in Splunk Search

Unable to change bar chart to ONLY show the number of events for just the previous business quarter

POR160893 — Fri, 21 Apr 2023 10:46:24 GMT

Hi,

I currently has a barchart like this which shows the number of requests per business quarter:

Here is the respective query:
index=servicenow sourcetype="snow:sc_task" dv_assignment_group="SECURITY-NETWORK-L3" description="Request for Dell firewall changes."
| stats latest(*) as * by dv_parent
| eval _time = strptime(dv_sys_updated_on, "%Y-%m-%d")
| eval Quarter=strftime(_time,"%Y" . "Q" . ceil((tonumber(strftime(_time,"%m"))+1)/4))
| stats count by Quarter

I need to alter this query to ONLY show the previous quarter, i.e. FY23Q4, After 1 week from today, the next quarter will start, so the bar chart will change to ONLY FY24Q1. Can you please me with this updated query?

Many thanks,

Re: Unable to change bar chart to ONLY show the number of events for just the previous business quarter

gcusello — Fri, 21 Apr 2023 10:59:58 GMT

Hi @POR160893,

add to your main search

index=servicenow sourcetype="snow:sc_task" dv_assignment_group="SECURITY-NETWORK-L3" description="Request for Dell firewall changes." earliest=-3mon@mon latest=@mon | stats latest(*) as * by dv_parent | eval _time = strptime(dv_sys_updated_on, "%Y-%m-%d") | eval Quarter=strftime(_time,"%Y" . "Q" . ceil((tonumber(strftime(_time,"%m"))+1)/4)) | stats count by Quarter

Ciao.

Giuseppe

even if you don't nell more the division by quarter but you can only use stats count.

Re: Unable to change bar chart to ONLY show the number of events for just the previous business quarter

POR160893 — Fri, 21 Apr 2023 11:09:08 GMT

Hi,

You query is giving the following:

It should be given FY23Q4 as that was the previous quarter for out financial year. We are CURRENTLY in FY24Q1 but we are interested in just the previous quarter.

Can you please help?

Re: Unable to change bar chart to ONLY show the number of events for just the previous business quarter

gcusello — Fri, 21 Apr 2023 14:24:15 GMT

Hi @POR160893,

sorry but i don't understand: we ae in april, so the 2023Q2, so the previous quarter is 2023Q1, what's the problem?

There could be a problme e.g. in May because using my time frame it takes the three previous months that I suppose isn't your need, so please try this:

index=servicenow sourcetype="snow:sc_task" dv_assignment_group="SECURITY-NETWORK-L3" description="Request for Dell firewall changes." earliest=-6mon@mon latest=@mon | stats latest(*) as * by dv_parent | eval _time = strptime(dv_sys_updated_on, "%Y-%m-%d") | eval Quarter=strftime(_time,"%Y" . "Q" . ceil((tonumber(strftime(_time,"%m"))+1)/4)) | stats count by Quarter

Ciao.

Giuseppe

Re: Unable to change bar chart to ONLY show the number of events for just the previous business quarter

woodcock — Fri, 21 Apr 2023 14:24:32 GMT

Like this:

index=servicenow sourcetype="snow:sc_task" dv_assignment_group="SECURITY-NETWORK-L3" description="Request for Dell firewall changes."
| eval _time = strptime(dv_sys_updated_on, "%Y-%m-%d")
| bin _time span=1q
| where _time = relative_time(now(), "@q-1q")
| eval Quarter=strftime(_time,"%Y" . "Q" . ceil((tonumber(strftime(_time,"%m"))+1)/4))
| stats count by Quarter

Re: Unable to change bar chart to ONLY show the number of events for just the previous business quarter

POR160893 — Sat, 22 Apr 2023 19:09:50 GMT

So, the financial year my employer is in is FY24, i,e, 2024. This began at the start of March as that was end of the previous financial year. So, right now, we are in FY24Q1. That will become FY24Q2 at the start of June and FY24Q3 at the start of September and FY23Q4 at the start of December.

The bar chart in question needs to show the number of FCR's for the PREVIOUS quarter ONLY.

Does that answer your question?

Re: Unable to change bar chart to ONLY show the number of events for just the previous business quarter

POR160893 — Sun, 23 Apr 2023 12:40:51 GMT

Re: Unable to change bar chart to ONLY show the number of events for just the previous business quarter

yuanliu — Sun, 23 Apr 2023 20:58:33 GMT

Not clear why you cannot simply exclude stats from current quarter. This way, you only have previous quarter in stats. Like this

index=servicenow sourcetype="snow:sc_task" dv_assignment_group="SECURITY-NETWORK-L3" description="Request for Dell firewall changes." | stats latest(*) as * by dv_parent | eval _time = strptime(dv_sys_updated_on, "%Y-%m-%d") | eval Quarter=strftime(_time,"%Y" . "Q" . ceil((tonumber(strftime(_time,"%m"))+1)/4)) | where Quarter != strftime(now(),"%Y" . "Q" . ceil((tonumber(strftime(now(),"%m"))+1)/4)) | stats count by Quarter