Fixing SQL report to go back into Lansweeper

[shuriken101]

Hi Guys,

I was working on a inventory report in Lansweeper, when I ran into issues with PCs that have multiple video cards, showing up as multiple line items on the report.

So I handed the report over to our SQL guy, and this is what we got back (see below).
The only problem is, Lansweeper does not like this code.
So instead, he built this report in SQL Server Manager Studio, then uses a few lines of code in Lansweeper to call this report.

Is there any way to modify the code to put the whole report back into Lansweeper?

Thanks,
Chris

Report in Lansweeper
--------------------

Select Top 1000000 *
From WC_ComputerInventory
Order By WC_ComputerInventory.AssetName


Report in SQL Server Management Studio
--------------------------------------

USE [lansweeperdb]
GO

/****** Object: View [dbo].[WC_ComputerInventory] Script Date: 2/18/2015 11:14:43 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



ALTER VIEW [dbo].[WC_ComputerInventory] AS
Select
A.AssetID
, A.AssetName
, tblState.Statename As Status
, tblADComputers.Description As DescriptionAD
, tblADusers1.Displayname As LastSignedIn
, tblAssetCustom.Manufacturer
, tblAssetCustom.Model
, tblAssetCustom.PurchaseDate As PurchaseDate
, tblAssetCustom.Warrantydate As WarrantyExpires
, A.Processor As CPU
, A.NrProcessors As CPUs
, CAST((A.Memory/1024) AS DECIMAL(19, 3)) As MemoryGB
, VC.VideoCard
, AR.AdapterRAM As Video_GB
, tsysOS.OSname
, tblAssetCustom.Custom1 As AssetTag
, tblAssetCustom.Serialnumber As SerialNum
, tblADComputers.Comment

From
tblAssets A
Inner Join tblAssetCustom On A.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = A.Assettype
Inner Join tblAssetGroupLink On A.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID = tblAssetGroupLink.AssetGroupID
Left Join tblADusers On tblAssetCustom.Custom2 = tblADusers.Username
Left Join tblADusers tblADusers1 On A.Username = tblADusers1.Username
Left Join tsysOS On A.OScode = tsysOS.OScode
Inner Join tblADComputers On A.AssetID = tblADComputers.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.[State]

OUTER APPLY
(
SELECT TOP 1
SUBSTRING((SELECT
', ' + LEFT( CAST((((VC.AdapterRAM/1024)/1024)/1024) AS DECIMAL(19, 3)), 100)
FROM
tblVideoController VC
WHERE
VC.AssetID = A.AssetID
AND VC.AdapterRAM IS NOT NULL
AND VC.Caption NOT LIKE '%LANDesk%'
AND VC.Caption NOT LIKE '%DameWare%'
ORDER BY
VC.Caption
FOR XML PATH('')), 2, 100000) AS AdapterRAM
) AR

OUTER APPLY
(
SELECT TOP 1
SUBSTRING((SELECT
', ' + VC.Caption
FROM
tblVideoController VC
WHERE
VC.AssetID = A.AssetID
AND VC.Caption IS NOT NULL
AND VC.Caption != ''
AND VC.Caption NOT LIKE '%LANDesk%'
AND VC.Caption NOT LIKE '%DameWare%'
ORDER BY
VC.Caption
FOR XML PATH('')), 2, 100000) AS VideoCard
) VC
Where
tblAssetCustom.Manufacturer != 'VMware, Inc.'
And tsysAssetTypes.AssetTypename = 'windows'
And tblState.Statename != 'Retired'
And tblAssetGroups.AssetGroup = 'O_Walnut Creek'




GO

[Daniel_B · Accepted solution]

If your Lansweeper database is running on SQL server Express or higher edition (which it is in your case), you can use Stuff() in order to display several subquery results in one single column.

An example can be found in this report about connected monitors.