TSQL to identify user's favourite reports

Nick shared this question 2 months ago
Answered

Hi team,

Are you able to help with and share the query to identify all the report favorites of a particular user?

Upon migrating a customer's environment, we noticed that the favorites for some users appear to be missing, hence, the need to grab this information from directly from the DB.

From what I can see, part of this information is kept in PersonFavourite table.

Thanks,

Nick

Comments (22)

photo
1

Hi Nick,

Thanks for reaching out. User favourite details are indeed stored in the PersonFavourite table.

Here's a query you can use to determine favourite assignments by user. These are results that users explicitly toggled as a favorite in the UI:

SELECT * FROM PersonFavourite WHERE IpPerson = '5' AND FavouriteTypeCode = 'FAVOURITE';
/079bfee132abf217e491daa3710820c5

/94f876327ad07614066b3f05ce1ba77a

You'll just replace IpPerson with the desired user value (as per the Person or IpClass table).

If you want to see all user favorite assignments, i.e., one's you'd see here:

/7071a518980ebd37d4171ae6ba873bb0

You'd run the same query without the 'AND FavouriteTypeCode = 'FAVORITE';' portion.

I suspect most users would be looking for the actual favorites they explicitly chose to add to favorites though, so for that you can run the first query to obtain those results, then put them into an insert statement in the migrated environment. Of course you'd also have to make sure the ContentId's are the same. In my example, I'd have to check whether ReportId '60687' exists in migrated environment. If it does not, in the original environment you'd search '60687' in ReportHeader table to find report name, search that in the migrated environment, then use that ReportId instead.

Hopefully this explanation makes sense. Please let me know if you have any questions on this.

Regards,

Mike

photo
1

Hi Mike,

Thanks for that.

We were able to fix the issue at hand by changing the role from "Public Content Writer & Collaborator" to "System Admin", and back to "Public Content Writer & Collaborator".

However, although we can now access and see the favourites, the timeline box display the favourites as 0.

Any idea where this number is taken from so that we can check the DB?

Thanks,

Nick

photo
1

Hi Mike,

Here's a screenshot.

Thanks,

Nick

photo
1

Hi Mike,

I think that would be a known issue.

Can you let me know in what build this is fixed:

17658Fixed an issue caused when a user in a system where no favourites are saved tries to access the timeline, profile settings, tasks

Thanks,

Nick

photo
1

Hi Nick,

Nice find! I was not aware of this defect - this is almost certainly related. This defect was fixed in 8.0.5, 7.4.13 and 9.0.4. I don't see the build you're on specified in this ticket, but it's before any of these builds, please upgrade and you should be good to go.

Please let me know how goes and whether you have any additional questions.

Regards,

Mike

photo
1

Hi Nick,

I just wanted to check in and see how things are going with this.

Regards,

Mike

photo
1

Hi Mike,

I am still seeing the issue in 8.06 build.

Let me confirm on this though.

Thanks,

Nick

photo
1

Hi Nick,

Thanks for the update. Please do.

Regards,

Mike

photo
1

Hi Mike,

We have opened the following case to have the defect fixed:

21036-02-25

Thanks,

Nick

photo
1

Hi Nick,

Thanks for letting me know! Considering this, I'll go ahead and close this ticket out then, but please don't hesitate to reach out with any other questions or concerns.

Regards,

Mike

photo
1

Hi Mike,

It seems the issue where the user cannot see their favorites happens for multiple users.

We do not know the true extent, and we currently need another way of fixing the issue rather than toggling the permission to admin and back to the original role for each user that is having the problem.

It would be great if we could a response on this as soon as possible as the issue is a bit time sensitive.

Let me know if you need anything else.

Thanks,

Nick

photo
1

Hi Nick,

This issue was replicated and logged as a Defect in ticket ID 21036. As such, it is currently in the Dev Team's hands. I see in the internal task the importance of this is noted, the priority is set to High and it's already assigned to someone on the Dev Team, so I suspect there will be further updates on this soon.

That said, since this defect is already being tracked in that ticket, I'm going to close this ticket out as it's ostensibly a duplicate at this point. If you have any further questions or concerns surrounding this, please respond in 21036.

Thanks,

Mike

photo
1

Hi Mike,

The display count showing as 0 is a defect - correct.

The issue we have is users cannot see their favourite reports. After toggling the permissions, then users were able to see their favourite reports, while the count was still displaying as 0.

