Reporting

dealing with summaries over time

andres91302
Communicator

Hello guys thanks for the help this community is awesome!

I have the meassure the mean of a sample of water microbial upload in the system every 30 minutes, I can then calculate the mean by day, month week ect, but I have not yet found a way to plot the overall mean historically , I wanna be able to plot a timechart with the : accumulated mean up to current date by spans of 1 month, sorry is that is a tongue twister this is whay I am looking for:

if the mean for Jan is 3.5 pp/ml  and the mean of Feb is 3.6 pp/ml I want my chart to display the over all accumulated mean up to Feb which then will include all of the values up to that date and average them ..

 

index="p_water_s_" OR "test_p_water_OCD"
|search  sample_qc=* date=*

 

the date field is recorded as an epoch time so I later on give iot structure... thank you guys so much you guys rock

Labels (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust
index="p_water_s_" OR "test_p_water_OCD"
|search  sample_qc=* date=*
| bin span=1d _time
| stats mean(sample_q) as mean_sample_q by _time
| streamstats mean(mean_sample_q) as mean_to_date 

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust

I am not sure if you want the overall mean across the whole period, which would be plotted as a straight horizontal line with the daily weekly or monthly means you are charting, or an average mean-to-date which would possibly become flatter the more means are taken into consideration, or a rolling average mean which could just consider the last 5 means for example.

For the first case, use eventstats to add the average mean to all the means you are plotting

For the second case use streamstats to add the cumulative average mean to all the means you are plotting

For the third case use streamstats with a window size of 5 for example.

andres91302
Communicator

@ITWhisperer  Thank you Sir would you please tell me if this would be ok For the second case:

 

index="p_water_s_" OR "test_p_water_OCD"
|search  sample_qc=* date=*
| sort 0 _time
| streamstats mean(sample_q) as mean_to_date 

 

or should I account for the commulative sums first? thank you so much

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

It depends on what you are trying to show, but I suspect you might want to calculate the average mean per reporting cycle, then take a streamstats average of those means. This will give you, for each reporting cycle, the mean for that cycle and the cumulative mean-to-date i.e. two series which you can plot against the same time line. If you do it the other way around, when you do the stats for the average for the reporting cycle, which value are you going to use for the cumulative mean-to-date? If you take the average of the cumulative mean-to-dates you might get a different number to doing it the other way around and my maths isn't strong enough to tell you off the top of my head whether these will be the same or not. If they are different, you would need to decide which of these two you want to show.

0 Karma

andres91302
Communicator

Thank you so much for your quick replay, I want to be able to plot the mean-to-date I want to plot what would be the mean of the water if we use all the meassrements  of the mean daily up until the current date like this:

water_qmean_by_dayacumm_mean (what I want)

3.6
3.7
3.8

3.73.7  
3.1
3.2
3.3
3.23.45   = mean(3.2 and 3.7)
3.4
3.3
3.9
3.533.47 = mean(3.2 and 3.7 and 3.53)
3.6
3.9
3.8
3.763.55 = mean(3.2 and 3.7 and 3.53 and 3.76)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
index="p_water_s_" OR "test_p_water_OCD"
|search  sample_qc=* date=*
| bin span=1d _time
| stats mean(sample_q) as mean_sample_q by _time
| streamstats mean(mean_sample_q) as mean_to_date 
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...