Calculate KWH from random samples - need help.

[pir8radio]

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.

[to4kawa · Accepted solution]

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

[pir8radio]

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

[to4kawa]

divide by a hundred.
that's good.

[pir8radio]

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.

[to4kawa]

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.

[pir8radio]

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

[pir8radio]

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

[pir8radio]

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

[pir8radio]

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

[to4kawa]

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