Show null vaules

[Graham_Ritchie]

I have added the Windows 10 end of life audit report to Lansweeper and am trying to edit it to include the OU the device is in. However, I do have some devices that are in Workgroups or have been imported from SCCM with no OU details. I would like them to still be in the report so I can filter for them. But every time I add the OU criteria it show ONLY devices with an OU field, hiding about 1000 devices. I have tried adding criteria to show NULL values but its not working. Is there a way I can achieve this? My report is below

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblOperatingsystem.Caption As OS,
tblAssets.Version,
Case
When tblAssets.Version In ('1507', '1511', '1607', '1703', '1709', '1903')
And (tblOperatingsystem.Caption Not Like '%LTSB%' And
tblOperatingsystem.Caption Not Like '%LTSC%') Then 'EOL'
When tblAssets.Version In ('1803', '1809') And
(tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then 'EOL'
When GetDate() < '2025-10-14' And (tblAssets.Version = '1507' Or
tblOperatingsystem.Caption Like '%2015%') And
(tblOperatingsystem.Caption Like '%LTSB%' Or
tblOperatingsystem.Caption Like '%LTSC%') Then Cast(DateDiff(DAY,
GetDate(), '2025-10-14') As nvarchar) + ' days remaining'
When GetDate() < '2026-10-13' And tblAssets.Version = '1607' And
(tblOperatingsystem.Caption Like '%LTSB%' Or
tblOperatingsystem.Caption Like '%LTSC%') Then Cast(DateDiff(DAY,
GetDate(), '2026-10-13') As nvarchar) + ' days remaining'
When GetDate() < '2029-01-09' And tblAssets.Version = '1809' And
(tblOperatingsystem.Caption Like '%LTSB%' Or
tblOperatingsystem.Caption Like '%LTSC%') Then Cast(DateDiff(DAY,
GetDate(), '2029-01-09') As nvarchar) + ' days remaining'
When GetDate() >= '2021-05-11' And tblAssets.Version = '1803' And
(tblOperatingsystem.Caption Like '%Ent__prise%' Or
tblOperatingsystem.Caption Like '%Education%' Or
tblOperatingsystem.Caption Like '%IoT%') Then 'EOL'
When GetDate() >= '2021-05-11' And tblAssets.Version = '1809' And
(tblOperatingsystem.Caption Like '%Ent__prise%' Or
tblOperatingsystem.Caption Like '%Education%' Or
tblOperatingsystem.Caption Like '%IoT%') Then 'EOL'
When GetDate() >= '2022-05-10' And tblAssets.Version = '1909' And
(tblOperatingsystem.Caption Like '%Ent__prise%' Or
tblOperatingsystem.Caption Like '%Education%' Or
tblOperatingsystem.Caption Like '%IoT%') Then 'EOL'
When GetDate() >= '2021-12-14' And tblAssets.Version = '2004' And
(tblOperatingsystem.Caption Like '%Ent__prise%' Or
tblOperatingsystem.Caption Like '%Education%' Or
tblOperatingsystem.Caption Like '%IoT%') Then 'EOL'
When GetDate() >= '2023-05-09' And tblAssets.Version = '2009' And
(tblOperatingsystem.Caption Like '%Ent__prise%' Or
tblOperatingsystem.Caption Like '%Education%' Or
tblOperatingsystem.Caption Like '%IoT%') Then 'EOL'
When GetDate() >= '2023-05-09' And tblAssets.Version = '20H2' And
(tblOperatingsystem.Caption Like '%Ent__prise%' Or
tblOperatingsystem.Caption Like '%Education%' Or
tblOperatingsystem.Caption Like '%IoT%') Then 'EOL'
When GetDate() >= '2022-12-13' And tblAssets.Version = '21H1' Then 'EOL'
When GetDate() >= '2023-06-13' And tblAssets.Version = '21H2' Then 'EOL'
When GetDate() >= '2021-05-11' And tblAssets.Version = '1909' And
(tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then 'EOL'
When GetDate() >= '2021-12-14' And tblAssets.Version = '2004' And
(tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then 'EOL'
When GetDate() >= '2022-05-10' And tblAssets.Version = '2009' And
(tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then 'EOL'
When GetDate() >= '2022-05-10' And tblAssets.Version = '20H2' And
(tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then 'EOL'
When GetDate() >= '2025-10-14' And tblAssets.Version = '1507' And
(tblOperatingsystem.Caption Like '%LTSB%' Or
tblOperatingsystem.Caption Like '%LTSC%') Then 'EOL'
When GetDate() >= '2026-10-13' And tblAssets.Version = '1607' And
(tblOperatingsystem.Caption Like '%LTSB%' Or
tblOperatingsystem.Caption Like '%LTSC%') Then 'EOL'
When GetDate() >= '2029-01-09' And tblAssets.Version = '1809' And
(tblOperatingsystem.Caption Like '%LTSB%' Or
tblOperatingsystem.Caption Like '%LTSC%') Then 'EOL'
When GetDate() < '2021-05-11' And tblAssets.Version = '1803' And
(tblOperatingsystem.Caption Like '%Ent__prise%' Or
tblOperatingsystem.Caption Like '%Education%' Or
tblOperatingsystem.Caption Like '%IoT%') Then Cast(DateDiff(DAY,
GetDate(), '2021-05-11') As nvarchar) + ' days remaining'
When GetDate() < '2021-05-11' And tblAssets.Version = '1809' And
(tblOperatingsystem.Caption Like '%Ent__prise%' Or
tblOperatingsystem.Caption Like '%Education%' Or
tblOperatingsystem.Caption Like '%IoT%') Then Cast(DateDiff(DAY,
GetDate(), '2021-05-11') As nvarchar) + ' days remaining'
When GetDate() < '2022-05-10' And tblAssets.Version = '1909' And
(tblOperatingsystem.Caption Like '%Ent__prise%' Or
tblOperatingsystem.Caption Like '%Education%' Or
tblOperatingsystem.Caption Like '%IoT%') Then Cast(DateDiff(DAY,
GetDate(), '2022-05-10') As nvarchar) + ' days remaining'
When GetDate() < '2021-12-14' And tblAssets.Version = '2004' And
(tblOperatingsystem.Caption Like '%Ent__prise%' Or
tblOperatingsystem.Caption Like '%Education%' Or
tblOperatingsystem.Caption Like '%IoT%') Then Cast(DateDiff(DAY,
GetDate(), '2021-12-14') As nvarchar) + ' days remaining'
When GetDate() < '2023-05-09' And tblAssets.Version = '2009' And
(tblOperatingsystem.Caption Like '%Ent__prise%' Or
tblOperatingsystem.Caption Like '%Education%' Or
tblOperatingsystem.Caption Like '%IoT%') Then Cast(DateDiff(DAY,
GetDate(), '2023-05-09') As nvarchar) + ' days remaining'
When GetDate() < '2022-12-13' And tblAssets.Version = '21H1' Then
Cast(DateDiff(DAY, GetDate(), '2022-12-13') As nvarchar) +
' days remaining'
When GetDate() < '2023-06-13' And tblAssets.Version = '21H2' Then
Cast(DateDiff(DAY, GetDate(), '2023-06-13') As nvarchar) +
' days remaining'
When GetDate() < '2023-05-09' And tblAssets.Version = '20H2' And
(tblOperatingsystem.Caption Like '%Ent__prise%' Or
tblOperatingsystem.Caption Like '%Education%' Or
tblOperatingsystem.Caption Like '%IoT%') Then Cast(DateDiff(DAY,
GetDate(), '2023-05-09') As nvarchar) + ' days remaining'
When GetDate() < '2021-05-11' And tblAssets.Version = '1909' And
(tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then Cast(DateDiff(DAY,
GetDate(), '2021-05-11') As nvarchar) + ' days remaining'
When GetDate() < '2021-12-14' And tblAssets.Version = '2004' And
(tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then Cast(DateDiff(DAY,
GetDate(), '2021-12-14') As nvarchar) + ' days remaining'
When GetDate() < '2022-05-10' And tblAssets.Version = '2009' And
(tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then Cast(DateDiff(DAY,
GetDate(), '2022-05-10') As nvarchar) + ' days remaining'
When GetDate() < '2022-05-10' And tblAssets.Version = '20H2' And
(tblOperatingsystem.Caption Like '%Home%' Or
tblOperatingsystem.Caption Like '%Pro%') Then Cast(DateDiff(DAY,
GetDate(), '2022-05-10') As nvarchar) + ' days remaining'
When GetDate() < '2027-01-12' And tblAssets.Version = '21H2' And
tblOperatingsystem.Caption Like '%LTSC%' Then Cast(DateDiff(DAY,
GetDate(), '2027-01-12') As nvarchar) + ' days remaining'
When GetDate() >= '2027-01-12' And tblAssets.Version = '21H2' And
tblOperatingsystem.Caption Like '%LTSC%' Then 'EOL'
When tblAssets.Version Is Null Then 'Rescan Asset'
Else 'Supported'
End As Status,
subquery1.[EOL Date],
tblAssets.Lastseen,
tblAssets.Lasttried,
Case
When GetDate() >= subquery1.[EOL Date] Then '#ffadad'
When GetDate() >= DateAdd(month, -6, subquery1.[EOL Date]) Then '#ffd152'
Else '#d4f4be'
End As backgroundcolor,
tblADComputers.OU
From tblAssets
Left Join (Select tblOperatingsystem.AssetID,
Case
When (tblassets.Version = '1507' Or
tblOperatingsystem.Caption Like '%2015%') And
(tblOperatingsystem.Caption Like '%LTSB%' Or
tblOperatingsystem.Caption Like '%LTSC%') Then '2025-10-14'
When tblassets.Version = '1607' And (tblOperatingsystem.Caption Like
'%LTSB%' Or
tblOperatingsystem.Caption Like '%LTSC%') Then '2026-10-13'
When tblassets.Version = '1809' And (tblOperatingsystem.Caption Like
'%LTSB%' Or
tblOperatingsystem.Caption Like '%LTSC%') Then '2029-01-09'
When tblassets.Version = '1507' And (tblOperatingsystem.Caption Not Like
'%LTSB%' Or
tblOperatingsystem.Caption Not Like '%LTSC%') Then '2017-05-09'
When tblassets.Version = '1511' Then '2017-10-10'
When tblassets.Version = '1607' And (tblOperatingsystem.Caption Like
'%Home%' Or tblOperatingsystem.Caption Like '%Pro%') Then '2018-04-10'
When tblassets.Version = '1607' And (tblOperatingsystem.Caption Like
'%Ent__prise%' Or
tblOperatingsystem.Caption Like '%Education%') Then '2019-04-09'
When tblassets.Version = '1703' And (tblOperatingsystem.Caption Like
'%Home%' Or tblOperatingsystem.Caption Like '%Pro%') Then '2018-10-09'
When tblassets.Version = '1703' And (tblOperatingsystem.Caption Like
'%Ent__prise%' Or
tblOperatingsystem.Caption Like '%Education%') Then '2019-10-08'
When tblassets.Version = '1709' And (tblOperatingsystem.Caption Like
'%Home%' Or tblOperatingsystem.Caption Like '%Pro%') Then '2019-04-09'
When tblassets.Version = '1709' And (tblOperatingsystem.Caption Like
'%Ent__prise%' Or
tblOperatingsystem.Caption Like '%Education%') Then '2020-04-14'
When tblassets.Version = '1803' And (tblOperatingsystem.Caption Like
'%Home%' Or tblOperatingsystem.Caption Like '%Pro%') Then '2019-11-12'
When tblassets.Version = '1803' And (tblOperatingsystem.Caption Like
'%Ent__prise%' Or
tblOperatingsystem.Caption Like '%Education%') Then '2020-11-10'
When tblassets.Version = '1809' And (tblOperatingsystem.Caption Like
'%Home%' Or tblOperatingsystem.Caption Like '%Pro%') Then '2020-05-12'
When tblassets.Version = '1809' And (tblOperatingsystem.Caption Like
'%Ent__prise%' Or
tblOperatingsystem.Caption Like '%Education%') Then '2021-05-11'
When tblassets.Version = '1903' And (tblOperatingsystem.Caption Like
'%Home%' Or tblOperatingsystem.Caption Like '%Pro%') Then '2020-12-08'
When tblassets.Version = '1903' And (tblOperatingsystem.Caption Like
'%Ent__prise%' Or
tblOperatingsystem.Caption Like '%Education%') Then '2020-12-08'
When tblassets.Version = '1909' And (tblOperatingsystem.Caption Like
'%Home%' Or tblOperatingsystem.Caption Like '%Pro%') Then '2021-05-11'
When tblassets.Version = '1909' And (tblOperatingsystem.Caption Like
'%Ent__prise%' Or
tblOperatingsystem.Caption Like '%Education%') Then '2022-05-10'
When tblassets.Version = '2004' And (tblOperatingsystem.Caption Like
'%Home%' Or tblOperatingsystem.Caption Like '%Pro%') Then '2021-12-14'
When tblassets.Version = '2004' And (tblOperatingsystem.Caption Like
'%Ent__prise%' Or
tblOperatingsystem.Caption Like '%Education%') Then '2021-12-14'
When tblassets.Version = '2009' And (tblOperatingsystem.Caption Like
'%Home%' Or tblOperatingsystem.Caption Like '%Pro%') Then '2022-05-10'
When tblassets.Version = '2009' And (tblOperatingsystem.Caption Like
'%Ent__prise%' Or
tblOperatingsystem.Caption Like '%Education%') Then '2023-05-09'
When tblassets.Version = '20H2' And (tblOperatingsystem.Caption Like
'%Home%' Or tblOperatingsystem.Caption Like '%Pro%') Then '2022-05-10'
When tblassets.Version = '20H2' And (tblOperatingsystem.Caption Like
'%Ent__prise%' Or
tblOperatingsystem.Caption Like '%Education%') Then '2023-05-09'
When tblassets.Version = '21H1' Then '2022-12-13'
When tblassets.Version = '21H2' And tblOperatingsystem.Caption
Like '%LTSC%' Then '2027-01-12'
When tblassets.Version = '21H2' Then '2023-06-13'
End As [EOL Date]
From tblOperatingsystem
Inner Join tblassets On tblassets.AssetID = tblOperatingsystem.AssetID) As
subquery1 On subquery1.AssetID = tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tblOperatingsystem On tblOperatingsystem.AssetID =
tblAssets.AssetID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Where tblOperatingsystem.Caption Like '%Windows 10%' And tblState.Statename =
'Active'
Order By tblAssets.Domain,
tblAssets.AssetName

[Graham_Ritchie]

Perfect! Thats exactly what I wanted.

Thanks 🙂

[RCorbeil]

First guess: the INNER JOIN on tblADComputers. Try changing it to a LEFT JOIN. Your OU-less machines presumably don't appear in tblADComputers, so the inner join filters them out of the results.