Dashboards & Visualizations

## Calculate KWH from random samples - need help.

Path Finder

Ok so i am grabbing watt readings every 10 or so seconds, but not EVERY 10 seconds, maybe its some samples are 5 seconds apart, some are 13, most are 10, long story short i have about 310-380 watt samples. so those samples can be any number from 0-10,000watts. With me so far? Now i need to take whatever quantity of samples I have within the hour, and figure out my KWH cost. that cost is 0.095 (9,5 cents) per kWh. I dont know how to do that. I dont want to average stuff too much, I want the kwh price to be as accurate as possible. I will be using this calculation in a splunk dashboard, so if anyone could help i would greatly appreciate it.

Labels (1)
• ### chart

Tags (2)
1 Solution
SplunkTrust
``````...
| bin _time span=1h
| stats count as total sum(watt) as watt_sum by _time
| eval hourly_cost=0.095*(watt_sum/1000)
| eval avg_kwh=hourly_cost/total
``````

like this?

SplunkTrust
``````...
| bin _time span=1h
| stats count as total sum(watt) as watt_sum by _time
| eval hourly_cost=0.095*(watt_sum/1000)
| eval avg_kwh=hourly_cost/total
``````

like this?

Path Finder

that gets me \$495 instead of \$4.95 for the day.

SplunkTrust

divide by a hundred.
that's good.

Path Finder
``````index="sense_power_monitor"  | bin _time span=1h
| stats count as samples sum(usage_info.d_w) as watt_sum by _time
| eval hourly_cost=0.095*(watt_sum/1000)
| eval avg_kwh=hourly_cost/samples
``````

Doesnt seem to work...... what am I missing... I put this in a chart and have no "watt_sum" value, but i do have a total value... not sure why the watt sum isnt working.

SplunkTrust

`I put this in a chart`
try line by line.

`| stats count as samples sum(usage_info.d_w) as watt_sum by _time`
this result is following:

`````` _time watt_sum sample
A:A:A 12345 200
B:B:B 23456 200
``````

....

at last:

``````_time watt_sum sample horly_cost avg_kwh
A:A:A 12345 500 4.54
....
``````

what's wrong? I don't know your log and your results.

Path Finder
``````_time                        samples        watt_sum
2020-04-15 18:00    477     2364938
2020-04-15 19:00    472     2161773
2020-04-15 20:00    495         1540459
2020-04-15 21:00    345     393664
2020-04-15 22:00    569     600557
2020-04-15 23:00    557     579712
2020-04-16 00:00    521     475932
2020-04-16 01:00    537     524457
2020-04-16 02:00    531     553670
2020-04-16 03:00    541         560756
``````

Something is up with your calculation, i cant get close to the actual values. in dollars or kwh...

Path Finder

I think i figured it out @to4kawa you got me on the right track, thank you!

Path Finder
``````index="sense_power_monitor"  | bin _time span=1h
| stats count as samples sum(usage_info.solar_w) as watt_sum by _time
| eval kW_Sum=watt_sum/1000
| eval avg_kWh=kW_Sum/samples
| eval kWhCost=avg_kWh*0.095
``````
Path Finder

This is what was throwing me off... some of my values are negative numbers...

``````2020-04-15 00:00    512 -1453.1523723602295000  -0.0028381882272660733000   -1.4531523723602295000  -0.00027
2020-04-15 01:00    525 -1413.8822467327118000  -0.0026931090413956416000   -1.4138822467327120000  -0.00026
2020-04-15 02:00    526 -1470.4220585823060000  -0.0027954791988256770000   -1.4704220585823060000  -0.00027
2020-04-15 03:00    524 -1379.7629923820496000  -0.0026331354816451326000   -1.3797629923820496000  -0.00025
2020-04-15 04:00    529 -1492.7082788944244000  -0.0028217547805187604000   -1.4927082788944244000  -0.00027
2020-04-15 05:00    518 -2029.8906929492950000  -0.0039187079014465160000   -2.0298906929492953000  -0.00037
2020-04-15 06:00    508 11771.73814809322400000 0.02317271288994729000000   11.77173814809322400000 0.0022
2020-04-15 07:00    507 42808.122723191980000000    0.084434167106887530000000  42.808122723191980000000    0.0080
``````
SplunkTrust

I see, We should extract fields again.
will you provide logs? maybe json.

Get Updates on the Splunk Community!