Top 50 Splunk Queries

Splunk is a powerful tool used for searching, monitoring, and analyzing machine-generated data. In this blog post, we’ll delve into the art of winning analytics by embracing success with the top 50 splunk queries. These queries are your secret weapon to harness the true potential of your data and achieve remarkable results.

Unleashing the Power of Splunk Queries

Imagine having the ability to effortlessly navigate through mountains of data and extract meaningful patterns, trends, and anomalies. Splunk queries serve as your guide on this journey, allowing you to ask specific questions and receive actionable answers. Whether you’re a business analyst, IT professional, or security expert, these top 50 splunk queries empower you to uncover insights that were previously hidden within the data. To learn more about splunk queries click here.

Embracing Success with Top 50 Splunk Queries:

Let’s explore how embracing the art of winning analytics with the top 50 Splunk queries can lead to remarkable success:

1. Search for a specific term in the logs:

Query: `index=logs sourcetype=application_logs “error”`

Explanation: This query retrieves all logs containing the term “error” from a data source named “logs” and a sourcetype “application_logs”.

Example: Finding all error messages in an application’s log files.

2. Search logs within a time range:

– Query: `index=logs sourcetype=web_logs earliest=-1d@d latest=now`

– Explanation: This query searches for web logs from the last day until the current time.

– Example: Analyzing website traffic for the past 24 hours.

3. Count events matching a condition:

– Query: `index=security sourcetype=authentication status=failed | stats count`

– Explanation: This query counts the number of failed authentication events from a data source named “security” and sourcetype “authentication”.

– Example: Counting failed login attempts in a security log.

4. Group and count events by a field:

– Query: `index=logs sourcetype=access_logs | stats count by client_ip`

– Explanation: This query groups access logs by the “client_ip” field and counts the occurrences for each IP address.

– Example: Identifying the most frequent visitors to a website.

5. Calculate the average response time:

– Query: `index=web_logs sourcetype=response_time | stats avg(response_time)`

– Explanation: This query calculates the average response time from a data source named “web_logs” with sourcetype “response_time”.

– Example: Monitoring the performance of a web application by calculating average response times.

6. Filter logs by multiple conditions:

– Query: `index=logs sourcetype=app_logs error AND status=500`

– Explanation: This query retrieves logs containing both “error” and “status=500” from the specified data source and sourcetype.

– Example: Finding critical errors with a specific HTTP status code in application logs.

7. Visualize data using a bar chart:

– Query: `index=logs sourcetype=traffic | chart count by page`

– Explanation: This query creates a bar chart showing the count of log events grouped by the “page” field.

– Example: Visualizing the popularity of different pages on a website.

8. Calculate maximum CPU usage:

– Query: `index=performance sourcetype=system_stats | stats max(cpu_usage)`

– Explanation: This query calculates the maximum CPU usage from a data source named “performance” and sourcetype “system_stats”.

– Example: Monitoring the highest CPU load on a server.

9. Identify unique values in a field:

– Query: `index=logs sourcetype=app_logs | dedup user`

– Explanation: This query removes duplicate entries based on the “user” field from the specified data source and sourcetype.

– Example: Finding distinct users in application logs.

10. Calculate data volume by source:

– Query: `index=* | stats sum(size) by source`

– Explanation: This query calculates the total data volume for each data source.

– Example: Monitoring the amount of data generated by different sources.

11. Filter logs based on time and action:

– Query: `index=logs sourcetype=audit_logs earliest=-7d action=download`

– Explanation: This query retrieves audit logs of “download” actions from the past 7 days.

– Example: Auditing file downloads in an organization.

12. Calculate event frequency over time:

– Query: `index=logs sourcetype=error_logs | timechart count`

– Explanation: This query generates a time chart displaying the count of error log events over time.

– Example: Tracking the occurrence of errors in a system.

13. Search for events containing either of multiple terms:

– Query: `index=logs sourcetype=app_logs “error” OR “exception”`

– Explanation: This query fetches logs containing either “error” or “exception” from the specified data source and sourcetype.

– Example: Finding errors or exceptions in application logs.

14. Calculate median response time:

– Query: `index=web_logs sourcetype=response_time | stats median(response_time)`

– Explanation: This query calculates the median response time from web logs.

– Example: Evaluating the typical response time of a web application.

15. Calculate average transaction amount:

– Query: `index=financial sourcetype=transactions | stats avg(amount)`

– Explanation: This query computes the average transaction amount from financial data.

