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.
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.
@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)
@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)
@niketnilay
Thank you, i tried multiple things and looked like i added unnecessary complexity.
This solution looks simple and clear.
Trying is important, community members are always there otherwise 🙂
@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 ?
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.