Device name link

[Darth_Jed]

I probably have overlooked this somewhere, but what SQL code do you need to create a custom report that lists printer names that are clickable links to their detail pages? For example, I'm trying to create a report that will list the printer names in the first column, followed by all the other fields. If I look at Device: Printer Status, that report has the effect I'm looking for. When I build my report in a similar fashion, the displayname is not converted into a clickable link. Here's my code.

Select Top 1000000 tblCustDevices.Displayname, tblCustDevices.Ipaddress, tblCustDevices.Model, tblstate.Statename, tblCustDevices.LastSeen, tblCustDevices.DeviceKey, tblCustDevices.DeviceKey As DeviceKey1 From tblCustDevices Inner Join tblstate On tblCustDevices.State = tblstate.State Inner Join tsysDevicetypes On tsysDevicetypes.ItemType = tblCustDevices.Devicetype Inner Join web40AllIPLocationsDev On tblCustDevices.DeviceKey = web40AllIPLocationsDev.DeviceKey Where tblstate.Statename = 'active' And tblCustDevices.DeviceKey Like '%.%' And tblCustDevices.Devicetype = 16 Order By tblCustDevices.Displayname

[Waterscorpion]

Awesome, that did it! Thanks

[Hemoco]

Please try the report below instead. You must include the tblComputers.Computername field in your report.

Select Top 1000000 tblComputers.Computername, tblComputers.Computer, tblComputerSystemProduct.IdentifyingNumber, tblCompCustom.Custom1 As AssignedTo, tblComputerSystemProduct1.Name, tblComputerSystemProduct1.Vendor, tblCompCustom.PurchaseDate, tblCompCustom.Warrantydate, WarrantyStatus = Case When tblCompCustom.PurchaseDate + 1095 <= GetDate() + 90 And tblCompCustom.PurchaseDate + 1095 > GetDate() Then 'Needs Replacing Soon' When tblCompCustom.PurchaseDate + 1095 <= GetDate() Then 'Needs Replacing' End, web40AllIPLocations.IPLocation From tblComputers Left Join tblComputerSystemProduct On tblComputers.Computername = tblComputerSystemProduct.Computername Left Join tblCompCustom On tblComputers.Computername = tblCompCustom.Computername Inner Join tblComputerSystemProduct tblComputerSystemProduct1 On tblComputers.Computername = tblComputerSystemProduct1.Computername Inner Join web40AllIPLocations On web40AllIPLocations.Computername = tblComputers.Computername Where Case When tblCompCustom.PurchaseDate + 1095 <= GetDate() + 90 And tblCompCustom.PurchaseDate + 1095 > GetDate() Then 'Needs Replacing Soon' When tblCompCustom.PurchaseDate + 1095 <= GetDate() Then 'Needs Replacing' End Is Not Null

[Waterscorpion]

I know this is an old post at this point, but what would be the appropriate input to make the computer name linkable in the following report?

Select Top 1000000 tblComputers.Computer, tblComputerSystemProduct.IdentifyingNumber, tblCompCustom.Custom1 As AssignedTo, tblComputerSystemProduct1.Name, tblComputerSystemProduct1.Vendor, tblCompCustom.PurchaseDate, tblCompCustom.Warrantydate, WarrantyStatus = Case When tblCompCustom.PurchaseDate + 1095 <= GetDate() + 90 And tblCompCustom.PurchaseDate + 1095 > GetDate() Then 'Needs Replacing Soon' When tblCompCustom.PurchaseDate + 1095 <= GetDate() Then 'Needs Replacing' End, web40AllIPLocations.IPLocation From tblComputers Left Join tblComputerSystemProduct On tblComputers.Computername = tblComputerSystemProduct.Computername Left Join tblCompCustom On tblComputers.Computername = tblCompCustom.Computername Inner Join tblComputerSystemProduct tblComputerSystemProduct1 On tblComputers.Computername = tblComputerSystemProduct1.Computername Inner Join web40AllIPLocations On web40AllIPLocations.Computername = tblComputers.Computername Where Case When tblCompCustom.PurchaseDate + 1095 <= GetDate() + 90 And tblCompCustom.PurchaseDate + 1095 > GetDate() Then 'Needs Replacing Soon' When tblCompCustom.PurchaseDate + 1095 <= GetDate() Then 'Needs Replacing' End Is Not Null

[Darth_Jed]

That's exactly what I was missing! Thank you!

[Hemoco · Accepted solution]

Please try the report below. To link the name to the device page the fields "Displayname" and "DeviceKey" must be included in your report and "Displayname" must be given the alias "Device Name".

Select Top 1000000 tblCustDevices.Displayname As [Device Name], tblCustDevices.Ipaddress, tblCustDevices.Model, tblstate.Statename, tblCustDevices.LastSeen, tblCustDevices.DeviceKey From tblCustDevices Inner Join tblstate On tblCustDevices.State = tblstate.State Inner Join tsysDevicetypes On tsysDevicetypes.ItemType = tblCustDevices.Devicetype Inner Join web40AllIPLocationsDev On tblCustDevices.DeviceKey = web40AllIPLocationsDev.DeviceKey Where tblstate.Statename = 'active' And tblCustDevices.DeviceKey Like '%.%' And tblCustDevices.Devicetype = 16 Order By tblCustDevices.Displayname