– Example: Analyzing the average value of transactions.

16. Identify high-traffic hours:

– Query: `index=web_logs sourcetype=traffic | eval hour=strftime(_time, “%H”) | stats count by hour`

– Explanation: This query groups web traffic logs by hour and shows the count of events for each hour.

– Example: Discovering the busiest hours for a website.

17. Calculate event percentage by source:

– Explanation: This query calculates the percentage of events for each source in the application logs.

– Query: `index=logs sourcetype=app_logs | stats count by source | eval percentage=(count*100)/sum(count)`

– Example: Understanding the contribution of different sources to the total log count.

18. Identify long-running queries:

– Query: `index=database_logs sourcetype=query_logs | search duration>1000`

– Explanation: This query retrieves query logs where the duration is greater than 1000 milliseconds.

– Example: Finding slow-performing database queries.

19. Visualize data changes over time:

– Query: `index=inventory sourcetype=stock_changes | timechart span=1d sum(change_in_stock)`

– Explanation: This query creates a time chart of daily changes in stock inventory.

– Example: Monitoring stock level fluctuations.

20. Calculate the percentage of successful requests:

– Query: `index=web_logs sourcetype=access_logs | eval success=if(status=200,1,0) | stats avg(success*100)`

– Explanation: This query calculates the percentage of successful HTTP requests.

– Example: Assessing the success rate of web requests.

21. Search for logs with specific keywords:

– Query: `index=logs sourcetype=app_logs “important” OR “urgent”`

– Explanation: This query retrieves logs containing either “important” or “urgent” from the application logs.

– Example: Finding critical messages in application logs.

– Query: `index=security sourcetype=authentication | stats count by user | sort -count | head 10`

– Explanation: This query counts authentication events for each user, sorts them by count in descending

order, and shows the top 10.

– Example: Identifying the most active users in the authentication logs.

23. Calculate request rate over time:

– Query: `index=web_logs sourcetype=access_logs | timechart span=1h count`

– Explanation: This query creates a time chart displaying the request count per hour.

– Example: Monitoring the incoming request rate to a web server.

24. Search for logs with certain values in multiple fields:

– Query: `index=logs sourcetype=app_logs error AND status=500 AND user=”john.doe”`

– Explanation: This query retrieves logs with specific conditions in the “error,” “status,” and “user” fields.

– Example: Finding errors with a certain status and user in application logs.

25. Calculate average request rate by hour:

– Query: `index=web_logs sourcetype=access_logs | eval hour=strftime(_time, “%H”) | stats avg(count) by hour`

– Explanation: This query calculates the average request count per hour for web logs.

– Example: Analyzing the average traffic by hour.

26. Filter logs based on different time ranges:

– Query: `index=logs sourcetype=app_logs (earliest=-1h@h latest=now) OR (earliest=-24h@h latest=-23h@h)`

– Explanation: This query retrieves logs from the past hour and logs from the same hour the previous day.

– Example: Comparing current hour logs with the same hour’s logs from the previous day.

27. Calculate the percentage of 404 errors:

– Query: `index=web_logs sourcetype=access_logs status=404 | stats count | eval percentage=(count*100)/total_count`

– Explanation: This query calculates the percentage of HTTP 404 errors in access logs.

– Example: Assessing the frequency of “Not Found” errors.

28. Identify the most common referrers:

– Query: `index=web_logs sourcetype=access_logs | top referrer`

– Explanation: This query identifies the most common referrers in web access logs.

– Example: Finding out which websites are referring users to your site.

29. Calculate error rate by application:

– Query: `index=logs sourcetype=app_logs (status=”error” OR status=”failed”) | stats count by application`

– Explanation: This query calculates the error count for each application in the logs.

– Example: Measuring error rates in different applications.

30. Search for specific field values within a range:

– Query: `index=logs sourcetype=app_logs response_time>=500 response_time<=1000`

– Explanation: This query retrieves logs with response times between 500 and 1000 milliseconds.

– Example: Finding logs with response times in a specific range.

31. Identify top pages by response time:

– Query: `index=web_logs sourcetype=access_logs | stats avg(response_time) by page | sort -avg(response_time) | head 10`

– Explanation: This query calculates the average response time for each page, sorts in descending order, and shows the top 10.

– Example: Identifying the slowest-loading pages on a website.

– Query: `index=security sourcetype=authentication | stats avg(duration)`

– Explanation: This query calculates the average login duration from authentication logs.

