Adding a URL to a csv

[hedge01]

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?

[hedge01]

That worked perfectly! thank you! We are using Topdesk for our helpdesk system.

[Hendrik_VE · Accepted solution]

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]