→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
shuriken101
Engaged Sweeper II
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


1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
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.

View solution in original post

1 REPLY 1
Daniel_B
Lansweeper Alumni
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.