Splunk Search

Finding duration of report with time stamps

Bliide
Path Finder

I have SSRS logs and I am attempting to find the duration of the sent reports.

I have a log file that tells me when the report was executed:

library!WindowsService_11!54f8!05/27/2015-08:00:05:: i INFO: Schedule bb16994f-b699-4bb1-b2ad-9fbcb85ed4fb executed at 05/27/2015 08:00:05.

and a log file that shows when the report was completed:

library!WindowsService_11!54f8!05/27/2015-08:00:05:: i INFO: Schedule bb16994f-b699-4bb1-b2ad-9fbcb85ed4fb execution completed at 05/27/2015 08:00:05.

This is not the best example since the execution and completion were instant in this case. I want to be able to show how long each report took from execution to completion. My goal is to have an alert that is triggered when the reports take more than 10 seconds to complete. Any ideas on how to go about this? I assume it is an eval but I have never attempted anything like this before with Splunk.

Tags (3)
1 Solution

woodcock
Esteemed Legend

This should do it:

... | rex "INFO: Schedule (?<ReportID>[\S]*) " | stats earliest(_time) AS startTime latest(_time) AS endTime BY ReportID| eval delta = endTime - startTime | where delta > 10

View solution in original post

stephanefotso
Motivator

Hello! just run this search and save it as an Alert. Note that all searches are concerned here.

    | rest /services/search/jobs |where runDuration>10

Thanks

SGF

Bliide
Path Finder

The SSRS logs I am attempting to find the duration on and alert off of are on a forwarded server. This solution appears to work if the logs were local. Thank you for the suggestion.

0 Karma

woodcock
Esteemed Legend

This should do it:

... | rex "INFO: Schedule (?<ReportID>[\S]*) " | stats earliest(_time) AS startTime latest(_time) AS endTime BY ReportID| eval delta = endTime - startTime | where delta > 10

Bliide
Path Finder

The suggested line does work to get me the duration but it is pulling duration from only one set of reports. There are many scheduled reports run each day. There is no human readable report name in the log. For example the report name in the logs I listed above is "bb16994f-b699-4bb1-b2ad-9fbcb85ed4fb". Any suggestion on how to get it to look at each report on each day and then check the duration.

The search I have is the following: index=foo executed OR completed "bb16994f-b699-4bb1-b2ad-9fbcb85ed4fb"

That is showing the 2 log entries for each day but when I add the suggested solution it seems to only pull from the top 2 entries. Do I need to change something before the rex to make it look at the logs of every day or do I need to add something in the suggested language to make it look at each day? I doubt I can add span=1d. Possibly use buckets?

0 Karma

aweitzman
Motivator

I think you need a by ReportID at the end of your stats command.

Bliide
Path Finder

That did it. Thank you both for the suggestions.

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...