I have a field "vpn_duration“ which is in the format hh:mm:ss
and as type keyword and a string. This is not a timestamp but the duration of a vpn connection.
Logically I’m not able to aggregate these values.
How can I convert that string to something recognized as a time duration
or how can I convert that string with a pipline rule to a value of seconds?
I suppose a possible way would be to strip down the string with regex and do the math on the individual values to get the total seconds as a result. But I have no clue how to tackle this.
Any ideas?
rule "XXX"
when
has_field("vpn_duration")
then
let current_year = now().year;
let ts_string = concat(to_string(current_year), concat(" ", to_string($message.vpn_duration)));
let new_date = parse_date(to_string($message.vpn_duration),"yyyy MMM dd HH:mm:ss","America/Chicago");
set_field("vpn_duration", new_date);
end
Unfortunately your suggested rule throws an error:
Error evaluating action for rule <duration/649e5c9f55d28000f72a8a65> (pipeline <VPN duration/649e5cea55d28000f72a8b04>) - In call to function 'parse_date' at 7:16 an exception was thrown: Invalid format: "00:00:42" is malformed at ":00:42"
I also don’t understand what you are doing with the new ts_string. It is not used in the subsequent processing. The result for ts_string is something like “2023 00:00:42”.
Maybe I was a bit unclear:
My message contains already 00:00:42 – which are 42 seconds. I don’t want a Date/Time like “2023 06 30 00:00:42”.
Just want to be able to sum up the seconds (or hours and minutes) of all messages in a given timeperiod. But for now the field with the value 00:00:42 is just a string.
There is a function seconds(value) or minutes(value) which creates a period but I’m unsure of how to apply that to something like 00:45:13 for a duration of 45 minutes and 13 seconds.
I think @gsmith misunderstood you but I hope I understand you correctly
my idea…
I would use a rule to set an additional field that has an integer value. As Unix time stamp e.g.
The reference is then time since 1970-01-01 and time now.
With the new field and a mapping to the VPN session (e.g. client IP) you evaluate the runtime per session in a second step (pipeline stage)
Of course, this only works if the VPN server regularly sends logs. (Unfortunately, that is not clear from your question)
I understand now sorry about that. Not sure if I have anything like that. I need to look through my doc’s. If i come arcoss something ill post it here.
I do have something similar but only for a hour. You maybe able to work off this.
Two different example of rules.
rule "hour-extractor"
when
true // all messages have the "timestamp" field by default
then
let hour = $message.timestamp.hourOfDay;
set_field("hour", hour);
end
Second Example using GROK
rule "HourExtractor"
when
has_field("timestamp")
then
let Time = to_string($message.timestamp);
let Hour = grok("%{DATA:UNWANTED}T%{NUMBER:Hour}%{DATA:UNWANTED}", Time, true);
set_fields(Hour);
end