Splunk Commands Reference & Tutorials @ DevOpsSchool.com (2024)

Support my work @Patron!

Stats: Splunk Commands Tutorials & Reference

Commands Category: Filtering

Commands: stats

Use: Calculates aggregate statistics,such as average, count, and sum, over the results set. This is similar to SQL aggregation. If the stats command is used without a BY clause, only one row is returned, which is the aggregation over the entire incoming result set. If a BY clause is used, one row is returned for each distinct value specified in the BY clause.

Difference between stats and eval commands

Thestatscommand calculates statistics based on fields in your events. Theevalcommand creates new fields in your events by using existing fields and an arbitrary expression.

Supported functions and syntax

• avg()
  
• count()
  
• distinct_count()
  
• estdc()
  
• estdc_error()
  
• exactperc<int>()
  
• max()
  
• median()
  
• min()
  
• mode()
  
• perc<int>()
  
• range()
  
• stdev()
  
• stdevp()
  
• sum()
  
• sumsq()
  
• upperperc<int>()
  
• var()
  
• varp()
  
• first()
  
• last()
  
• list()
  
• values()
  
• earliest()
  
• earliest_time()
  
• latest()
  
• latest_time()
  
• rate()

Use the count function of the stats command to find out how many items were added to a cartversus being purchased.

Sample Data - Download sample data for lab - ../../tutorial/splunk/labs/fundamental/Splunk_f1_Data.zip

index=main sourcetype=access_combined_wcookie file=success.do OR file=cart.do status=200# Use the stats count function with a by clause to count events by the file that was servedindex=main sourcetype=access_combined_wcookie file=success.do OR file=cart.do status=200 | statscount by file# Notice that the count column is labeled count by default. Use an as clause to rename the column to Transactions.index=main sourcetype=access_combined_wcookie file=success.do OR file=cart.do status=200 | stats count as Transactions by file# Using the rename command, change the name of the file field to Function.index=main sourcetype=access_combined_wcookie file=success.do OR file=cart.do status=200 | stats count as Transactions by file | rename file as Function# Use search terms with the stats dc function to count all sessions (JSESSIONID)that have been used in our web application dataindex=main sourcetype=access_combined_wcookie | stats dc(JSESSIONID)# Using the by clause, split the Logins by clientip.index=main sourcetype=access_combined_wcookie | stats dc(JSESSIONID) as Logins by clientip# Use the sort command to sort Logins so that the clientip with the most Logins is displayed at the top of the list. Make a note of the top clientip you might be asked about it in the quiz.index=main sourcetype=access_combined_wcookie | stats dc(JSESSIONID) as Logins by clientip | sort -Logins# Use the stats sum function to find the total bytes used for the web applicationindex=main sourcetype=access_combined_wcookie status=200 | stats sum(bytes) as TotalBytes# Split the results by the file field using the by clauseindex=main sourcetype=access_combined_wcookie status=200 | stats sum(bytes) as TotalBytes by file# Use the sort command to sort the file names into alphabetical orderindex=main sourcetype=access_combined_wcookie status=200 | stats sum(bytes) as TotalBytes by file | sort file# Search all database events and use the average (avg) function of the stats command to get an average Duration of all queries.index=main sourcetype=db_audit | stats avg(Duration)# Use as and by clauses to rename the average field to time to complete and split by the Command.index=main sourcetype=db_audit | stats avg(Duration) as "time to complete" by Command# Sort the time to complete so that Command values that take the longest are shown firstindex=main sourcetype=db_audit | stats avg(Duration) as "time to complete" by Command | sort - "time to complete"# Use the stats list function to generate a list of all useragent values that have accessed the web application.index=main sourcetype=access_combined_wcookie | stats list(useragent)# Use the stats values function to only return one instance of each useragent. Add an as clause to name the result as Agents usedindex=main sourcetype=access_combined_wcookie | stats values(useragent) as "Agents used"# This report would be much more useful if we knew the number of times each useragent was used. Add a count function to the stats command that counts the events by useragent as Times used.index=main sourcetype=access_combined_wcookie | stats values(useragent) as "Agents used" count as"Times used" by useragent# Put the results of Agents used and Times used into a tableindex=main sourcetype=access_combined_wcookie | stats values(useragent) as "Agents used" count as "Times used" by useragent | table "Agents used", "Times used"

When you use the stats command, you must specify either a statistical function or a sparkline function. When you use a statistical function, you can use an eval expression as part of the statistical function. For example:

index=* | stats count(eval(status="404")) AS count_status BY sourcetype

Return the average transfer rate for each host

sourcetype=access* | stats avg(kbps) BY host

Search the access logs, and return the total number of hits from the top 100 values of "referer_domain". The "top" command returns a count and percent value for each "referer_domain".

