Reporting

How to rename multiple fields dynamically?

jasmartin
Explorer

Hello, I've been asked to create a report that will show the number of events from the 2 previous quarters by country, the monthly average, and the quarterly percent increase:

Country Q1'22 Total Q1'22 Monthly Avg Q2'22 Total Q2'22 Monthly Avg Q2'22 Percent Increase
US 300000 100000 330000 110000 10%
UK 60000 20000 61000 20333 2%
Canada 1200 400 1500 500 25%

 

Using this:

 

 

index=mydata earliest=-2q@q latest=-q@q 
| chart dc(ID) as count_earlier by Country 
| appendcols 
    [ search index=mydata earliest=-q@q latest=@q
    | chart dc(ID) as count_later by Country] 
| eval ave_earlier=round(count_earlier/3,0) 
| eval ave_later=round(count_later/3,0) 
| eval DiffPer=round(((count_later - count_earlier) / count_earlier) * 100,0)."%" 
| table ReportersCountry,count_earlier,ave_earlier,count_later,ave_later,DiffPer

 

 

Now I'm trying to rename count_earlier, ave_earlier, count_later, and ave_later to be the quarter labels. I've been using:

 

 

| convert TIMEFORMAT="%m" ctime(_time) AS month 
| rex field=date_year "\d{2}(?<short_year>\d{2})"
| eval quarter=case(month<=3,"Q1",month<=6,"Q2",month<=9,"Q3",month<=12,"Q4",1=1,"missing")."'".short_year

 

 

And have been trying to use eval {} to rename the columns but haven't quite figured it out.

I also tried using chart which allows me to get the quarter headers, but then I couldn't figure out how to calculate the percent difference column. 

Thanks for any help in advance!

0 Karma

jasmartin
Explorer

This is the other query I'm trying, but the DiffPerc field is not showing up:

index=mydata earliest=-2q@q latest=-q@q 
| dedup ID 
| convert TIMEFORMAT="%m" ctime(_time) AS month 
| rex field=date_year "\d{2}(?<short_year>\d{2})" 
| eval quarter=case(month<=3,"Q1",month<=6,"Q2",month<=9,"Q3",month<=12,"Q4",1=1,"missing")."'".short_year 
| chart count(ID) as count_earlier by Country, quarter 
| appendcols 
    [ search index=mydata earliest=-q@q latest=@q 
    | dedup ID 
    | convert TIMEFORMAT="%m" ctime(_time) AS month 
    | rex field=date_year "\d{2}(?<short_year>\d{2})" 
    | eval quarter=case(month<=3,"Q1",month<=6,"Q2",month<=9,"Q3",month<=12,"Q4",1=1,"missing")."'".short_year 
    | chart count(ID) as count_later by Country, quarter]
    | eval DiffPer=round(((count_later - count_earlier) / count_earlier) * 100,0)."%"
0 Karma

FelixLeh
Contributor

Try using the chart command over Country by Quarter to show the individual count of quarters!

your search
| eval Month = strftime(_time,"%m")
| eval Quarter = case(Month<=3,"Quarter 1",Month<=6,"Quarter 2",Month<=9,"Quarter 3",Month<=12,"Quarter 4")
| chart count over Country by Quarter

Hope this helps!

_______________________________________

If this was helpful please consider awarding Karma. Thx!

0 Karma

jasmartin
Explorer

Thanks! That's basically was what I was starting with, but I didn't know how to calculate the percentage increase column after that since the quarter columns would be dynamically added.

0 Karma

FelixLeh
Contributor

How about this? I add the Quarter including the year in the initial stats function and later add it with a . into the rename.

| rename ORIGINAL_FIELD as QUARTER_VALUE." STRING VALUE"

This way the Quarter Value changes depending on the input of the search.

Example:

index=mydata earliest=-2q@q latest=-q@q
| eval Month = strftime(_time,"%m")
| eval Year = strftime(_time,"%Y")
| eval Quarter = case(Month<=3,"1 ".Year,Month<=6,"2 ".Year,Month<=9,"3 ".Year,Month<=12,"4 ".Year)
| stats dc(ID) as count_earlier values(Quarter) as Quarter by Country
| appendcols
[ search index=mydata earliest=-q@q latest=@q
| eval Month = strftime(_time,"%m")
| eval Year = strftime(_time,"%Y")
| eval Quarter = case(Month<=3,"1 ".Year,Month<=6,"2 ".Year,Month<=9,"3 ".Year,Month<=12,"4 ".Year)
| stats dc(ID) as count_later values(Quarter) as Quarter by Country]
| eval ave_earlier=round(count_earlier/3,0)
| eval ave_later=round(count_later/3,0)
| eval DiffPer=round(((count_later - count_earlier) / count_earlier) * 100,0)."%"
| table ReportersCountry,count_earlier,ave_earlier,count_later,ave_later,DiffPer,Quarter
| rename count_earlier as Quarter." Total", ave_earlier as Quarter." Monthly Avg",count_later as Quarter." Total", ave_later as Quarter." Monthly Avg"


Is this what you were looking for?

_______________________________________

If this was helpful please consider awarding Karma. Thx!

 

0 Karma

jasmartin
Explorer

Unfortunately, the rename doesn't replace Quarter with the value, that's where I was trying to use eval {} to make a field name based on the contents of another field.

0 Karma

FelixLeh
Contributor

I realised my Quarter eval had an error. I tested it again. Try this instead:

| eval Year = strftime(_time,"%Y")
| eval Month = strftime(_time,"%m")
| rex field=Year "..(?<year_short>..)"
| eval Quarter = case(Month<=3,"Q1'",Month<=6,"Q2'",Month<=9,"Q3'",Month<=12,"Q4'"),Quarter=Quarter.year_short

 

New Complete Example:

index=mydata earliest=-2q@q latest=-q@q
| eval Year = strftime(_time,"%Y")
| eval Month = strftime(_time,"%m")
| rex field=Year "..(?<year_short>..)"
| eval Quarter = case(Month<=3,"Q1'",Month<=6,"Q2'",Month<=9,"Q3'",Month<=12,"Q4'"),Quarter=Quarter.year_short
| stats dc(ID) as count_earlier values(Quarter) as Quarter by Country
| appendcols
[ search index=mydata earliest=-q@q latest=@q
| eval Year = strftime(_time,"%Y")
| eval Month = strftime(_time,"%m")
| rex field=Year "..(?<year_short>..)"
| eval Quarter = case(Month<=3,"Q1'",Month<=6,"Q2'",Month<=9,"Q3'",Month<=12,"Q4'"),Quarter=Quarter.year_short
| stats dc(ID) as count_later values(Quarter) as Quarter by Country]
| eval ave_earlier=round(count_earlier/3,0)
| eval ave_later=round(count_later/3,0)
| eval DiffPer=round(((count_later - count_earlier) / count_earlier) * 100,0)."%"
| table ReportersCountry,count_earlier,ave_earlier,count_later,ave_later,DiffPer,Quarter
| rename count_earlier as Quarter." Total", ave_earlier as Quarter." Monthly Avg",count_later as Quarter." Total", ave_later as Quarter." Monthly Avg"

0 Karma

FelixLeh
Contributor

I somehow didn't see your mention that you already tried that, my bad! I'm interested in answering this but have no answer for now. 

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud’s AI Assistant in Action Series: Analyzing and ...

This is the second post in our Splunk Observability Cloud’s AI Assistant in Action series, in which we look at ...

Elevate Your Organization with Splunk’s Next Platform Evolution

 Thursday, July 10, 2025  |  11AM PDT / 2PM EDT Whether you're managing complex deployments or looking to ...

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...