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
Ultra Champion
``````...
| 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?

Ultra Champion
``````...
| 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.

Ultra Champion

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.

Ultra Champion

`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
....
``````

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
``````
Ultra Champion

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

Get Updates on the Splunk Community!

#### Enterprise Security Content Update (ESCU) | New Releases

In the last month, the Splunk Threat Research Team (STRT) has had 2 releases of new security content via the ...

#### Announcing the 1st Round Champion’s Tribute Winners of the Great Resilience Quest

We are happy to announce the 20 lucky questers who are selected to be the first round of Champion's Tribute ...

#### We’ve Got Education Validation!

Are you feeling it? All the career-boosting benefits of up-skilling with Splunk? It’s not just a feeling, it's ...