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.

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.