Splunk Search

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

wu_weidong
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:

CountryLastWeekThisWeekPercentChange
US10100
Japan54-20
Australia4525
China20-100
Canada02400

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)
0 Karma
1 Solution

rnowitzki
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 country = case(ID=1,"US",ID=2,"Australia",ID=3,"Japan",ID=4,"China",ID=5,"Canada",ID=6,"US",ID=7,"Australia",ID=8,"Japan",ID=9,"China",ID=10,"Canada")
| 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")
| transpose header_field=Period
| 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. 

map_percchange.PNG

 

Hope it helps.
BR
Ralph

--
Karma and/or Solution tagging appreciated.

--
Karma and/or Solution tagging appreciated.

View solution in original post

rnowitzki
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 country = case(ID=1,"US",ID=2,"Australia",ID=3,"Japan",ID=4,"China",ID=5,"Canada",ID=6,"US",ID=7,"Australia",ID=8,"Japan",ID=9,"China",ID=10,"Canada")
| 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")
| transpose header_field=Period
| 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. 

map_percchange.PNG

 

Hope it helps.
BR
Ralph

--
Karma and/or Solution tagging appreciated.

--
Karma and/or Solution tagging appreciated.

wu_weidong
Path Finder

Awesome!  I had to change 

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

to 

| timechart span=7d count as EventCount by country

 Otherwise, it worked great! Thanks!

0 Karma