Custom reporting - Asset Groups and AD Description

[mrdisco]

Hello, all. I have posted before about this custom report I've been building, and I have it mostly right where I want it. I went in and manually added devices to Asset Groups and I can run a report for the various departments in my organization. This report includes eveything from VOIP phones to desktops to network equipment, and security cameras.

I thought it would be helpful to add in the AD description, but when I add made the below change, the only devices displayed are devices that HAVE an AD description. It removes the non-domain-joined devices. I'm not super familiar with SQL, but I think it has something to do with the Inner Join section. I appreciate the help!


Here is the initial report that shows all devices:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssets.Username As [Last Login],
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate,
Cast(DateDiff(dd, tblAssetCustom.PurchaseDate, GetDate()) / 365.2425
As numeric(8,1)) As Age,
Case
When tblAssetCustom.PurchaseDate Is Null Then '#ff00ff'
When DateAdd(dd, 5 * 365.2425, tblAssetCustom.PurchaseDate) < GetDate() Then
'#ff0000'
End As foregroundcolor,
tblAssetCustom.State
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblAssetGroups.AssetGroup Like 'Human Resources'
Order By tsysAssetTypes.AssetTypename,
tblAssets.AssetName

Here is the report where I'm trying to add in AD Description:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblADComputers.Description,
tblAssets.Username As [Last Login],
tblAssetCustom.PurchaseDate,
Cast(DateDiff(dd, tblAssetCustom.PurchaseDate, GetDate()) / 365.2425
As numeric(8,1)) As Age,
Case
When tblAssetCustom.PurchaseDate Is Null Then '#ff00ff'
When DateAdd(dd, 5 * 365.2425, tblAssetCustom.PurchaseDate) < GetDate() Then
'#ff0000'
End As foregroundcolor,
tblAssetCustom.State
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblAssetGroups.AssetGroup Like 'Human Resources'
Order By tsysAssetTypes.AssetTypename,
tblAssets.AssetName

[shawn_s]

Josh, Did you ever find a solution, I'm looking for something similar?