Software subscription report issue - 2 users with same name

[velcro]

Hi

Looking for some guidance on a workaround for an issue I have with a report which is designed to flag users with more than one subscription for the various Visio Online plans available to us.

Report is returning data from the sys_users table based on related list content which I think is queried from the samp_sw_subscription table. View of the report build shown below:

If you can't read/expand it, the related list condition is using Software Subscription -> User

We have 2 users with the same name each with a subscription and it's returning one of them in the belief that they are the same user rather than being able to pick up that they are different (for example via the UPN).
It doesn't look like there's any alternatives on the related list part of the report that query this table. 

I can structure the report to use User Allocations -> Assigned to, but I want to see it from the samp_sw_subscription as that's a feed in from the external portal to my understanding.

The user data in Software Entitlements and Software Subscriptions views relating to these subs is confirmed as correct.

Any guidance on what i can do here is greatly appreciated.

Thanks in advance.

Matt

[vaishali231]

Hey @velcro 

I believe the issue is not with the samp_sw_subscription data itself, but with how the report is using the sys_user display value.

A user's Display name is not unique. Two different sys_user records can have the same name, but they will have different sys_id, User ID/UPN, or email values.

I would first verify the two users and their subscription records:

1. Open both users with the same name and compare their sys_id.
2. Open the corresponding records in samp_sw_subscription.
3. Check the User reference field on each subscription.
4. Confirm that the two subscriptions point to different sys_user.sys_id values.

If the sys_id values are different, then the subscription data is correctly related and I would change the report design.

Instead of starting from sys_user and using a Related List Condition, try creating the report directly on:

Software Subscription [samp_sw_subscription]

Then filter for the required Visio plans and group/aggregate by a unique user field, preferably User ID/UPN rather than Display name.

For example:

Table: Software Subscription [samp_sw_subscription]

Filter:

Display name contains Visio Online Plan

Group by:

User.User ID   (or UPN)

Aggregation:

Count

Condition:

Count > 1

For troubleshooting, I would also temporarily create a List report and add these columns:

User

User ID

Email / UPN

Subscription

If both users appear separately in the list, then the relationship is working correctly and the issue is most likely with the grouping/display configuration of the original report.

If both subscription records actually contain the same User sys_id, then I would investigate the SAM import/feed or the user-matching logic instead, because the report cannot distinguish two subscriptions that are genuinely associated with the same user record.

Since your requirement is specifically to report from samp_sw_subscription, I would recommend keeping that table as the report source rather than switching to User Allocations. This also ensures the report is based directly on the subscription data coming from your external feed.

The key thing to check is:

Different sys_id → investigate report/grouping configuration.

Same sys_id → investigate the subscription import/user matching process.

**********************************************************************************************************************************

If this response helps, please mark it as Accept as Solution and Helpful.

Doing so helps others in the community and encourages me to keep contributing.

Regards

Vaishali Singh

Servicenow Developer
Linkedin - https://www.linkedin.com/in/vaishali-singh-2273361bb

[velcro]

Thanks for the reply   

It might either be my lack of report writing skills or something that's not available in Zurich (though I can't see it in our test instance of Australia just now either), but I can't locate an aggregation setting on the options when progressing a report on the list format which is what I'm looking to return. I'm aware it's on other formats but none of them feel right for what I'm trying to achieve.

Maybe I'll need to try build this in PBI or something similar.

Thanks