Report for list of active users who don't have laptop asset
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
05-11-2022 11:17 PM
I have tried adding the related list condition (asset -> assigned to) & filter conditions (active is true, model category is laptop) but it is showing all the users who have laptop asset as well.
Any alternate solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
05-11-2022 11:23 PM
Hi,
Asset table means alm_asset right
So can you share screenshot what it is showing currently and what you want to be shown?
Regards
Ankur
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
05-11-2022 11:30 PM
check this KB and enhance for count as 0
Report on alm_asset table with users having more than 1 asset
this link has solution using database view shared 8 years ago which
Report of users without an asset
Regards
Ankur
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
05-11-2022 11:33 PM
sure will do it.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
05-11-2022 11:23 PM
Hi
To do this you would need to create a Database View. I actually answered a question very similar to this recently in another forum and am planning to do a blog post, but have not yet had the opportunity. This topic is also covered in the next release of our Asset Management class, as it is a bit of common information that Asset Managers want to report on.
Here are the reasons you need a Database View with a left join for this:
- You want to report based on users, but the one-to-many relationship here means that the assets point to a user. The user record does not have a direct reference to the assets.
- The view allows you to merge the tables based on the sys_id of the user matching the assigned_to of the asset so that each match is displayed as a single row.
- The left join allows all users to display, even if they do not have a corresponding asset, which is vital to the report in this case.
I have an update set, created on Eureka, if you want to take a look at a sample of what I put together in responding to the other question, which was to see all users without an assigned computer. It should give you an idea of what to do.
Here is a screenshot of the Database View I created to do this and the Try It so you can see what columns I selected to display in the view. You can easily create a report that shows where Name is empty to get the information you need.
Mark my answer correct & Helpful, if Applicable.
Thanks,
Sandeep