Splunk Search

## Optimizing search to display week-over-week percentage change of event count

Path Finder

Hi all,

I'm trying to display a week-over-week percentage change of event count collected for various countries, and display that percentage on the choropleth map.

• The time window is a rolling window of the past 7 days (including today) compared with the 7 days before that, so it's not always fixed to start on a Sunday.
• I also have to dedup `fieldA` and `fieldB` for each of the 7 days, i.e. if fieldA=5, fieldB=6 appears twice in each of the two 7-day period in the same country, I would do a `dedup` and the event would be counted once for both 7-day periods

For example:

 Country LastWeek ThisWeek PercentChange US 10 10 0 Japan 5 4 -20 Australia 4 5 25 China 2 0 -100 Canada 0 2 400

For the Canada case, I'm dividing it by 1 instead of 0 to calculate PercentChange, because I still want to show that there is a change in the number of events.

I would then display the Percentage change values on the map over the respective countries.

The query I have is

index=test earliest=-6d@d latest=now()
| bin _time span=7d
| dedup _time fieldA fieldB country
| stats count as ThisWeek by country
| append [search index=test earliest=-13d@d latest=-6d@d
| bin _time span=7d
| dedup _time fieldA fieldB country
| stats count as LastWeek by country ]
| stats values(*) as * by country
| eval LastWeek=if(isnotnull(LastWeek), LastWeek, 0)
| eval ThisWeek=if(isnotnull(ThisWeek), ThisWeek, 0)
| eval divideBy=if(LastWeek=0, 1, LastWeek)
| eval PercentChange=round((ThisWeek - LastWeek)/divideBy * 100, 2)
| stats values(PercentChange) as Percentage_Change by country
| geom geo_countries featureIdField=country

I'm getting the map to display the values, however, my query takes a long time to parse and run. Can I optimize it to make it run faster?

Thank you!

Labels (1)
• ### stats

1 Solution
Builder

Hi @wu_weidong ,

I came up with something that seems to work.

| makeresults
| eval ID = "1 2 3 4 5 6 7 8 9 10"
| makemv ID
| mvexpand ID
| eval count = case(ID=1,"10",ID=2,"4",ID=3,"5",ID=4,"0",ID=5,"4",ID=6,"10",ID=7,"5",ID=8,"4",ID=9,"2",ID=10,"0")
| eval _time = case(ID=1,_time,ID=2,_time,ID=3,_time,ID=4,_time,ID=5,_time,ID=6,_time-700000,ID=7,_time-700000,ID=8,_time-700000,ID=9,_time-700000,ID=10,_time-700000)

| timechart span=7d sum(count) as EventCount by country
| eval Period=if(_time < relative_time(now(), "-7d@d"), "LastWeek", "ThisWeek")
| eval PercentChange= if(LastWeek!=0,(ThisWeek-LastWeek)/LastWeek*100,ThisWeek*100)
| where column!="_time" AND column!="_span" AND column!="_spandays"
| rename column as country
| fields - LastWeek, ThisWeek
| geom geo_countries featureIdField=country

The first 7 rows are just to create some sample data that I could work with.
You need the stuff beginning with the timechart command.

I used transpose instead of streamstats. so that you can work with the LastWeek and ThisWeek columns to calculate the PercentChange.

You will have to play around with the coloring of the map.
Maybe make the ones where the LastWeek is 0 default to 100, so you have an expected range (-100 to +100) that you can work with.

Hope it helps.
BR
Ralph

--
Karma and/or Solution tagging appreciated.

--
Karma and/or Solution tagging appreciated.
Builder

Hi @wu_weidong ,

I came up with something that seems to work.

| makeresults
| eval ID = "1 2 3 4 5 6 7 8 9 10"
| makemv ID
| mvexpand ID
| eval count = case(ID=1,"10",ID=2,"4",ID=3,"5",ID=4,"0",ID=5,"4",ID=6,"10",ID=7,"5",ID=8,"4",ID=9,"2",ID=10,"0")
| eval _time = case(ID=1,_time,ID=2,_time,ID=3,_time,ID=4,_time,ID=5,_time,ID=6,_time-700000,ID=7,_time-700000,ID=8,_time-700000,ID=9,_time-700000,ID=10,_time-700000)

| timechart span=7d sum(count) as EventCount by country
| eval Period=if(_time < relative_time(now(), "-7d@d"), "LastWeek", "ThisWeek")
| eval PercentChange= if(LastWeek!=0,(ThisWeek-LastWeek)/LastWeek*100,ThisWeek*100)
| where column!="_time" AND column!="_span" AND column!="_spandays"
| rename column as country
| fields - LastWeek, ThisWeek
| geom geo_countries featureIdField=country

The first 7 rows are just to create some sample data that I could work with.
You need the stuff beginning with the timechart command.

I used transpose instead of streamstats. so that you can work with the LastWeek and ThisWeek columns to calculate the PercentChange.

You will have to play around with the coloring of the map.
Maybe make the ones where the LastWeek is 0 default to 100, so you have an expected range (-100 to +100) that you can work with.

Hope it helps.
BR
Ralph

--
Karma and/or Solution tagging appreciated.

--
Karma and/or Solution tagging appreciated.
Path Finder

| timechart span=7d sum(count) as EventCount by country

to

| timechart span=7d count as EventCount by country

Otherwise, it worked great! Thanks!

Get Updates on the Splunk Community!