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!

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...