Reporting

percent change over time

Menorel
Loves-to-Learn

Hello All,

Been trying to get the hang of syntax within Splunk and have been able to sus out a basic understanding, true to form for myself, I usually end up jumping into the deep end when I do things, so bear with me.

I am attempting to creat a report/search/dashboard that looks over the last four hours and will display the largest percent increase of a value.

The field is BIN currently stored as a numerical value, I have tried the tostring command to transform it but usually ends up as no values being returned or them all being grouped together.

But I digress, how would I first create a search/table view that would be updated along a described timeframe lets say every hour where it looks at the previous timeframe as a percentage of total records for that timeframe and calculates the percentage increase of the two timeframes and filters to see the top 20 increases?

Example:

I would want to ignore any decreasing values and possibly only see the top 20 that had increased that are greater than or equal to a 15% increase.

 

BINPercent 2 hour agoPercent 1 hr agoPecent change
12345610%12%16.7%
23456110%8%-25%
34561230%25%-20%
45612335%30%-16.7%
56123415%

25%

40%

 

Labels (1)
0 Karma

bowesmana
SplunkTrust
SplunkTrust

Here's an example search that will probably do what you want. It's using sourcetype from _internal index to do the search, but you should replace the index query and sourcetype to be your index search criteria and BIN.

index=_internal earliest=-2h@h latest=@h
| bin _time span=1h
| stats count by _time sourcetype
| eventstats sum(count) as total by _time
| eval percent=round(count/total*100, 2)
| chart values(percent) as p over _time by sourcetype
| streamstats c
| fields - _time t
| transpose 0 header_field=c
| eval diff='2'-'1'
| where !isnull(diff)
| eval Change=round(diff / '1' * 100, 2)
| rename "1" as "Previous 2hr", "2" as "Previous 1hr"
| fields - diff
| sort - Change
| where Change>15

 Keep earliest and latest time in search. This does

  1. Counts event numbers by sourcetype in 1 hour intervals (your BIN)
  2. Counts total event per hour
  3. Works out percentage of total events for each BIN
  4. charts the values (to a table) - you will have two rows now
  5. Numbers the rows (streamstats)
  6. Transposes table
  7. Calculates difference in -2 hours and -1 hours
  8. Calculates change in event count ratio
  9. Renames and filters

Note that this is calculating the percentages of events as a ratio of total, which if one bin number goes down the ratio of other bins will go up even if the bin counts do not, so may not be what you want, but hopefully gives you some tools to play with.

 

0 Karma

Menorel
Loves-to-Learn

@bowesmana 

I updated the Index with mine I am not sure about what the sourcetype would be.  in the searches that I was attempting to put together, I was using data=data=billing_card but replacing sourcetype with billing_card or setting it equal to sourcetype it retrieves no data.

I redacted the last two lines of code to see if there just was not anything meeting those criteria for the search and I get:

columnPrevious 2hrPrevious 1hrChangediff
NULL100.00100.000.000.00

 

If I just leave sourcetype in I get I am guessing the sources

 

0 Karma

bowesmana
SplunkTrust
SplunkTrust

I mentioned you need to replace sourcetype with your BIN - as that is what you are grouping by - sourcetype was only an example. Your original post shows the metrics by BIN, so that is what you need to split by.

 

0 Karma

PickleRick
Ultra Champion

You can fiddle with timechart and transposing result table.

0 Karma
*NEW* Splunk Love Promo!
Snag a $25 Visa Gift Card for Giving Your Review!

It's another Splunk Love Special! For a limited time, you can review one of our select Splunk products through Gartner Peer Insights and receive a $25 Visa gift card!

Review:





Or Learn More in Our Blog >>