How to distribute a value form an event over a given time period?

[Finn]

Data Model (simplified):

- numeric value "Hours"

- numeric value "StartTime" (assumed to always have time be 00:00:00) in UnixTime

- numeric value "EndTime" (same assumptionm as above) in UnixTime

- calculated from the above two: time period as UnixTime value

- calculated: "Hours" per day

- string value (cathegorical) "Group"

Goal:

get a List of Days where each day contains:

- the respective date

- the "Hours per Day" value assigned to a field named after the Group

Intention:

create a vizualisation showing what group is needed how much at what time

[ITWhisperer]

Can you share some sample events - preferably in a code block </> (rather than paragraph text or graphic) as it makes it easer to replicate your usecase, and suggest a solution?

[Finn]

Sure 🙂

This would be the data i am working with:

{
    "Stunden":  3,
    "Gruppe":  "WST-M",
    "DatumStart":  "2022-07-08 00:00:00",
    "DatumEnde":  "2022-08-31 00:00:00",
}

 These are all the relevant fields i am working with.

I already wrote SPL to extract the Unix Times, calculate the duration of the operation and divide the "Stunden" by this duration.

Since alle field names are in german, I will provide some translations below (ignore if not needed)

Stunden - hours

Gruppe - group

Datum - Date

Start - start

Ende - end

[ITWhisperer · Accepted solution]

Try something like this

| makeresults
| eval _raw="{
    \"Stunden\":  3,
    \"Gruppe\":  \"WST-M\",
    \"DatumStart\":  \"2022-07-08 00:00:00\",
    \"DatumEnde\":  \"2022-08-31 00:00:00\",
}"
| spath
| eval DatumStart = strptime(DatumStart,"%F %T")
| eval DatumEnde = strptime(DatumEnde,"%F %T")
| eval duration = DatumEnde-DatumStart
| eval days = floor(duration/86400)
| eval hoursPerDay = Stunden/days
| eval day = mvrange(0,days)
| mvexpand day
| eval _time = DatumStart+(day*86400)
| timechart sum(hoursPerDay) by Gruppe

[Finn]

Thank you

"You're simply the best" - Tina Turner

---