{"id":932,"date":"2017-09-28T06:00:02","date_gmt":"2017-09-28T13:00:02","guid":{"rendered":"http:\/\/www.sqldbpros.com\/?p=932"},"modified":"2017-09-27T11:25:33","modified_gmt":"2017-09-27T18:25:33","slug":"ssrs-parse-parameters-column-from-executionlog","status":"publish","type":"post","link":"http:\/\/sqldbpros.com\/wordpress\/2017\/09\/ssrs-parse-parameters-column-from-executionlog\/","title":{"rendered":"SSRS: Parse parameters column from ExecutionLog"},"content":{"rendered":"<p>Ever worked on a quick little project, come across what you assume is a common need and think \"I'll just google up some code that someone else has written and then be off to the races\"? \u00a0Have you done this and not been able to find the code you're looking for? Well, that was me yesterday. I needed to parse a few parameters from the URL string stored in the SQL Server Reporting Services ExecutionLog3 Parameters column and thought for sure I'd be able to quickly find the code floating around the interweb. Strangely, no luck. I tried a few different google searches like: \"SSRS Parse ExecutionLog Parameters\", \"SQL Server Reporting Services Parse URL Parameters\", \"ExecutionLog Parameters column\". Nothing. And thus this post was born.<\/p>\n<p>There are a few reasons why one might want to parse out the parameters from the ReportServer ExecutionLog table. In our case we were interested in seeing which parameters were the most frequently used which would allow us to optimize the reports for those queries. In particular we were curious what date ranges were most commonly used by our report users.<\/p>\n<p>The scalar function below takes two parameters, the column to search (typically, ExecutionLog.Parameters) and the specific parameter to search for. \u00a0For example, If we want to know the typical date range being used by our users when they run the \"Top Perfomers\" we can use the following query to parse out the StartDate and EndDate parameters in the Parameters column:<\/p>\n<p>&nbsp;<\/p>\n<pre>select top 1000 Parameters\r\n, dbo.ParseSSRSExecutionLogParameters('StartDate',parameters)\r\n, dbo.ParseSSRSExecutionLogParameters('EndDate',parameters)\r\n, TimeDataRetrieval\r\n, [RowCount]\r\nfrom dbo.ExecutionLog3\r\nwhere ItemPath = 'Top Performers'<\/pre>\n<p>&nbsp;<\/p>\n<p>This will return a result set which looks like:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-933\" src=\"http:\/\/www.sqldbpros.com\/wordpress\/wp-content\/uploads\/2017\/09\/Parse-Execution-Log-results-e1506535284218.png\" alt=\"\" width=\"700\" height=\"217\" \/><\/p>\n<p>Ta da! Beautiful right?<br \/>\nA few specs:<\/p>\n<p>The parameter can be entered with or without a trailing equal sign. \"StartDate\" or \"StartDate=\" will return the same result.<\/p>\n<p>If the entered parameter name is not found the function will return a null.<\/p>\n<p>The function handles the most common URL encoding characters:<\/p>\n<p>%2F is converted to forward slash (typically found in dates)<\/p>\n<p>%20 is converted to a space<\/p>\n<p>%3A is converted to a colon (typically found in time data).<\/p>\n<p>Questions? Comments? Enhancements? Love it? Hate it? Let me know in the comments section!<\/p>\n<pre>IF object_id('dbo.ParseSSRSExecutionLogParameters', 'FN') is not null\r\nBEGIN\r\n\tDROP FUNCTION dbo.ParseSSRSExecutionLogParameters\r\nEND\r\nGO\r\n\r\nCREATE FUNCTION dbo.ParseSSRSExecutionLogParameters\r\n    (\r\n     @ParameterToFind varchar(1000)\r\n    ,@ParameterColumnName ntext\r\n\t)\r\nRETURNS VARCHAR(1000)\r\nAS\r\n\/*\r\n\r\nDesc: Parse a specific parameter out of a URL string. \r\nSpecifically designed to work with the SSRS ExecutionLog table and \r\n\"parameters\" column.\r\n\r\nSample exec:\r\nselect top 1000 dbo.ParseSSRSExecutionLogParameters('StartDate',parameters)\r\n, dbo.ParseSSRSExecutionLogParameters('GroupId',parameters)\r\n, dbo.ParseSSRSExecutionLogParameters('LocaleId',parameters)\r\n, dbo.ParseSSRSExecutionLogParameters('EndDate',parameters)\r\n, dbo.ParseSSRSExecutionLogParameters('VehicleId',parameters)\r\n, parameters\r\n, ItemPath\r\n, *\r\nfrom dbo.ExecutionLog3\r\norder by 1 desc,2 desc,3 desc,4 desc,5 desc\r\n\r\n\r\n--------------------------\r\n2017-09-27\tPhil Steffek\t\tUS25315 - Initial version.\r\n\r\n*\/\r\n    BEGIN\r\n\t--If the input doesn't end with an equal then add it\r\n\tif substring(reverse(@ParameterToFind), 1,1)&lt;&gt;'='\r\n\tbegin\r\n\tselect @ParameterToFind=@ParameterToFind+'='\r\n\tend\r\n\r\n        DECLARE @ParameterValue VARCHAR(1000)\r\n\t\r\n        SELECT\r\n            @ParameterValue = case when @ParameterColumnName like '%'+@ParameterToFind+'%'\r\n\t\tthen \r\n\t\treplace(\r\n\t\t\treplace(\r\n\t\t\t\treplace (\t\r\n\t\t\t\t\tsubstring(\r\n\t\t\t\t\t\t\t@ParameterColumnName -- string\r\n\t\t\t\t\t\t\t,charindex(@ParameterToFind,@ParameterColumnName)+len(@ParameterToFind) --starting position\r\n\t\t\t\t\t\t\t, \tcase\r\n\t\t\t\t\t\t\t\t\twhen charindex('&amp;'\t, @ParameterColumnName, charindex(@ParameterToFind,@ParameterColumnName)+len(@ParameterToFind))=0 --If an \"&amp;\" is not found then use the end of the string for the ending position.\r\n\t\t\t\t\t\t\t\t\tthen len( cast(@ParameterColumnName as varchar(8000))) - (charindex(@ParameterToFind,@ParameterColumnName)+len(@ParameterToFind))+1\r\n\t\t\t\t\t\t\t\t\telse  (charindex('&amp;'\t, @ParameterColumnName, charindex(@ParameterToFind,@ParameterColumnName)+len(@ParameterToFind))) \r\n\t\t\t\t\t\t\t\t\t\t\t- (charindex(@ParameterToFind,@ParameterColumnName)+len(@ParameterToFind))\r\n\t\t\t\t\t\t\t\tend\r\n\t\t\t\t\t\t\t)\r\n\t\t\t\t\t, '%2F'\r\n\t\t\t\t\t, '\/'\r\n\t\t\t\t)\r\n\t\t\t, '%20'\r\n\t\t\t, ' '\r\n\t\t\t)\r\n\t\t, '%3A'\r\n\t\t, ':'\r\n\t\t)\r\n\t\telse null\r\n\t\tend \r\n\r\n        RETURN @ParameterValue \r\n\r\n\r\n    END \r\n\r\nGO\r\n<\/pre>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Ever worked on a quick little project, come across what you assume is a common need and think \"I'll just google up some code that someone else has written and then be off to the races\"? \u00a0Have you done this and not been able to find the code you're looking for? Well, that was me [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[22,43,8,3,58],"tags":[20,10,4,60,9],"class_list":["post-932","post","type-post","status-publish","format-standard","hentry","category-businss-intelligence","category-microsoft-sql-server","category-performance-tuning","category-scripts","category-ssrs-reporting-services","tag-business-intelligence","tag-ms-sql","tag-performance","tag-reporting-services","tag-sql-server"],"_links":{"self":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/932","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/comments?post=932"}],"version-history":[{"count":5,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/932\/revisions"}],"predecessor-version":[{"id":938,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/932\/revisions\/938"}],"wp:attachment":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/media?parent=932"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/categories?post=932"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/tags?post=932"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}