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!

The All New Performance Insights for Splunk

Splunk gives you amazing tools to analyze system data and make business-critical decisions, react to issues, ...

Good Sourcetype Naming

When it comes to getting data in, one of the earliest decisions made is what to use as a sourcetype. Often, ...

See your relevant APM services, dashboards, and alerts in one place with the updated ...

As a Splunk Observability user, you have a lot of data you have to manage, prioritize, and troubleshoot on a ...