How to create a transaction that startswith=(something!="(null)") endswith=(something="(null)")

[ib_321]

My goal is to create a transaction that ends with customerId being "(null)" and starts with customerId being something other than "(null)". Here is my query:

... | transaction deviceId startswith=(customerId!="(null)") endswith=(customerId="(null)") maxspan=10m | stats count by deviceId

When I inspect the resulting list of deviceIds, none of them meet the criteria that I wanted for my transaction--none of them go from customerId!="(null)" to customerId="(null)". I have tried reversing the log ... | reverse | transaction ..., but I get the same result.

The only explanation I have come up with is that this has something to do with comparison to "(null)"--that in the end customerId will always be "(null)" after the last event--but this query compares customerId to the string "(null)" so that doesn't make sense.

Any help would be greatly appreciated. Thanks in advance.

[493669]

Hi @ib_321,
before transaction command add | fillnull which will fill all null values by zero
try below:

...| fillnull| transaction deviceId startswith=(customerId!="0") endswith=(customerId="0") maxspan=10m | stats count by deviceId

[ib_321]

Thank you for your response. This didn't resolve the issue.

I don't think fillnull, replaces "(null)". For example,

... | fillnull | search customerId="(null)"

returns a bunch of events.

[ddrillic]

Interesting, the documentation at fillnull

says -

... | fillnull

-- For the current search results, fill all empty fields with zero.

[493669]

ohhk..i thought its null field but it's a string (null)
Try below:

... | transaction deviceId startswith=(customerId!="(null)") endswith=eval(match(customerId, "(null)")) mvlist=true maxspan=10m | stats count by deviceId

[ib_321]

That seems to have solved my problem. Thank you!

Can you explain why this query worked while mine didn't?

[493669]

I think your query will also work just add mvlist=true

... | transaction deviceId startswith=(customerId!="(null)") endswith=(customerId="(null)") mvlist=true maxspan=10m | stats count by deviceId

The mvlist attribute controls whether the multivalue fields of the transaction are (1) a list of the original events ordered in arrival order or (2) a set of unique field values ordered lexigraphically. If a comma- or space-delimited list of fields is provided, only those fields are rendered as lists.