Computer repot missing asstets

[Sandro]

Hi All,

I'm trying to create a report of company computers. It is working pretty fine but it is not showing computers with no user details. This happens example for computers that were just deployed and no user logged on and yet. In this case I the machine details I see <computername>\defaultuser0 in the last user field.

If I remove the the table tblADusers from the report, then all the computers appear correctly, but I do not have the DIsplayName field of each user in the report. Here is the report, can you please help?

Thanks!

Sandro

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblADusers.Displayname,
tsysOS.OSname As OS,
tblAssets.Version As Version,
tblOperatingsystem.Version As Build,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblDiskdrives.Caption As Drive,
Cast(tblDiskdrives.Size / 1024 / 1024 / 1024 As numeric) As TotalSizeGB,
Case
When Ceiling(tblDiskdrives.Freespace / (Case tblDiskdrives.Size
When 0 Then 1
Else tblDiskdrives.Size
End) * 100) < 10 Then '#f7caca'
When Ceiling(tblDiskdrives.Freespace / (Case tblDiskdrives.Size
When 0 Then 1
Else tblDiskdrives.Size
End) * 100) < 30 Then '#f7f0ca'
End As backgroundcolor,
Cast(tblDiskdrives.Freespace / 1024 / 1024 / 1024 As numeric) As FreeGB,
Ceiling(tblDiskdrives.Freespace / (Case tblDiskdrives.Size
When 0 Then 1
Else tblDiskdrives.Size
End) * 100) As [%SpaceLeft],
tblAssets.IPAddress,
tblAssets.Processor,
tblAssets.Memory,
tblAssetCustom.Manufacturer As Manifacturer,
tblAssetCustom.Model As Model,
tblAssets.Lastseen,
tblAssets.Lasttried,
tsysIPLocations.IPLocation,
tblAssetCustom.PurchaseDate
From tblAssets
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblOperatingsystem On
tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblADusers On tblAssets.Username = tblADusers.Username
Where tblDiskdrives.Caption = 'c:' And Cast(tblDiskdrives.Size / 1024 / 1024 /
1024 As numeric) <> 0 And tblState.Statename = 'Active' And
Case tblDiskdrives.DriveType
When 3 Then 'Local Disk'
End = 'Local Disk'
Order By tblAssets.AssetName

[RCorbeil · Accepted solution]

Change your "Inner Join tblADusers" to "LEFT Join tblADusers".

An inner join automatically filters out any results where the table you're linking against -- tblADUsers, in this case -- has no corresponding records. A left join includes the resulting row regardless of whether there's a match in the linked table.

[Sandro]

thank you so much! it worked perfectly! 😊