Dashboards & Visualizations

Calculate KWH from random samples - need help.

pir8radio
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)
Tags (2)
0 Karma
1 Solution

to4kawa
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?

View solution in original post

0 Karma

to4kawa
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?

0 Karma

pir8radio
Path Finder

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

0 Karma

to4kawa
Ultra Champion

divide by a hundred.
that's good.

0 Karma

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

0 Karma

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

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

0 Karma

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

0 Karma

pir8radio
Path Finder

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

0 Karma

pir8radio
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
  | addcoltotals
0 Karma

pir8radio
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
0 Karma

to4kawa
Ultra Champion

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

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

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

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...