Splunk Search

Stats search for users on domain that have used over X amount of megabytes?

bcarlson
New Member

Stats help please
I have CDR records that contain the fields --- User | Megabytes Used | Date | Domain
I want to search through my CDR records and generate a report that shows me the Users on the domain Bob.com that have used over 300Meg in the month I am running the report. I will then run the search with a date range.

The report would look something like this.

User A 800 Meg
User B 799 Meg
User C 755 Meg
User D 300 Meg
Currently my search looks like this.
Domain="Bob.com" AcctType="2" | fields Total_Megabytes, User | stats sum(Total_Megabytes) as Usage | where Usage>=300 | chart sum(Usage) by User
My search does not return any results. Any suggestions would be much appreciated.
thanks Bob

Tags (2)
0 Karma
1 Solution

somesoni2
Revered Legend

You search can be simplified as below:

Domain="Bob.com" AcctType="2" | stats sum(Total_Megabytes) as Usage by User | where Usage>=300 

This will work fine if the field 'Total_Megabytes' contains only numeric values (e.g. '800' OR '799' OR '755'. If it contains values with suffix Meg (e.g. '800 Meg' OR '799 Meg' OR '755 Meg') then you need to extract the numerical value before the stats (and add it back after where if you need that format).

If values are like '800 Meg', then something like this would work.

Domain="Bob.com" AcctType="2"  | eval Total_Megabytes=mvindex(split(Total_Megabytes," "),0)| stats sum(Total_Megabytes) as Usage by User | where Usage>=300 | eval Usage=Usage." Meg"

View solution in original post

somesoni2
Revered Legend

You search can be simplified as below:

Domain="Bob.com" AcctType="2" | stats sum(Total_Megabytes) as Usage by User | where Usage>=300 

This will work fine if the field 'Total_Megabytes' contains only numeric values (e.g. '800' OR '799' OR '755'. If it contains values with suffix Meg (e.g. '800 Meg' OR '799 Meg' OR '755 Meg') then you need to extract the numerical value before the stats (and add it back after where if you need that format).

If values are like '800 Meg', then something like this would work.

Domain="Bob.com" AcctType="2"  | eval Total_Megabytes=mvindex(split(Total_Megabytes," "),0)| stats sum(Total_Megabytes) as Usage by User | where Usage>=300 | eval Usage=Usage." Meg"

bcarlson
New Member

somesoni2. that worked perfectly! thanks for much for your help today.

0 Karma
Get Updates on the Splunk Community!

Splunk Education - Fast Start Program!

Welcome to Splunk Education! Splunk training programs are designed to enable you to get started quickly and ...

Five Subtly Different Ways of Adding Manual Instrumentation in Java

You can find the code of this example on GitHub here. Please feel free to star the repository to keep in ...

New Splunk APM Enhancements Help Troubleshoot Your MySQL and NoSQL Databases Faster

Splunk Observability has two new enhancements to make it quicker and easier to troubleshoot slow or frequently ...