How to achieve stats sum with complex grouping conditions?

[vsasdao]

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

[marysan · Accepted solution]

| 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

[vsasdao]

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.