AD Inventory Report Help

[chuff]

Hello,

With the help of searching the forms, I was able to create an AD Inventory Report that matches what we've been documenting in our previous spreadsheet. The Problem I'm having is that I'm missing a couple of scanned computers in Lansweeper that don't show up in report. Can anyone help with what might be the root cause?

From the looks of it, it appears that the report is not displaying assets that have instances of the AD username being logged in else where.

The Report list the following:

Computer Asset Name
User Name (last logged on User)
Last Logon Time
Active Directory Computer Description
IP Address
OS
Manufacturer
Model
Serial Number
Number of Processors
Processor Type


Code:

Select Top 1000000 tblAssets.AssetName,
SubQuery.Username,
SubQuery.Domain As Userdomain,
SubQuery.LastLogon,
tblADComputers.Description,
tblAssets.IPAddress As [IP Address],
tsysOS.OSname As OS,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber As Serial,
tsysOS.Image As icon,
tblAssets.NrProcessors As #CPU,
tblAssets.Processor,
tblAssets.AssetID
From tblCPlogoninfo
Inner Join (Select Top 1000000 tblCPlogoninfo.Username,
tblCPlogoninfo.Domain,
Max(tblCPlogoninfo.logontime) As LastLogon
From tblCPlogoninfo
Group By tblCPlogoninfo.Username,
tblCPlogoninfo.Domain) SubQuery On tblCPlogoninfo.Username =
SubQuery.Username And tblCPlogoninfo.Domain = SubQuery.Domain And
tblCPlogoninfo.logontime = SubQuery.LastLogon
Inner Join tblAssets On tblAssets.AssetID = tblCPlogoninfo.AssetID
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Where tblDiskdrives.Caption = 'c:' And tblAssetCustom.State = 1
Order By tblAssets.AssetName

[chuff · Accepted solution]

I have since modified my report with the following updated code and the missing computers now appear.

Code:

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Username As [Last Logged On User],
tblAssets.Userdomain As [User Domain],
tblADComputers.Description,
tblAssets.IPAddress As [IP Address],
tsysIPLocations.IPLocation As [IP Location],
tblOperatingsystem.Caption As [Operating system],
tblOperatingsystem.ServicePackMajorVersion As [Service Pack],
Case tblComputersystem.SystemType When 'x64-based PC' Then '64-bit'
When 'X86-based PC' Then '32-bit' End As Bit,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber As [Serial Number],
tblAssets.Memory,
tblAssets.NrProcessors As [# of Processors],
tblAssets.Processor
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric
And tsysIPLocations.EndIP >= tblAssets.IPNumeric
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where tblAssetCustom.State = 1
Order By tblAssets.AssetName

[AZHockeyNut]

could you capture a screenshot or two of the report run in your environment, and what you are comparing it to so perhaps we can have a look?