What we need is a way to find all these users that cannot see their favourite reports, and fix the issue without having to toggle the permission for each one of them as this would be a tedious manual task for potentially hundreds of users.

Let me know if it is still unclear.

Thanks,

Nick

photo
1

Hi Nick,

I think I understand now: in this case you're adding Favourite Reports, favourites remains '0' (the defect logged in 21036), and they're not showing up in the timeline unless you toggle the permission. Is my understanding now correct?

If so, my follow-up question is: when you say the favourites show up upon toggling the permission, what you're referring to is changing the user Role to Admin, then back to its original Role, correct?

Unfortunately, I can't replicate this, even in 8.0.6, but if my understanding above is correct, it should be possible to toggle this via the following SQL queries in SQL Server:

UPDATE StaffMemberRole SET EndDate = GETDATE() WHERE IpEmployee = '13151' AND RoleCode = 'YFREPORTCONSUMER';
INSERT StaffMemberRole VALUES (13151, 1, 'YFADMIN', GETDATE(), '9999-12-31', 1);
UPDATE StaffMemberRole SET EndDate = GETDATE() WHERE IpEmployee = '13151' AND StartDate = GETDATE() AND RoleCode = 'YFADMIN';
INSERT StaffMemberRole VALUES (13151, 1, 'YFREPORTCONSUMER', GETDATE(), '9999-12-31', 1);

These queries are updating the current role to be no longer in effect, inserting the new role, updating the new role to no longer be in effect, then inserting the old role back and making it current.

You'll of course just need to swap out the IpEmployee ID's (by identifying users in the IpClass and/or Person table), and the corresponding RoleCode's.


That said, given the nature of how Role permissions are assigned (via UPDATE then INSERT queries), it's still a little bit of manual work to do this, but it's likely still quicker than going through the UI.

Anyways, please let me know if this appears to be what you're looking for.

Regards,

Mike

photo
1

Hi Mike,

I think I understand now: in this case you're adding Favourite Reports, favourites remains '0' (the defect logged in 21036), and they're not showing up in the timeline unless you toggle the permission. Is my understanding now correct?

Nick: No, I'm specifically referring to reports that had already been added as favourites in the past. When we tested with a different user, adding new reports as favourites worked fine ie we could see the report in the favourites section but the count still displayed 0

If so, my follow-up question is: when you say the favourites show up upon toggling the permission, what you're referring to is changing the user Role to Admin, then back to its original Role, correct?

Nick: That's correct.

Unfortunately, I can't replicate this, even in 8.0.6, but if my understanding above is correct, it should be possible to toggle this via the following SQL queries in SQL Server:

Nick: Thanks Mike. I think we could give this a try. When I had a quick look in the DB, it seems the records are there, but unsure how toggling the role played a part in this

Thanks,

Nick

photo
1

I have uploaded an extract of the DB tables under REQ000001114340.zip on the FTP site if you want to have a look.


  • ipperson 74612 - Fixed by toggling the permission
  • ipperson 117651 - Not fixed yet (Will be testing the query against this user)


Is it possible to find out whether it could be the defect that is causing the behavior where users cannot see their existing reports added as favourites?

Thanks,

Nick

photo
1

Hi Mike,

Based on the latest information provided, are you also able to help us with a query to try identify other users that might have this issue since we would also need to run the query for multiple users if the issue is widespread.

Thanks,

Nick

photo
1

Hi Nick,

Thanks for your response. Good find! I can certainly provide a query to identify users who had pre-existing favourites they had manually defined. The only piece I'm not sure of is what date would be considered pre-existing, so you'll have to plug in that date yourself, but here's the query:

SELECT * FROM PersonFavourite WHERE CreateByCode = 'MANUAL' AND StartTimestamp < '2020-09-23';
Just swap out the date with your desired date and this will return the list of all items each user manually added to the Favourites list.

Please let me know how goes and whether you require anything else here.

Regards,

Mike

photo
1

Hi Nick,

I just wanted to check in and see how things are going with this.

Regards,

Mike

photo
1

Hi Nick,

I'm going to go ahead and mark this one as Answered since I haven't heard back from you, but if you have further questions or concerns on this, if you respond, it will re-open the case and put it back in my queue and I'll be happy to help.

Regards,

Mike

photo
1

Hi Mike,

That is fine.

Thanks for your support.

Regards,

Nick

photo
1

Hi Nick,

Thanks for confirming!

Regards,

Mike