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
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"
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"
somesoni2. that worked perfectly! thanks for much for your help today.