topic How to create a new Quarter field with specific conditions? in Splunk Search

How to create a new Quarter field with specific conditions?

POR160893 — Thu, 27 Apr 2023 13:48:09 GMT

Hi,

I need to set a condition in Splunk for how the business quarters are set up in my place of work.

In my job, the new financial year starts each year on February 1st.

The 1st quarter is between February to April inclusive, the 2nd quarter is May to July inclusive, the 3rd quarter is August to October inclusive, and the 4th quarter is November to January inclusive.

Currently I have this Splunk query:
index=_internal earliest=-1y latest=now
| eval month=strftime(_time, "%m")
| eval quarter = case(
month>=2 AND month<=4, "Q1",
month>=5 AND month<=7, "Q2",
month>=8 AND month<=10, "Q3",
month>=11 OR month<=1, "Q4"
)
| eval year = if(month>=2, strftime("%y", relative_time(now(), "@y")+1."y"), strftime("%y", now()))
| eval quarter = "FY" . year . quarter

However, I am receiving the following error:
Error in 'eval' command: The expression is malformed. Expected ).

How can I set a new field called "Quarter" with this information this in a Splunk command or query?

Many thanks as always!

Re: Create a new Quarter field with specific conditions

rut — Thu, 27 Apr 2023 12:22:21 GMT

@POR160893I think you were almost there.

| eval year = if(month>=2, strftime(relative_time(now(), "@y+1y"), "%y"), strftime(now(), "%y")) | eval quarter = "FY" . year . quarter

Changes:

Combined the "@y+1y" into a single string (that was the error)
Switched arguments in strftime (first the date, then the format)

Re: Create a new Quarter field with specific conditions

gcusello — Thu, 27 Apr 2023 12:23:41 GMT

Hi @POR160893,

there's an error in the strftime definition: you must use field, format not format,field.

In other words, please, try this:

| eval year = if(month>=2, strftime(relative_time(now(), "@y")+1,"%y"), strftime(now(),"%y"))

Ciao.

Giuseppe