Splunk is widely used by organizations to monitor and troubleshoot IT infrastructure and applications. It is employed in many industries, such as healthcare, finance, and retail, to gain insights into their operations, security, and compliance and make data-driven decisions.
In this post, let’s see how to create Retrospective dashboard queries in Splunk with a simple scenario with a sample data.
Highlights:
|
What Is Splunk?
Splunk is a software designed to collect, analyze, and visualize large amounts of machine-generated data, such as log files, network traffic data, and sensor data. It offers a wide range of capabilities for searching, analyzing, and visualizing data, as well as building and deploying custom applications.
The software can be deployed on-premises or in the cloud, and offers a wide range of APIs and integrations with other systems, enabling users to collect data from various sources easily. It indexes and correlates information in a container, making it searchable, and enables the generation of alerts, reports, and visualizations.
Additionally, Splunk has a large and growing ecosystem of add-ons and integrations with other tools, making it a popular choice for organizations that need a flexible and scalable data analysis solution.
PrimeSoft’s Expertise on Splunk
PrimeSoft has good expertise on Splunk as we have helped our customers monitor and troubleshoot alerts received from multiple systems in both Production and Non-Production environments for business-critical applications.
Our experts have helped customers analyze, set up, rationalize, and perfect the alerts for maximizing the coverage of applications and infrastructure monitoring with effective alerts put into the right place. They have also been instrumental in creating various monitoring and reporting dashboards in Splunk, helping key customer stakeholders by offering critical business insights in a dashboard. Based on the lessons learned, our expert is sharing how to create retrospective dashboard queries in Splunk.
Retrospective Data Analysis Demo
To draw insights and make informed decisions, one must retrospectively look at historical data to uncover trends, patterns, and relationships.
Sample Data
In this demo, let’s use sample data with Users’ Login count for every hour throughout the year 2022. We will start with uploading the sample data in a CSV file to the Splunk Cloud (trial version). You can use the Splunk free trials available by following the process here.
The data contains only two columns, DateTime and UserLogins, as shown below.
Figure 1
Uploading the Sample Data
To upload data, navigate to the Searching and Reporting view in Splunk Cloud, click on Settings to see the Add Data option, and follow the process.
Figure 2
Building Queries and Visualizing Data
Let’s build queries to help us visualize data trends in the following scenarios.
- Scenario 1 – Weekday & Weekly Trend Comparison (when log timestamp and _time field are same)
- Scenario 2 – Monthly Trend Comparison (when log timestamp and _time field are NOT same)
- Scenario 3 – Monthly Trend Comparison (when log timestamp and _time are in matching but need to be reported in different time zone)
Scenario 1
Let us assume that the log timestamp values are perfect and match with the default “_time” field in the Splunk index. We can use just two commands, timechart, and timewarp, to achieve retrospective data comparison.
The Query for Weekday Data Comparison
| source=”SampleDataforSplunkBlog.csv” host=”si-i-0494c4ce0352be1f5.prd-p-w97tj.splunkcloud.com” sourcetype=”csv” | timechart values(UserLogins) span=1h | timewrap w | where strftime(_time, “%A”) == “Monday” |
- The first line of the query fetches the data.
- The second line time chart command creates data points based on the UserLogins field for every hour (Span=1h) and the timewrap command wraps the data points created earlier by week (w).
- Finally, in line three, we filter based on the weekday we wish to compare the metrics.
- Additionally, we confined the search period to only 3 weeks. We will see the same weekday data from previous weeks if we increase it.
Figure 3.1
Figure 3.2
Similarly, we can build a query for Weekly User Login data comparison.
The Query for Weekly Data Comparison
| source=”SampleDataforSplunkBlog.csv” host=”si-i-0494c4ce0352be1f5.prd-p-w97tj.splunkcloud.com” sourcetype=”csv” | timechart values(UserLogins) span=1h | timewrap w |
- The first line of the query fetches the data.
- In the second line, the timechart command creates data points based on the UserLogins field for every hour (Span=1h) and the timewrap command wraps the data points created earlier by week (w).
- We confined the search period to only 3 weeks and if we increase it, we will observe data from previous weeks for comparison.
Figure 4.1
Figure 4.2
By updating the Span to 1 day and adding the User log-in values by replacing values() with the sum() function, we can generate aggregated data points per day to compare over 3 or more weeks based on the search period.
| source=”SampleDataforSplunkBlog.csv” host=”si-i-0494c4ce0352be1f5.prd-p-w97tj.splunkcloud.com” sourcetype=”csv” | timechart sum(UserLogins) span=1day | timewrap w |
Figure 5.1
Figure 5.2
Read more about timechart and timewarp commands in the Splunk documentation through the hyperlinks. These commands are highly customizable through inputs, which can help us to build many versions of Retrospective Metrics.
Scenario 2
Let’s assume that the log timestamp values are NOT matching with the default “_time” field in the Splunk index. In this case, we will have to use additional commands such as eval, chart, etc.
The Query for Monthly Data Comparison
| source=”SampleDataforSplunkBlog.csv” host=”si-i-0494c4ce0352be1f5.prd-p-w97tj.splunkcloud.com” sourcetype=”csv” | eval month = strftime(strptime(DateTime, “%d-%m-%Y %H:%M” ),”%B”), dayofmonth = strftime(strptime(DateTime, “%d-%m-%Y %H:%M” ),”%d”) |chart sum(UserLogins) as userloginsfortheday over dayofmonth by month limit=0 |
- The first line of the query fetches the data.
- In the second line, we are using the strftime and strptime Data-Time functions from Splunk to calculate the Day of the Month and Month fields.
- Finally, in line three, we use the chart command to calculate the Sum of User Logins per day and chart it over the day of the month for each month to compare.
- Additionally, we confined the search period to only 3 months, and by increasing it, we will be able to observe daily data from prior months.
Figure 6.1
Figure 6.2
Scenario 3
Let’s assume that the log timestamp and _time field in the Splunk index match, but need to be reported in the preferred time zone. Timestamp in this example is in the GMT zone and the query will help report it in the user’s preferred time zone in the user settings.
The Query for Monthly Data Comparison
| source=”SampleDataforSplunkBlog.csv” host=”si-i-0494c4ce0352be1f5.prd-p-w97tj.splunkcloud.com” sourcetype=”csv” | eval dtime=strftime(_time, “%d-%m-%Y %H:%M”) | eval DTimeZone=dtime+” GMT” | eval DTime=strftime(strptime(DTimeZone,”%d-%m-%Y %H:%M %Z”),”%d-%m-%Y %H:%M %Z”) | eval month = strftime(strptime(DTime, “%d-%m-%Y %H:%M %Z” ),”%B”), dayofmonth = strftime(strptime(DTime, “%d-%m-%Y %H:%M %Z” ),”%d”) |chart sum(UserLogins) as userloginsfortheday over dayofmonth by month limit=0 |
- The first line of the query fetches the data.
- In line two, we are using strftime to convert from UNIX to the general Date-Time format.
- In line three, we are adding the Time zone as GMT, assuming the logs are in GMT.
- In line four, we are using strftime and strptime to convert the Date-Time from GMT to the current user’s time zone setting.
- In line five, we are calculating the Month and Day of the Month.
- Finally, in line six, we use the chart command to calculate the Sum of User Logins per day and chart it over the day of the month by each month in order to compare them.
- Additionally, we confined the search period to only 3 months, and by increasing it, we will be able to see daily data from previous months.
Figure 7.1
Figure 7.2
Thank you for reading. We hope you learned something new that will help you build retrospective queries to analyze your data patterns.