Updating custom report.

[ffA]

Hi,

I had some issues with my database when upgrading to 5.1 (logs filled up database and had to start from fresh). Because of this a custom report wasn't converted.

I have had a couple of attempts to do this manually but I can't find all of the tables that I need.

Could you please have a look at the Query and point me in the direction of the new table names.

Thank you

SELECT TOP (1000000) dbo.upgrade_tblComputers.Computername, dbo.upgrade_tblCompCustom.Location, dbo.upgrade_tblCompCustom.Department,
dbo.upgrade_tblComputers.ComputerUnique, dbo.upgrade_Web40OSName.Compimage AS icon, dbo.tblADusers.Username,
dbo.upgrade_tblVideoController.Caption AS [Graphics Card], dbo.upgrade_tblVideoController.DriverVersion,
CEILING(dbo.upgrade_tblVideoController.AdapterRAM / 1024 / 1024) AS [Ram MB], CAST(dbo.upgrade_tblVideoController.CurrentHorizontalResolution AS bigint)
AS [H Rez], dbo.upgrade_tblVideoController.CurrentVerticalResolution AS [V Rez]
FROM dbo.upgrade_tblVideoController INNER JOIN
dbo.upgrade_tblComputers ON dbo.upgrade_tblVideoController.Computername = dbo.upgrade_tblComputers.Computername INNER JOIN
dbo.upgrade_tblOperatingsystem ON dbo.upgrade_tblComputers.Computername = dbo.upgrade_tblOperatingsystem.Computername INNER JOIN
dbo.upgrade_web40ActiveComputers ON dbo.upgrade_tblComputers.Computername = dbo.upgrade_web40ActiveComputers.Computername INNER JOIN
dbo.upgrade_Web40OSName ON dbo.upgrade_Web40OSName.Computername = dbo.upgrade_tblComputers.Computername INNER JOIN
dbo.tblADusers ON dbo.tblADusers.Username = dbo.upgrade_tblComputers.Username AND
dbo.tblADusers.Userdomain = dbo.upgrade_tblComputers.Userdomain INNER JOIN
dbo.upgrade_tblCompCustom ON dbo.upgrade_tblComputers.Computername = dbo.upgrade_tblCompCustom.Computername
ORDER BY [Ram MB] DESC

[ffA]

HI,

Thanks for the assistance with the report.

This is working great, much appreciated.

[Hemoco]

Adding "distinct" should fix this:

Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Username,
tblDisplayConfiguration.Caption As GPU,
tblDisplayConfiguration.DriverVersion As Driver,
Ceiling(tblVideoController.AdapterRAM / 1024 / 1024) As [RAM MB],
tblVideoController.CurrentHorizontalResolution As [H REZ],
tblVideoController.CurrentVerticalResolution As [V REZ],
tblAssets.Lastseen,
tblDisplayConfiguration.lastchanged
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblDisplayConfiguration On tblAssets.AssetID =
tblDisplayConfiguration.AssetID
Inner Join tblVideoController
On tblAssets.AssetID = tblVideoController.AssetID
Order By tblAssets.AssetName

[ffA]

Hi,

I have re-built the report but I am having a couple of issue, could you please assist?

There is a duplication of some of the machines in the report.

Multiple different graphics cards in a single machine are being reported the same graphics card with one missing some details.

Thanks in advance

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Username,
tblDisplayConfiguration.Caption As GPU,
tblDisplayConfiguration.DriverVersion As Driver,
Ceiling(tblVideoController.AdapterRAM / 1024 / 1024) As [RAM MB],
tblVideoController.CurrentHorizontalResolution As [H REZ],
tblVideoController.CurrentVerticalResolution As [V REZ],
tblAssets.Lastseen,
tblDisplayConfiguration.lastchanged
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblDisplayConfiguration On tblAssets.AssetID =
tblDisplayConfiguration.AssetID
Inner Join tblVideoController
On tblAssets.AssetID = tblVideoController.AssetID
Order By tblAssets.AssetName