sourcetype=access_combined | top limit=100 referer_domain | stats sum(count) AS total

Calculate the average time for each hour for similar fields using wildcard characters

... | stats avg(*lay) BY date_hour

Remove duplicates of results with the same "host" value and return the total count of the remaining results.

... | stats dc(host)

For each unique value of mvfield, return the average value of field. Deduplicates the values in the mvfield. i.e In a multivalue BY field, remove duplicate values

...| stats avg(field) BY mvfield dedup_splitvals=true

Compare the difference between using the stats and chart commands. This search uses the stats command to count the number of events for a combination of HTTP status code values and host: You can then click the Visualization tab to see a chart of the results.

sourcetype=access_* | stats count BY status, host

Substitute the chart command for the stats command in the search.You can then click the Visualization tab to see a chart of the results.

sourcetype=access_* | chart count BY status, host

Use eval expressions to count the different types of requests against each Web server. You can then click the Visualization tab to see a chart of the results.

sourcetype=access_* | stats count(eval(method="GET")) AS GET, count(eval(method="POST")) AS POST BY host

Calculate aggregate statistics for the magnitudes of earthquakes in an area. Search for earthquakes in and around California. Calculate the number of earthquakes that were recorded. Use statistical functions to calculate the minimum, maximum, range (the difference between the min and max), and average magnitudes of the recent earthquakes. List the values by magnitude type.

source=all_month.csv place=*California* | stats count, max(mag), min(mag), range(mag), avg(mag) BY magType

Find the mean, standard deviation, and variance of the magnitudes of the recent quakes

source=usgs place=*California* | stats count mean(mag), stdev(mag), var(mag) BY magType

Create a table that displays the items sold at the Buttercup Games online store by their ID, type, and name. Also, calculate the revenue for each product.

sourcetype=access_* status=200 action=purchase | stats values(categoryId) AS Type, values(productName) AS "Product Name", sum(price) AS "Revenue" by productId | rename productId AS "Product ID" | eval Revenue="$ ".tostring(Revenue,"commas")

Find out how much of the email in your organization comes from .com, .net, .org or other top level domains.

sourcetype="cisco:esa" mailfrom=* | eval accountname=split(mailfrom,"@"), from_domain=mvindex(accountname,-1) | stats count(eval(match(from_domain, "[^\n\r\s]+\.com"))) AS ".com", count(eval(match(from_domain, "[^\n\r\s]+\.net"))) AS ".net", count(eval(match(from_domain, "[^\n\r\s]+\.org"))) AS ".org", count(eval(NOT match(from_domain, "[^\n\r\s]+\.(com|net|org)"))) AS "other"

Search Web access logs for the total number of hits from the top 10 referring domains. his example searches the web access logs and return the total number of hits from the top 10 referring domains.

sourcetype=access_* | top limit=10 referer

Some Examples from SampleData

index=sales sourcetype=vendor_sales | stats distinct_count(product_name)

Some Examples from SampleData

index=sales sourcetype=vendor_sales| stats distinct_count(product_name)as "Number of Games for sale by vendors"

Some Examples from SampleData

index=sales sourcetype=vendor_sales| stats distinct_count(product_name)as "Number of Games for sale by vendors" by sale_price

Some Examples from SampleData

index=sales sourcetype=vendor_sales| stats dc(product_name)as "Number of Games for sale by vendors" by sale_price

Some Examples from SampleData

index=sales sourcetype=vendor_sales | stats sum(price) as "Gross Sales"index=sales sourcetype=vendor_sales | stats sum(price) as "Gross Sales" by product_nameindex=sales sourcetype=vendor_sales | stats count as "Units Sold" sum(price) as "Gross Sales" by product_nameindex=sales sourcetype=vendor_sales | stats avg(sale_price) as "Average Price"

Some Examples from SampleData

index=sales sourcetype=vendor_sales| stats avg(sale_price) as "Average Price", min(sale_price) as "Min Price", max(sale_price) as "Max Price" by categoryid

Some Examples from SampleData

index=bcgassets sourcetype=asset_list| stats list(Asset) as "company assets" by Employee

Some Examples from SampleData

index=network sourcetype=cisco_wsa_squid| stats values(s_hostname) by cs_username

Some Examples from homeworkdataset.csv

host=homeworkhost=homework state=* usr=*host=homework state=* usr=* | stats count(usr) BY statehost=homework state=* usr=* | stats count(usr) AS cuser BY statehost=homework state=* usr=* | stats count(usr) AS cuser BY state | sort -cuserhost=homework state=* usr=* | stats count(usr) AS cuser BY state | sort cuser

Avail Rajesh Kumar as trainer at 50% Discount Puppet Online Training Puppet Classroom TrainingEnroll Now