Reporting on Users and Related Computers

[roxaneamanda]

Hi,

I need to create a report on Users with their related Computers. I know that I can use the computer table as I have a reference on this table to the assigned_to user, this will show all machines with only users which have a machine, but I want to do this the other way around. I have a user report and I want to add the machines\machines assigned to those users (i realise that i there are 2 or more computers I will get multiple users but that's OK) I want all the users and then the machines related to these users. I have tried creating a database view to joint the sys_user and the cmdb_ci_computer table but I'm not sure what field to put in the 'where clause' ive tried cmdb_ci_computer.assigned_to=sys_user.user_name but it does not seem to like this clause and i'm not certain which table to put the clause on either?

Can anyone help, I would really appreciate it!

Thanks

[gaidem]

Can you simply run your report and group by user?

[roxaneamanda]

Hi Thanks for your reply but I am trying to use an ODBC connection into Excel to get this information out. I have lots of different User fields but I need to add in the machine or machines.

I thought about creating a new field on the user form which would contain the machine or machines that the user has related to the, but i'm not sure how to do this either?

[gaidem]

Sorry, I haven't yet used the JDBC driver, so I haven't seen the outputs. I'm sure someone else on the community has some feedback!

[Kandi Markwood]

Hi Roxanne, 

If you go to the " All Assets" table on the left side of Service Now and in the filter use the drop down menu and click on "Assigned to" then "is not empty". This will pull a report of all computers and the users associated to each device. My recommendation is to design your list view of the table with the below criteria in whatever order works for you. Then you can export to excel or CSV depending on the file size. Hope this helps!