Reporting

How to achieve stats sum with complex grouping conditions?

vsasdao
Explorer

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

 

Labels (1)
0 Karma
1 Solution

marysan
Communicator

| 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

View solution in original post

Tags (4)

marysan
Communicator

| 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

Tags (4)

vsasdao
Explorer

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.

 

0 Karma
Get Updates on the Splunk Community!

Devesh Logendran, Splunk, and the Singapore Cyber Conquest

At this year’s Splunk University, I had the privilege of chatting with Devesh Logendran, one of the winners in ...

There's No Place Like Chrome and the Splunk Platform

WATCH NOW!Malware. Risky Extensions. Data Exfiltration. End-users are increasingly reliant on browsers to ...

Customer Experience | Join the Customer Advisory Board!

Are you ready to take your Splunk journey to the next level? &#x1f680; We invite you to join our elite squad ...