Find and compare a value from one event in a subsequent future event

[alphanumeric85]

Hello,

I'm trying to figure out how to search and compare values in subsequent/sequential JSON messages where a user is the same.

Scenario: "UserA" auth-validates from location A, but finishes authentication from location B. Auth validation and auth completion messages are in two separate JSON blobs of different types (one for auth-validation one for auth-success). I'd like to show a table by user showing: User, Validation City, Success City.

In pseudo code:

[Event A] message.Type="auth-validation" for "UserA" = client.City "x"
and in a subsequent JSON entry for "UserA",
[Event B] message.Type"auth-success" NOT = client.City="x"

Example fields I'm working with:

index=auths

(example of event at 03:45:01AM)
user="UserA"
message.Type="auth-validation"
client.City="Los Angeles"

(example of event at 03:45:02AM)
user="UserA"
message.Type"auth-success"
client.City="Houston"

[woodcock · Accepted solution]

Like this:

index="auths" AND ('message.Type'="auth-validation"  OR 'message.Type'="auth-success")
| stats range(_time) AS duration earliest(client.City) AS vaildationCity latest(client.City) AS successCity BY user

Here is a run-anywhere exmple:

|makeresults
| eval _time = "03:45:01AM", user="UserA", message.Type="auth-validation", client.City="Los Angeles"
| append [ |makeresults
| eval _time = "03:45:02AM", user="UserA", message.Type="auth-success", client.City="Houston" ]
| eval _time = strptime(_time, "%I:%M:%S%p")
| stats range(_time) AS duration earliest(client.City) AS vaildationCity latest(client.City) AS successCity BY user

[alphanumeric85]

Thank you!!! This one worked to show the results in the format I needed.... However, just missing one item - how would I only show results where the cities do not match for the auth-validation and auth-success? Right now it's showing cities for both regardless if they do or don't match.

[alphanumeric85]

Note I added "streamstats global=f window=2 current=t" to make sure I capture sequential events.

[woodcock]

Add a dc(client.City) AS cityCount aggregation to stats and then do | where cityCount > 1 at the end.

[to4kawa]

index=auths (message.Type="auth-validation" OR message.Type="auth-success")
|stats values(eval(if(message.Type="auth-validation", client.City, NULL))) as "Validation City", values(eval(if(message.Type="auth-success", client.City, NULL))) as "Success City" by user

[alphanumeric85]

Thank you for writing. Although this does capture both events, this doesn't compare the two where auth success is different than auth validation and it doesn't capture sequential events by user.

[to4kawa]

sorry @alphanumeric85
I forget to add by user .
my answer is updated

[alphanumeric85]

Thanks for this. Unfortunately it doesn't appear to be comparing two consecutive events for the user in a chronological timeline/timespan and seeing if the two cities do not equal each other for the two events, then displaying them. Does streamstats need to be used here along with some sort of earliest/latest combination?