topic Re: Distribute a value form an event over a given time period in Splunk Search

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

Finn — Mon, 01 Aug 2022 15:11:37 GMT

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

Re: Distribute a value form an event over a given time period

ITWhisperer — Mon, 01 Aug 2022 09:01:10 GMT

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?

Re: Distribute a value form an event over a given time period

Finn — Mon, 01 Aug 2022 09:25:37 GMT

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

Re: Distribute a value form an event over a given time period

ITWhisperer — Mon, 01 Aug 2022 12:12:29 GMT

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

Re: Distribute a value form an event over a given time period

Finn — Mon, 01 Aug 2022 13:27:19 GMT

Thank you

"You're simply the best" - Tina Turner