Splunk Search

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

POR160893
Builder

Hi,

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

POR160893_0-1682073811895.png



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,

Labels (3)
0 Karma

yuanliu
SplunkTrust
SplunkTrust

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

 

0 Karma

POR160893
Builder

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 anyone know how to incorporate these conditions into a drilldown?

0 Karma

woodcock
Esteemed Legend

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

0 Karma

gcusello
SplunkTrust
SplunkTrust

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.

 

POR160893
Builder

Hi,

You query is giving the following:

POR160893_0-1682075314789.png



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?

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

POR160893
Builder

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?

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