Computer and devices belonging to a user

[Finn_Kopke]

How can I create a report that shows which computers and devices that are delivered to a user. I record all devices (iPhone, iPad, HTC, Routers) to users and write their name in the contact field (under devices)

Thanks in advance

[Finn_Kopke]

Thanks. I see an colom - identifier - how can I remove this?

Is it possible to select a user when you run the report eg. in a dropdown windows.

I want to use the report as documentation for all delivered IT equipmet to a single user as they have to sign

Best

Finn

[Hemoco]

Finn.Kopke wrote:
Thanks. I see an colom - identifier - how can I remove this?

To remove the specified column, use the SQL code below for the report instead.

Select Top 1000000 DERIVEDTBL.Contact, DERIVEDTBL.Domain, DERIVEDTBL.Name As
Sysname, DERIVEDTBL.Type, DERIVEDTBL.IP, DERIVEDTBL.IPLocation,
DERIVEDTBL.Lastseen, DERIVEDTBL.icon
From (Select Web40OSName.Compimage As icon, tblComputers.LastknownIP As IP,
web40AllIPLocations.IPLocation As IPLocation, tblComputers.Computer As
Name, Web40OSName.OSname As Type, tblComputers.Lastseen,
tblComputers.IPNumeric, tblComputers.Domain, tblCompCustom.Custom10 As
Contact
From tblComputers Inner Join
web40AllIPLocations On tblComputers.Computername =
web40AllIPLocations.Computername Inner Join
web40ActiveComputers On tblComputers.Computername =
web40ActiveComputers.Computername Inner Join
Web40OSName On tblComputers.Computername = Web40OSName.Computername
Inner Join
tblCompCustom On tblComputers.Computername = tblCompCustom.Computername
Union
Select tsysDevicetypes.ItemTypeIcon10 As icon, tblCustDevices.Ipaddress As
IP, web40AllIPLocationsDev.IPLocation As IPLocation,
tblCustDevices.Displayname As Name, tsysDevicetypes.ItemTypename As Type,
tblCustDevices.LastSeen, tblCustDevices.IPNumeric, '' As domain,
tblCustDevices.Contact As contact
From tsysDevicetypes Inner Join
tblCustDevices On tsysDevicetypes.ItemType = tblCustDevices.Devicetype
Inner Join
web40AllIPLocationsDev On tblCustDevices.DeviceKey =
web40AllIPLocationsDev.DeviceKey
Where tblCustDevices.State = 1) DERIVEDTBL
Order By DERIVEDTBL.Contact, DERIVEDTBL.IPNumeric

Finn.Kopke wrote:
Is it possible to select a user when you run the report eg. in a dropdown windows.

This is not currently possible unfortunately. If you wish to report on a specific user, you will have to add his or her name to the report criteria. An example can be seen below. Replace "YourUser" with the name of the user you would like to report on.

Select Top 1000000 DERIVEDTBL.Contact, DERIVEDTBL.Domain, DERIVEDTBL.Name As
Sysname, DERIVEDTBL.Type, DERIVEDTBL.IP, DERIVEDTBL.IPLocation,
DERIVEDTBL.Lastseen, DERIVEDTBL.icon
From (Select Web40OSName.Compimage As icon, tblComputers.LastknownIP As IP,
web40AllIPLocations.IPLocation As IPLocation, tblComputers.Computer As
Name, Web40OSName.OSname As Type, tblComputers.Lastseen,
tblComputers.IPNumeric, tblComputers.Domain, tblCompCustom.Custom10 As
Contact
From tblComputers Inner Join
web40AllIPLocations On tblComputers.Computername =
web40AllIPLocations.Computername Inner Join
web40ActiveComputers On tblComputers.Computername =
web40ActiveComputers.Computername Inner Join
Web40OSName On tblComputers.Computername = Web40OSName.Computername
Inner Join
tblCompCustom On tblComputers.Computername = tblCompCustom.Computername
Union
Select tsysDevicetypes.ItemTypeIcon10 As icon, tblCustDevices.Ipaddress As
IP, web40AllIPLocationsDev.IPLocation As IPLocation,
tblCustDevices.Displayname As Name, tsysDevicetypes.ItemTypename As Type,
tblCustDevices.LastSeen, tblCustDevices.IPNumeric, '' As domain,
tblCustDevices.Contact As contact
From tsysDevicetypes Inner Join
tblCustDevices On tsysDevicetypes.ItemType = tblCustDevices.Devicetype
Inner Join
web40AllIPLocationsDev On tblCustDevices.DeviceKey =
web40AllIPLocationsDev.DeviceKey
Where tblCustDevices.State = 1) DERIVEDTBL
Where DERIVEDTBL.Contact = 'YourUser'
Order By DERIVEDTBL.Contact, DERIVEDTBL.IPNumeric

[Hemoco]

Please try the report below.

Select Top 1000000 DERIVEDTBL.Contact, DERIVEDTBL.Domain, DERIVEDTBL.Name As
Sysname, DERIVEDTBL.Type, DERIVEDTBL.IP, DERIVEDTBL.IPLocation,
DERIVEDTBL.Lastseen, DERIVEDTBL.computername As Identifier, DERIVEDTBL.icon
From (Select Web40OSName.Compimage As icon, tblComputers.LastknownIP As IP,
web40AllIPLocations.IPLocation As IPLocation, tblComputers.Computer As
Name, Web40OSName.OSname As Type, tblComputers.Lastseen,
tblComputers.IPNumeric, Cast(tblComputers.Computername As varchar) As
computername, tblComputers.Domain, tblCompCustom.Custom10 As Contact
From tblComputers Inner Join
web40AllIPLocations On tblComputers.Computername =
web40AllIPLocations.Computername Inner Join
web40ActiveComputers On tblComputers.Computername =
web40ActiveComputers.Computername Inner Join
Web40OSName On tblComputers.Computername = Web40OSName.Computername
Inner Join
tblCompCustom On tblComputers.Computername = tblCompCustom.Computername
Union
Select tsysDevicetypes.ItemTypeIcon10 As icon, tblCustDevices.Ipaddress As
IP, web40AllIPLocationsDev.IPLocation As IPLocation,
tblCustDevices.Displayname As Name, tsysDevicetypes.ItemTypename As Type,
tblCustDevices.LastSeen, tblCustDevices.IPNumeric,
tblCustDevices.DeviceKey As computername, '' As domain,
tblCustDevices.Contact As contact
From tsysDevicetypes Inner Join
tblCustDevices On tsysDevicetypes.ItemType = tblCustDevices.Devicetype
Inner Join
web40AllIPLocationsDev On tblCustDevices.DeviceKey =
web40AllIPLocationsDev.DeviceKey
Where tblCustDevices.State = 1) DERIVEDTBL
Order By DERIVEDTBL.Contact, DERIVEDTBL.IPNumeric

To use the specified report, do the following:
• Open the Lansweeper configuration console and select Reports & Alerts\Report Builder. Hit the “New” button.
• Copy the SQL code provided and paste it at the bottom of the newly created report, replacing the default SQL code.
• Click somewhere near tblComputers so the new code applies.
• Give the report a “View name” and a “Report name” and hit the “Save” button.
• Double-click on the report in the report list to see its results and export options.

[Finn_Kopke]

Yes I'm using Custom10 for Username

[Hemoco]

Which field are you using to track computer contacts? There is a "contact" field for devices, but not for computers. Did you rename one of the CustomX fields?