– Example: Analyzing the typical time users spend during login.

33. Search for logs containing specific keywords and excluding others:

– Query: `index=logs sourcetype=app_logs “error” AND NOT “debug”`

– Explanation: This query retrieves logs with “error” but excludes those with “debug” from the application logs.

– Example: Filtering out debugging messages from error logs.

34. Identify the most frequent errors by type:

– Query: `index=logs sourcetype=error_logs | top error_type`

– Explanation: This query identifies the most frequent error types in error logs.

– Example: Discovering common error categories.

35. Calculate daily transaction volume:

– Query: `index=financial sourcetype=transactions | timechart span=1d count`

– Explanation: This query creates a time chart showing the count of transactions per day.

– Example: Monitoring the daily transaction activity.

– Query: `index=logs sourcetype=app_logs user=”jane.doe”`

– Explanation: This query retrieves logs related to the user “jane.doe” from application logs.

– Example: Finding logs associated with a particular user.

37. Identify the most common user agents:

– Query: `index=web_logs sourcetype=access_logs | top user_agent`

– Explanation: This query identifies the most common user agents in web access logs.

– Example: Determining the most popular browsers and devices.

38. Calculate downtime duration:

– Query: `index=system_logs sourcetype=uptime | stats sum(downtime_duration)`

– Explanation: This query calculates the total downtime duration from system logs.

– Example: Measuring the cumulative time a system was down.

39. Search for logs during business hours:

– Query: `index=logs sourcetype=app_logs hour>=9 hour<=17`

– Explanation: This query retrieves logs generated between 9 AM and 5 PM.

– Example: Analyzing application behavior during work hours.

40. Identify changes in stock beyond a threshold:

– Query: `index=inventory sourcetype=stock_changes change_in_stock<-10 OR change_in_stock>10`

– Explanation: This query finds stock changes exceeding a threshold of +/- 10 units.

– Example: Detecting significant inventory fluctuations.

41. Calculate average memory usage by host:

– Query: `index=performance sourcetype=system_stats | stats avg(memory_usage) by host`

– Explanation: This query calculates the average memory usage for each host.

– Example: Monitoring memory usage across different servers.

42. Search for logs with a specific event ID:

– Query: `index=windows_logs sourcetype=event_logs event_id=4625`

– Explanation: This query retrieves logs with Event ID 4625 from Windows event logs.

– Example: Investigating failed login attempts in Windows.

43. Identify top URLs by visit count:

– Query: `index=web_logs sourcetype=access_logs | top url`

– Explanation: This query identifies the most visited URLs in web access logs.

– Example: Understanding popular content on a website.

44. Calculate average order value:

– Query: `index=ecommerce sourcetype=orders | stats avg(order_value)`

– Explanation: This query calculates the average value of orders in e-commerce data.

– Example: Analyzing the typical order amount.

45. Search for logs with specific text patterns:

– Query: `index=logs sourcetype=app_logs “

ERROR” AND (“connection” OR “timeout”)`

– Explanation: This query retrieves logs containing “ERROR” and either “connection” or “timeout.”

– Example: Finding error messages related to connection issues or timeouts.

46. Identify top user agents by count:

– Query: `index=web_logs sourcetype=access_logs | top limit=10 user_agent`

– Explanation: This query identifies the top 10 user agents by count in web access logs.

– Example: Listing the most common user agents.

47. Calculate average query execution time:

– Query: `index=database_logs sourcetype=query_logs | stats avg(execution_time)`

– Explanation: This query calculates the average query execution time from database query logs.

– Example: Monitoring the efficiency of database queries.

– Query: `index=ecommerce sourcetype=order_logs order_id=”12345″`

– Explanation: This query retrieves logs related to the order with ID “12345” from order logs.

– Example: Finding logs associated with a particular order.

49. Identify unique error messages:

– Query: `index=logs sourcetype=error_logs | dedup error_message`

– Explanation: This query removes duplicate error messages from error logs.

– Example: Identifying distinct error messages in logs.

50. Calculate average request time per URL path:

– Query: `index=web_logs sourcetype=access_logs | rex field=url_path “(?P<url_path>\/[^\/]+)” | stats avg(response_time) by url_path`

– Explanation: This query extracts the URL path from the URL and calculates the average response time per path.

– Example: Analyzing response times based on different URL paths.

These queries cover a variety of scenarios and operations you can perform using Splunk. Remember that real-world data may require adjustments to these queries to match your specific use case and data structure.

Table of Contents