How to rename multiple fields dynamically?

[jasmartin]

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!

[jasmartin]

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)."%"

[FelixLeh]

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!

[jasmartin]

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.

[FelixLeh]

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!

[jasmartin]

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.

[FelixLeh]

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"

[FelixLeh]

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.