Splunk Search

Find stats from 2 events

dyapasrikanth
Explorer

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.

Labels (2)
0 Karma
1 Solution

scelikok
Champion

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
If this reply helps you an upvote is appreciated.

View solution in original post

scelikok
Champion

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
If this reply helps you an upvote is appreciated.

View solution in original post

richgalloway
SplunkTrust
SplunkTrust

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
---
If this reply helps you, an upvote would be appreciated.
0 Karma

dyapasrikanth
Explorer

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.

0 Karma
Take the 2021 Splunk Career Survey

Help us learn about how Splunk has
impacted your career by taking the 2021 Splunk Career Survey.

Earn $50 in Amazon cash!