Hi there,
I'd like to run a report from Splunk that concerns the means of receiving the One-Time-Password for both my own company and our partners. The means can be either email or mobile phone number
index=2FA-OTP "has called sendOtp with" AND "for primary customers:" | rex ".* has called sendOtp with (?<means>.*?) for .* from:(?<regnr>.*?) for primary customers: (?<hostname>.+)" | stats count as nrs by hostname,regnr,means
The above Splunk search gives results like:
...
94.***.**.**:45701 has called sendOtp with +45 41***** for hf10028 from:Partner-Company-A for primary customers: site.example.com
95.***.**.**:45702 has called sendOtp with +47 41***** for hf10029 from:Partner-Company-B for primary customers: site.example.com
98.***.**.**:45732 has called sendOtp with james@example.com for jm23456 from:mycompany for primary customers: site2.example.com
98.***.**.**:45732 has called sendOtp with +48 98***** for jm23457 from:mycompany for primary customers: site2.example.com
...
However, I'd like to further group the counting results by grouping into Email (means containing '@') or SMS (means containing no '@'), if it's for my own company (regnr containing "mycompany") or Partners (regnr containing no "mycompany")
Thanks in advance
| eval flag=case(match(regnr,"Partner"),"partner",match(regnr,"mycompany"),"mycompany")
| stats count(eval(match(means,"@"))) as Email_Count , count(eval(NOT match(means,"@"))) as SMS_Count by flag
OR of you expect more details :
| eval flag=case(match(regnr,"Partner"),"partner",match(regnr,"mycompany"),"mycompany")
| stats count(eval(match(means,"@"))) as Email_Count , count(eval(NOT match(means,"@"))) as SMS_Count, values(means) as means , values(regnr) as regnr by flag
| eval flag=case(match(regnr,"Partner"),"partner",match(regnr,"mycompany"),"mycompany")
| stats count(eval(match(means,"@"))) as Email_Count , count(eval(NOT match(means,"@"))) as SMS_Count by flag
OR of you expect more details :
| eval flag=case(match(regnr,"Partner"),"partner",match(regnr,"mycompany"),"mycompany")
| stats count(eval(match(means,"@"))) as Email_Count , count(eval(NOT match(means,"@"))) as SMS_Count, values(means) as means , values(regnr) as regnr by flag
I've adjusted a bit:
eval flag=case(like(regnr, "%mycompany%"), "MYCOMPANY",1=1,"PARTNERS")
I can not use "else" or "default" in the case statement, therefore I've used the tricks/workaround: 1=1 as the default/else situations.