Top ten most common database queries – Splunk Lantern

You might want to know what queries to your database are the most common when doing the following:

Analyzing wire data from databases

Prerequisites

In order to execute this procedure in your environment, you may need to first on-board the data, services, or apps shown in the following table.

Category	Name	Importance
Data	Database query data	Required
Data	Database performance data	Recommended
App	Splunk Stream	Required

Example

The purpose of this example is to show how this procedure works in a general environment. In your environment, you can optimize the search by specifying an index, a time range, or a different data source.

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.

Run the following search:
```
|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 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".

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

Result

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

Prerequisites

Example

Search Explanation

Result

Related articles