# Compare to previous column string value

Hello, noob user here with Flux working with InfluxDB 2 and Syslogs.

I’m looking to graph information based on what info I get in a syslog which has no numerical information. I’m pulling information out of the syslog message and generating new columns of data that basically interprets the state of the event. I’m then using stateDuration to get a time of how long the duration is between states.

This is an example where the _value column is the syslog message and I generate the state and ID columns by pulling values out of the message.

I can get a State Duration for “No Purging” and graph that. I want to be able to get a State Duration for Phase1 and Phase2 except that info is not in the message as it doesn’t identify which phase completed. However, the phases will complete in order so I’ve been trying to find a way to query which phase started on the previous event and fill in the state as that phase. Another option would be to compare the IDs and fill in the phase but I’ve not been able to get anything to work.

If I can get those two empty event fields filled in I can graph a complete time frame of how long each phase or no purge takes.

Any help would be greatly appreciated.

Rob!

Hello @R0bR,
Can you |> filter(fn: (r) => r.state =~ /Phase[1-2]\$/ and then use events.duration() function | InfluxDB OSS 2.0 Documentation ?

Hello @Anaisdg,

Thanks for the response. The issue is that I need the duration between the start\end of phase1 and the start\end of phase2, not the duration between phase1 and phase2. Because the completed syslog event does not identify the phase I have nothing to extract into the state field. I was hoping there was a way to use the ID matching to extract the the previous state into the empty cell.

Hello @R0bR,
Ah sorry for misunderstanding/thanks for clarifying.
Can you:

|> group( columns: ["ID"]


And then use events.duration()? Assuming you’re only monitoring start and stop of phases?

To extract the state field? Does fill() provide the functionality you’re looking for? fill() function | InfluxDB OSS 2.0 Documentation

Hello @Anaisdg ,

By definition it looks like fill() function should give me what I need to fill the nulls with the previous state but it’s not working. I think it is because the value of the field is probably not actually null but empty. When I check the syslog message for the phase and don’t get a match I equate it to “”. Unless I’m over thinking it but should it not show as null in order for the fill() function to match?

Thanks,
Rob!

Hello @R0bR,
How are you writing empty strings? Wouldn’t it be easier to not write that value?
Anywho this should work:


import "experimental/json"
null=json.parse(data: bytes(v: "null"))

from(bucket: "test")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "test")
|> filter(fn: (r) => r["_field"] == "foo")
|> filter(fn: (r) => r["test"] == "one")

|> map(fn: (r) => ({
r with
_value:
if r._value == "" then null
else  r._value
})
)
|> fill(usePrevious: true)

1 Like

Hello @Anaisdg,

This was the missing piece, I tried to use just null before but failed with an error, once I added the declaration it got me there. I also had to use fill(column: “state”, usePrevious: true) rather than just fill(usePrevious: true).

Thank you so much, you’ve been an incredible help.

Rob!

Hello @R0bR,
I’m so happy I could help!