cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
hedge01
Engaged Sweeper II
We need to make a CSV report with a bunch of information such as - Name, IS, Model, Manufacturer, Serial Number, Mac address etc. However we also need to generate a URL for each individual asset on that report that would link to that particular asset. This will be used as an import for a Service Desk system.

Does anyone know how we generate a URL for the report that could be exported to a CSV?
1 ACCEPTED SOLUTION
Hendrik_VE
Champion Sweeper III
I use this select statement in my export to Insight CMDB (replace <LS Website> and the port with your own site):

Select Top (10000) 
...
...
Concat('https://<LS Website>:84/asset.aspx?AssetID=',
Cast(tblAssets.AssetID As varchar)) As [LS URL]


This is the complete report, because it looks quite similar to what you're doing. What Service Desk system do you have btw?

Select Top (10000) tblAssets.AssetID,
tsysIPLocations.IPLocation,
tblAssets.AssetName As [Host Name],
tsysOS.OSname As [Operating System],
Left(tblAssets.SP, 30) As [Operating System Service Pack],
tblOperatingsystem.Caption,
tblOperatingsystem.Version As [Operating System Version],
tblOperatingsystem.OSLanguage As [Operating System Country Code],
tblAssets.Domain,
Left(tblAssetCustom.Serialnumber, 80) As [Serial Number],
Left(tblAssetCustom.Model, 80) As Model,
ProcCapacity.CPU As [CPU Type],
ProcCapacity.Name As [CPU Edition],
ProcCapacity.MaxClockSpeed As [CPU Speed],
ProcCapacity.NrOfProcessors As [Number CPUs],
tblSystemEnclosure.ChassisTypes,
tblComputersystem.Domainrole,
tblAssets.Memory As [Physical Memory],
tblAssets.Mac As [Mac Address],
tblAssets.Lastseen As [Last Modified Date Time],
tblAssets.Firstseen As [Created Date Time],
tsysAssetTypes.AssetTypename As AssetType,
tblSystemEnclosure.Manufacturer,
TsysChassisTypes.ChassisName As [Computer Type],
'Lansweeper' As [Asset Source],
tblAssets.IPAddress As [IP Address],
Left(tblBIOS.SMBIOSBIOSVersion, 50) As [BIOS Version],
tblBIOS.ReleaseDate As [BIOS Date],
tblBIOS.Manufacturer As BIOS,
tblAssetCustom.Custom2 As Building,
tblAssetCustom.Custom3 As Vendor,
tblAssetCustom.Custom4 As [System Coordinator],
tblAssetCustom.Custom5 As [Network Administrator],
tblAssetCustom.Custom6 As [System Administrator],
tblAssetCustom.Custom7 As [VLAN Name],
tblState.Statename As State,
Concat('https://<LS Website>:84/asset.aspx?AssetID=',
Cast(tblAssets.AssetID As varchar)) As [LS URL]
From tblAssets
Inner Join (Select tblAssets_1.AssetID,
Max(tblProcessor.MaxClockSpeed) As MaxClockSpeed,
Count(tblProcessor.WIN32_PROCESSORid) As NrOfProcessors,
Max(tblProcessor.MaxClockSpeed) As [total Proc Capacity],
Max(tblProcessor.Architecture) As Architecture,
Max(tblProcessor.Caption) As Name,
Max(Replace(Replace(tblProcessor.Name, '(R)', ''), '(TM)', '')) As CPU
From tblAssets As tblAssets_1
Inner Join tblProcessor On tblAssets_1.AssetID = tblProcessor.AssetID
Group By tblAssets_1.AssetID) As ProcCapacity On ProcCapacity.AssetID =
tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblSystemEnclosure On
tblAssets.AssetID = tblSystemEnclosure.AssetID
Inner Join TsysChassisTypes On TsysChassisTypes.Chassistype =
tblSystemEnclosure.ChassisTypes
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblOperatingsystem On
tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Order By [Host Name]

View solution in original post

2 REPLIES 2
hedge01
Engaged Sweeper II
That worked perfectly! thank you! We are using Topdesk for our helpdesk system.
Hendrik_VE
Champion Sweeper III
I use this select statement in my export to Insight CMDB (replace <LS Website> and the port with your own site):

Select Top (10000) 
...
...
Concat('https://<LS Website>:84/asset.aspx?AssetID=',
Cast(tblAssets.AssetID As varchar)) As [LS URL]


This is the complete report, because it looks quite similar to what you're doing. What Service Desk system do you have btw?

Select Top (10000) tblAssets.AssetID,
tsysIPLocations.IPLocation,
tblAssets.AssetName As [Host Name],
tsysOS.OSname As [Operating System],
Left(tblAssets.SP, 30) As [Operating System Service Pack],
tblOperatingsystem.Caption,
tblOperatingsystem.Version As [Operating System Version],
tblOperatingsystem.OSLanguage As [Operating System Country Code],
tblAssets.Domain,
Left(tblAssetCustom.Serialnumber, 80) As [Serial Number],
Left(tblAssetCustom.Model, 80) As Model,
ProcCapacity.CPU As [CPU Type],
ProcCapacity.Name As [CPU Edition],
ProcCapacity.MaxClockSpeed As [CPU Speed],
ProcCapacity.NrOfProcessors As [Number CPUs],
tblSystemEnclosure.ChassisTypes,
tblComputersystem.Domainrole,
tblAssets.Memory As [Physical Memory],
tblAssets.Mac As [Mac Address],
tblAssets.Lastseen As [Last Modified Date Time],
tblAssets.Firstseen As [Created Date Time],
tsysAssetTypes.AssetTypename As AssetType,
tblSystemEnclosure.Manufacturer,
TsysChassisTypes.ChassisName As [Computer Type],
'Lansweeper' As [Asset Source],
tblAssets.IPAddress As [IP Address],
Left(tblBIOS.SMBIOSBIOSVersion, 50) As [BIOS Version],
tblBIOS.ReleaseDate As [BIOS Date],
tblBIOS.Manufacturer As BIOS,
tblAssetCustom.Custom2 As Building,
tblAssetCustom.Custom3 As Vendor,
tblAssetCustom.Custom4 As [System Coordinator],
tblAssetCustom.Custom5 As [Network Administrator],
tblAssetCustom.Custom6 As [System Administrator],
tblAssetCustom.Custom7 As [VLAN Name],
tblState.Statename As State,
Concat('https://<LS Website>:84/asset.aspx?AssetID=',
Cast(tblAssets.AssetID As varchar)) As [LS URL]
From tblAssets
Inner Join (Select tblAssets_1.AssetID,
Max(tblProcessor.MaxClockSpeed) As MaxClockSpeed,
Count(tblProcessor.WIN32_PROCESSORid) As NrOfProcessors,
Max(tblProcessor.MaxClockSpeed) As [total Proc Capacity],
Max(tblProcessor.Architecture) As Architecture,
Max(tblProcessor.Caption) As Name,
Max(Replace(Replace(tblProcessor.Name, '(R)', ''), '(TM)', '')) As CPU
From tblAssets As tblAssets_1
Inner Join tblProcessor On tblAssets_1.AssetID = tblProcessor.AssetID
Group By tblAssets_1.AssetID) As ProcCapacity On ProcCapacity.AssetID =
tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblSystemEnclosure On
tblAssets.AssetID = tblSystemEnclosure.AssetID
Inner Join TsysChassisTypes On TsysChassisTypes.Chassistype =
tblSystemEnclosure.ChassisTypes
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblOperatingsystem On
tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Order By [Host Name]