Skip to main content
 
Splunk Lantern

Top ten most common database queries

 

Queries with a high execution volume warrant regular review and inspection. You want to create a list of the top ten so that you can work with the owners to optimize such queries.

Data required   

Database data

Procedure 

  1. Verify that you have deployed Splunk Stream.
  2. Run the following search. You can optimize it by specifying an index and adjusting the time range.
|top query useother=true

Search explanation

The table provides an explanation of what each part of this search achieves. You can adjust this query based on the specifics of your environment.

Splunk Search Explanation

|top query useother=true

Return the top 10 most common queries, with a count and percent value for each query. Merge the results not included in the table into a single new row called "OTHER".

You can change the number of queries returned by adding the limit parameter. Example, limit=5.

Next steps

The result is a table showing the top 10 queries by count. Owners should be notified when their queries are in the top ten so they can optimize them to make the query exceptionally fast. Where possible, upstream refactoring, such as caching, may be considered to minimize the number of times the query is called. 

In addition, the SQL statements themselves can tell a story about potential policy violations, such as large full table scans of sensitive data such as human resources information or passwords. 

query count percent

SELECT * FROM hr_employee_info

1700

12.834063

SELECT password FROM SYS.USER$ WHERE name='SYS'

887

6.696361

SELECT * FROM customers WHERE customer_uid=b56a5c74-87a3-4593-9eb5-2d2b1c38a0d8

22

0.166088

SELECT * FROM customers WHERE customer_uid=5d052b26-9b18-44a0-b4db-7c4b028e74e2

22

0.166088

Finally, you might also want to look at similar searches in our article Analyzing wire data from databases