Report for list of active users who don't have laptop asset

meghana penchal
Kilo Contributor

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 

4 REPLIES 4

Ankur Bawiskar
Tera Patron
Tera Patron

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

Regards,
Ankur
Certified Technical Architect  ||  9x ServiceNow MVP  ||  ServiceNow Community Leader

@meghana penchala 

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 @Sandeep Dutta  has mentioned

@Sandeep Dutta : It would be nice if you share the reference link from where you have picked the approach so that member can directly navigate to that link for further details/help.

Report of users without an asset

find_real_file.png

Regards
Ankur

Regards,
Ankur
Certified Technical Architect  ||  9x ServiceNow MVP  ||  ServiceNow Community Leader

Community Alums
Not applicable

sure will do it.

Community Alums
Not applicable

Hi @meghana penchala ,

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.

 

 

Screen Shot 2014-07-11 at 7.56.46 AM.png

 

 

 

Screen Shot 2014-07-11 at 7.58.36 AM.png

Mark my answer correct & Helpful, if Applicable.

Thanks,

Sandeep