As If Report Manager is a real web interface. You know I think it’s not. But, if it’s what you have to use, then… you do. And I gotta.
As If its provided web server was a “normal” web server, like Apache or IIS, with HTTP logs. Well, it kinda is that. But MS seems to have gone out of their way to make this difficult.
Why I’m analyzing SSRS HTTP logs this week
We have a need to figure out how much of our intranet SSRS traffic is coming in from VPN requests. I was as surprised as anybody to realize I couldn’t just pull client IP address information out of execution history in the ReportServer database. Users, yes, IP addresses, no.
The way to do this properly is using web server logs.
Some people will tell you that you should write really complex and silly RDL-embedded code to accomplish this, or hack Report Manager pages or something (I did find quite a few references to this type of approach on the Internet, very quickly). Those hacks might be fine if you only care about one or two reports, or one particular type of access, but it’s not going to get you a comprehensive result.
So, rinse and repeat:
The way to do this properly is using web server logs.
As far back as SSRS 2008 R2, if memory serves, and forward MS has made appropriate logs available. Look up Report Server HTTP Log in MSDN for your particular version of SSRS, and you’ll see it.
Here’s how they made it difficult
Go on, read the MSDN pages. They provide the important information that HTTP logging isn’t turned on by default, as well as the not-very-difficult syntax you need to use in the ReportingServicesService.exe.config file to enable it.
Unfortunately, every single version of that MSDN page — as well as every blog or clever little wanna-be guru that uncritically re-posted the syntax from those MSDN pages — have a bug in the same ReportingServicesService.exe.config syntax. They make it very easy for you to copy and paste the syntax, complete with bug. HIghlight is mine, to draw your attention to the critical line with the problem:
See that space before clientip in the list of HttpTraceSwitches? If you don’t remove that space, you ain’t going to get the clientip. Which, of course, is exactly the value I wanted to get.
After much gnashing of teeth, a period during which I got everything except the clientip in my log, I finally realized that this list must exactly match the attributes you want. No spaces allowed. Probably it’s case-sensitive as well, although I didn’t tempt fate.
Here’s how I’d like to make this easier for you
I’m going to attach an XML format file to this post, which you should find useful for importing the contents of the HTTP logs into a staging table. Actually, I’ll attach two copies of the format file; one matching the Unicode log I got and one that would work in UTF8 if you find there’s an option for getting the logs to be UTF8 instead of UTF16. If so, please drop me a line; the logs would be half the size if this switch is allowed and I don’t see that I’d be missing any important information.
Your syntax for import would be something like this:
SELECT * INTO [SSRS_HTTPLog_Staging] FROM
OPENROWSET (
BULK 'Your fully pathed log file name',
FORMATFILE = 'fully pathed format file name',
FIRSTROW = 2 ) xx;
… and I’ll also attach a little RDL that I wrote to take advantage of this information. The RDL doesn’t use the staging table directly; from that data I move the data into a table called SSRS_HTTP that has a couple of extra columns parsed out besides all the raw staging content. Here’s the basic query for doing that, so you can see how the extra/parsed columns are derived.
SELECT DISTINCT [SSRSDate]
,[SSRSTime]
,[C_IP]
,[C_USER]
,[S_IP]
,[S_PORT]
,[S_HOST]
,[METHOD]
,[URI_STEM]
,[URI_QUERY]
,[STATUS]
,[BYTES]
,[TIME_TAKEN]
,[PROTOCOL_VERSION]
,[USERAGENT]
,[COOKIE_RECEIVED]
,[COOKIE_SENT]
,CASE WHEN Referrer LIKE '%http%' THEN
SUBSTRING(Referrer,1,CHARINDEX('http',Referrer,1) -1)
ELSE Referrer END AS ClientInfo
,CASE WHEN Referrer LIKE '%http%' THEN
SUBSTRING(Referrer,CHARINDEX('http',Referrer,1),4000)
ELSE null END AS Referrer
INTO [SSRS_HTTPLog]
FROM [SSRS_HTTPLog_Staging]
… any and all additional code is in the RDL. Enjoy!
BCP.SSRSHTTP-Unicode.xml (2.79 kb)