Splunk Search

How to calculate percent increase of crime by month over 6 years?

KassandraI
Engager

alt text

Hello, I'm new to Splunk and would appreciate any help.
I am trying to figure out what month had the largest percent increase in crime from 2011-2017.
I was able to list all the values out and add the totals but I'm not sure where to go from here.

Thank you for you help.

0 Karma
1 Solution

somesoni2
Revered Legend

Give this a try

sourcetype=csv | stats count by date_month date_year 
| appendpipe [| stats first(count) as from last(count) as to by date_month | eval count=round(to-from)*100/from,2) | eval date_year="PercentChange"| table date_month date_year count] 
| appendpipe [| stats sum(count) as count by date_month | eval date_year="Total"| table date_month date_year count] 
| xyseries date_month date_year count

The first line get count in following format

date_month, date_year, count
april, 2011, xxx
...
april,2017,yyy
..other months say way 2011 to 2017...

The first appendpipe takes the first count (for 2011) and last count (for 2017) and calculates % change. The column names are adjusted to follow the output of first line (date_month, date_year as label percent and count as percent value). Output of this is added to the end of the output so far.

date_month, date_year, count
..above output..
april, percentchange,zzzz
..similarly for other months...

The second appendpipe is to calculate total for the month. The columns names are again formatted to match the output of first line (date_month, date_year as label Total and count as total value). Output of this is added to the end of the output so far.

The xyseries will give output similar to chart command, where all date_month will come as row, all date_year values as column, include PercentChange and Total, and corresponding values will be populated.

View solution in original post

0 Karma

somesoni2
Revered Legend

Give this a try

sourcetype=csv | stats count by date_month date_year 
| appendpipe [| stats first(count) as from last(count) as to by date_month | eval count=round(to-from)*100/from,2) | eval date_year="PercentChange"| table date_month date_year count] 
| appendpipe [| stats sum(count) as count by date_month | eval date_year="Total"| table date_month date_year count] 
| xyseries date_month date_year count

The first line get count in following format

date_month, date_year, count
april, 2011, xxx
...
april,2017,yyy
..other months say way 2011 to 2017...

The first appendpipe takes the first count (for 2011) and last count (for 2017) and calculates % change. The column names are adjusted to follow the output of first line (date_month, date_year as label percent and count as percent value). Output of this is added to the end of the output so far.

date_month, date_year, count
..above output..
april, percentchange,zzzz
..similarly for other months...

The second appendpipe is to calculate total for the month. The columns names are again formatted to match the output of first line (date_month, date_year as label Total and count as total value). Output of this is added to the end of the output so far.

The xyseries will give output similar to chart command, where all date_month will come as row, all date_year values as column, include PercentChange and Total, and corresponding values will be populated.

0 Karma

asimagu
Builder

hey somesoni2
in the second appendpipe, when you calculate the totals, I think you are also including the figures from the first appendpipe, and then the final figure is wrong.

0 Karma

KassandraI
Engager

Thank you! There was an error with the eval command but I was able to fix it and I also added:

| fieldformat Percent=tostring(Percent) + "%"

to format "Percent".

rflouquet
Explorer

hello,

@Kassandral, could you please tell my how do you fixe the Eval error ?

Thanks a lot,

Romain

0 Karma

DalJeanis
Legend

The eval is missing an open parenthesis...

| eval count=round((to-from)*100/from,2)

0 Karma
Get Updates on the Splunk Community!

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud  In today’s fast-paced digital ...

Observability protocols to know about

Observability protocols define the specifications or formats for collecting, encoding, transporting, and ...

Take Your Breath Away with Splunk Risk-Based Alerting (RBA)

WATCH NOW!The Splunk Guide to Risk-Based Alerting is here to empower your SOC like never before. Join Haylee ...