Report combining Windows computers where OS is known or not

[Glenn_Gagne]

Hi need to generate a report to find some assets on my network and I'm unable to find the right operator.

For example, If I use this report, I could find over 50 Windows assets, where these assets are not necessary fully scanned (missing credentials or error or unknown errors). It's exactly what I need :

Select Top 1000000 tblassets.AssetID,
tblassets.AssetName,
tblassets.IPAddress,
tblassets.Lastseen,
tblassets.Domain,
tblassets.Description,
tblassets.Firstseen,
tblassetcustom.Location,
tblassetcustom.Manufacturer,
tblassetcustom.Model
From tblassets
Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
Where tblassets.Domain <> 'Domain1' And tblassets.Domain <> 'Domain2' And
tblassets.Domain <> 'Workgroup1' And tblassetcustom.State = 1 And
tsysassettypes.AssetTypeName = 'Windows'

But in this previous report, I don't have the "Operating System" details. If I simply add the table to get OS information, this new report below will drop my result to less than 10 Windows assets !!!

Select Top 1000000 tblassets.AssetID,
tblassets.AssetName,
tblassets.IPAddress,
tblassets.Domain,
tblassets.Description,
tblassets.Firstseen,
tblassetcustom.Location,
tblassetcustom.Manufacturer,
tblassetcustom.Model, tblOperatingsystem.Caption
From tblassets
Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
Inner Join tblOperatingsystem On
tblassets.AssetID = tblOperatingsystem.AssetID
Where tblassets.Domain <> 'Domain1' And tblassets.Domain <> 'Domain2' And
tblassets.Domain <> 'WorkgroupX' And tblassetcustom.State = 1 And
tsysassettypes.AssetTypeName = 'Windows'

In the logical, the reason is the INNER JOIN "Inner Join tblOperatingsystem On
tblassets.AssetID = tblOperatingsystem.AssetID" where some assets are not properly identified their OS and this table is empy/not existing. 

Does it possible to create a report where I can have a "INNER JOIN" exclusion... like "If the AssetID exist in tblOperatingSystem give me the result" And "If the AssetID not exist in tblOperatingSystem give me even the result but with empty/unknown result" ... ?

Then, for those Windows assets where the OperatingSystem table is not existing, for some machines where the network scan could reach information, I can sometime observe the "OS, Build and/or version". Which table I can use to retrieve these informations in the report ? *(See picture in attachment)

If I can't make this report, this situation require to open each asset one by one to validate his OS. I can't export the result also...

[Mister_Nobody]

Try left Join