topic Re: How to add a new column to existing stats result after performing a calculation on each value in a column ? in Splunk Search

How to add a new column to existing stats result after performing a calculation on each value in a column ?

UdayAditya — Tue, 29 Sep 2020 17:02:49 GMT

Hi
I am new to splunk and still exploring it.
How do i create a new result set after performing some calculation on existing stats output ?

More details here:
There can be multiple stores and each store can create multiple deals.

I was able to get total deals per store id using this query
index=fosign
env="test"
Level="Information"
Properties.DealJacketId=*
Properties.StoreId=*
Properties.LogSource="Create.NewDeal.Handler"
| stats count(Properties.DealJacketId) as "total_deals (In selected time period)" by Properties.StoreId

but I am finding it difficult to produce average deals per day, dynamically based on selected time frame.
Note: User can select multiple time frames and this needs to work for all time frames selected.

StoreId | total_deals (In selected time period) | average_deals_per_day (includes weekend) ==> "need help for this column"

S1234 100 12
S1234 200 15
.
.
.

Sample log:
{

Level: Information

MessageTemplate: Deal created successfully for store: {storeId}, deal id: {DealJacketId}, DealNumber: {DealNumber}
Properties: {

CorrelationId: No Correlation Id Provided
DealJacketId: MTc1ODY2MDAwMDAwMDAwMDAyfDcyMjN8bmp3OUVZQkZ6Sw==

DealNumber: 7223

LogSource: Create.NewDeal.Handler
SourceContext: CreatingNewDeal

}

Timestamp: 2017-12-01T09:20:08.7158876+00:00

}

Any help is appreciated. Thank you.

Re: How to add a new column to existing stats result after performing a calculation on each value in a column ?

niketn — Fri, 01 Dec 2017 10:38:40 GMT

@UdayAditya, following is a run anywhere search based on Splunk's _internal index which gives a daily average of errors as well as total for selected time period:

index=_internal sourcetype=splunkd log_level!=INFO
|  bin span=1d _time
|  stats count as Total by log_level, _time
|  stats avg(Total) as Avg sum(Total) as Total by log_level
|  eval Avg=round(Avg,1)

You can try the following with your search:

 <YourBaseSearch>
| bin span=1d _time
| stats count(Properties.DealJacketId) as total_deals by Properties.StoreId, _time
| stats sum(total_deals) as "total_deals (In selected time period)" avg(total_deals) as "avg_deals_daily" by Properties.StoreId
| eval avg_deals_daily=round(avg_deals_daily,1)

Re: How to add a new column to existing stats result after performing a calculation on each value in a column ?

UdayAditya — Fri, 01 Dec 2017 10:48:43 GMT

@niketnilay
Thank you, i tried multiple things and looked like i added unnecessary complexity.
This solution looks simple and clear.

Re: How to add a new column to existing stats result after performing a calculation on each value in a column ?

niketn — Fri, 01 Dec 2017 15:09:36 GMT

Trying is important, community members are always there otherwise 🙂

Re: How to add a new column to existing stats result after performing a calculation on each value in a column ?

UdayAditya — Fri, 01 Dec 2017 17:23:15 GMT

@niketnilay
if i understood correctly. "span=1d _time" will try placing all events in buckets corresponding to each day.

While aggregating values, I realized Splunk ignores buckets without any events in it.
In that case the average calculated can easily become invalid if there are no events falling into particular day's bucket.

ex:
Selected time range = 5 days, total events = 10 then
I expect the daily average to be 10/5 = 2.

day1 bucket = 3
day2 bucket = 3
day3 bucket = 0
day4 bucket = 4
day5 bucket = 0

then i see that Splunk calculates average as (3+3+4)/3 ~ 3.33 which is not desired for me.

Any suggestion to how can i achieve the correct average using splunk ?

Re: How to add a new column to existing stats result after performing a calculation on each value in a column ?

niketn — Tue, 12 Dec 2017 03:07:20 GMT

Yes your understanding of bin command is correct. Refer to documentation: https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Bin

You would have to switch from stats to timechart. The timechart command with fillnull will fill empty rows with 0 so that your average shows up correctly.

  <YourBaseSearch>
 | timechart span=1d count(Properties.DealJacketId) as total_deals by Properties.StoreId
 | fillnull value=0 <yourStoreID1>,<yourStoreID2>...
 | stats sum(total_deals) as "total_deals (In selected time period)" avg(total_deals) as "avg_deals_daily" by Properties.StoreId
 | eval avg_deals_daily=round(avg_deals_daily,1)

PS: span=1d argument in timechart command has same effect as bin command in stats. If you remove span=1d, Splunk will adjust on its own the span buckets. For example, for 7 days it will be span=1d and for 24 hours it will be span=1h

Please try out and confirm.