Find stats from 2 events

[dyapasrikanth]

We have 2 events

OTP generated  through SMS with UUID=123123
OTP generated through EMAIL with UUID=432432
OTP Verified for UUID=123123

How to join events to find how many OTPs generated through different mediums (SMS/EMAIL) and how many successfully verified.

[scelikok · Accepted solution]

Hi @dyapasrikanth,

You can use eventstats to add medium to all events based on UUID. Please try below;

index=foo ("OTP generated*" OR "OTP Verified*")
| rex "OTP (?<action>\w+)"
| rex "through (?<medium>\w+)"
| rex "UUID=(?<UUID>\d+)"
| stats values(*) as * by UUID
| eventstats last(medium) as medium by UUID
| stats sum(eval(action="generated")) as NumGenerated, sum(eval(action="Verified")) as NumVerified by medium

[richgalloway]

Join is not necessary because the stats command can fulfill your wishes.

index=foo ("OTP generated*" OR "OTP Verified*")
```Now extract fields.  Skip this if the fields are already extracted```
| rex "OTP (?<action>\w+)"
| rex "through (?<medium>\w+)"
| rex "UUID=(?<UUID>\d+)"
```Now for the "join"```
| stats values(*) as * by UUID
```Now count the events```
| stats sum(eval(action="generated")) as NumGenerated, sum(eval(action="Verified")) as NumVerified by medium

[dyapasrikanth]

Thanks for your reply, I am getting data for NumGenerated not for NumVerified.

dyapasrikanth_0-1618180583660.png

We have medium only for generate events, not for verify events. We can correlate them only by UUID to find the medium which is verified.