SSRS reports URL in database

Question

SSRS reports URL in database

sqlfriend

SSC Guru

Points: 52781
More actions
June 26, 2023 at 10:27 pm

#4220154

We would like to include userID in the reportURL, so that later we can see the report execution data for specific users.
I know we can query the database using ExecutionLog3, but I don't see a URL field included.
Any where store that information, what is the best way to get report execution counts for specific users.
Thanks,

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

The item path is the reportURL, you would just need to tag on the http part as a concatenation if you wanted it as a full clickable URL

Gerckens.3 SSC Rookie Points: 42 More actions · Answer 2

Instead of querying the ExecutionLog views, take a look at the table ExecutionLogStorage. There is a column there for UserName. Strangely it does not include the UserID, so if you need that GUID you would need to link back from ExecutionLogStorage to Users joining on UserName. Hope that is helpful.

"When it comes to report design and development, I have a list of pet peeves a mile wide. You might think the list would be a mile long, but I like to turn it on its side, sharpen the end, and poke people with it." - Me

sqlfriend SSC Guru Points: 52781 More actions · Answer 3

Thank you. ExecutionLog3 has username too. But since our ssrs reports are running from a .net application, the username is an application account not specific users. So that is why we want to pass the userID in the URL and hope it is going to be saved in some column of reportserver database. That is why I ask where the URL stored.

Thanks

sqlfriend SSC Guru Points: 52781 More actions · Answer 4

Ant-Green wrote:

The item path is the reportURL, you would just need to tag on the http part as a concatenation if you wanted it as a full clickable URL

Thanks, but that looks like only the path, no file extension like html, or asp etc at the end, of course I cannot see the username that is supposed to be at end of the URL.

We would like to capture username and also use the ExecutionLog3 view to get execution data.

The username in the view currently not help, because it is an application account. not a real user.

Thanks

Gerckens.3 SSC Rookie Points: 42 More actions · Answer 5

Can you create a hidden parameter in the report(s) to store the user id?

...reporturl?UserIDParameter=UserID1234

Then you could query it from the parameters column in ExecutionLog.

"When it comes to report design and development, I have a list of pet peeves a mile wide. You might think the list would be a mile long, but I like to turn it on its side, sharpen the end, and poke people with it." - Me

sqlfriend SSC Guru Points: 52781 More actions · Answer 6

Can you create a hidden parameter in the report(s) to store the user id?

-- Yes we thought about that, but then we will have to include that in all our existing reports, which are many.

is there an article to illustrate how to do this?

..reporturl?UserIDParameter=UserID1234

-- from above line, so all the report parameters will be include in the URL line, right?

Then you could query it from the parameters column in ExecutionLog.

-- if there are many parameters, will they all in the same column, how to parse it to just pull username?

Thanks,

Ant-Green SSC Guru Points: 113507 More actions · Answer 7

sqlfriend wrote:

Ant-Green wrote:
The item path is the reportURL, you would just need to tag on the http part as a concatenation if you wanted it as a full clickable URL
Thanks, but that looks like only the path, no file extension like html, or asp etc at the end, of course I cannot see the username that is supposed to be at end of the URL.
We would like to capture username and also use the ExecutionLog3 view to get execution data.
The username in the view currently not help, because it is an application account. not a real user.
Thanks

SSRS reports don't have an extension like HTML or ASP, you want to see which is the application URL which called that report, then you need to setup some form of logging at the IIS / w3svc side of things.

The SSRS URL is the http://mySSRSserver/reports/<ItemPath>, there is not .HTML/.ASP in the URL it doesn't work like that.

sqlfriend wrote:

Can you create a hidden parameter in the report(s) to store the user id?
-- Yes we thought about that, but then we will have to include that in all our existing reports, which are many. is there an article to illustrate how to do this?
..reporturl?UserIDParameter=UserID1234
-- from above line, so all the report parameters will be include in the URL line, right?
Then you could query it from the parameters column in ExecutionLog. -- if there are many parameters, will they all in the same column, how to parse it to just pull username?
Thanks,

Yes you would need to change each and every report to include a new parameter, best way is to go back to your source control and get all the projects and add a parameter to each, then in your web logic, you will need to ensure that however that report is run, also passes in a new parameter for the applications username to that new parameter. If the SSRS report front end is dynamic and lets uses see the report in a iFrame or something then that will potentially get a little trickier if the parameter is allowed to be changed or they flick between reports and the value doesn't populate, so you need to do a heck of a lot of redevelopment work to make this work correctly.

As for shredding the parameters out of execution log, you would have to do something like the usual SUBSTRING / CHARINDEX / DelimitedSplit8K stuff to split the parameters field into it's constituent parts and pull out the piece you actually need.

I would probably look at IIS / w3svc logging to see what you can do there first about what users are hitting what app pages which trigger which reports, probably a lot easier and less dev work than trying to shoehorn in something to each and every report.