Community FAQ
cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
PeterAJ
Engaged Sweeper

Hi,

I am trying (perhaps in a clumsy way!) to make a report of assets with one or more missing custom fields filled out. It sort of works, but my problem is, that assets only in Default Group doesn't show up! If I then assign the assets to another group, it shows up, even if I remove it from the group again!

Select Top 1000000 tblassets.AssetID,
tblassets.AssetName,
tsysassettypes.AssetTypename,
tblAssetGroups.AssetGroup,
tblassetcustom.Custom8 As System,
tblassetcustom.Custom1 As Service,
tblassetcustom.PurchaseDate,
tblassetcustom.Custom3 As HW,
tblassetcustom.Custom2 As VLAN,
tblassetcustom.Custom4 As Building,
tblassetcustom.Custom7 As Priority,
tsysassettypes.AssetTypeIcon10 As icon,
tblassets.Firstseen
From tblassets
Inner Join tblAssetGroupLink On tblassets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
Where (tblassets.AssetName <> 'None' And tblassetcustom.Custom8 <> 'NoSystem'
And tblassetcustom.Custom2 = '' And tblassetcustom.State = 1) Or
(tblassets.AssetName <> 'None' And tblassetcustom.Custom8 <> 'NoSystem' And
tblassetcustom.Custom1 = '' And tblassetcustom.State = 1) Or
(tblassets.AssetName <> 'None' And tblassetcustom.Custom8 = '' And
tblassetcustom.Custom8 <> 'NoSystem' And tblassetcustom.State = 1) Or
(tblassets.AssetName <> 'None' And tblassetcustom.Custom8 <> 'NoSystem' And
tblassetcustom.Custom3 = 'yes' And tblassetcustom.Custom4 = '' And
tblassetcustom.State = 1) Or
(tblassets.AssetName <> 'None' And tblassetcustom.Custom8 <> 'NoSystem' And
tblassetcustom.PurchaseDate = '' And tblassetcustom.Custom3 = 'yes' And
tblassetcustom.State = 1)
Order By tblassets.Firstseen Desc

 

Any help appreciated 🙂

Thank you

Kind regards Peter

 

 

 

 

1 REPLY 1
rinks
Champion Sweeper II

Try using a Left Join instead of Inner Join:

SELECT TOP 1000000
tblassets.AssetID,
tblassets.AssetName,
tsysassettypes.AssetTypename,
tblAssetGroups.AssetGroup,
tblassetcustom.Custom8 AS System,
tblassetcustom.Custom1 AS Service,
tblassetcustom.PurchaseDate,
tblassetcustom.Custom3 AS HW,
tblassetcustom.Custom2 AS VLAN,
tblassetcustom.Custom4 AS Building,
tblassetcustom.Custom7 AS Priority,
tsysassettypes.AssetTypeIcon10 AS icon,
tblassets.Firstseen
FROM tblassets
LEFT JOIN tblAssetGroupLink ON tblassets.AssetID = tblAssetGroupLink.AssetID
LEFT JOIN tblAssetGroups ON tblAssetGroups.AssetGroupID = tblAssetGroupLink.AssetGroupID
INNER JOIN tblassetcustom ON tblassets.AssetID = tblassetcustom.AssetID
INNER JOIN tsysassettypes ON tsysassettypes.AssetType = tblassets.Assettype
WHERE tblassets.AssetName <> 'None'
AND tblassetcustom.State = 1
AND tblassetcustom.Custom8 <> 'NoSystem'
AND (
tblassetcustom.Custom2 = '' OR
tblassetcustom.Custom1 = '' OR
(tblassetcustom.Custom8 = '' AND tblassetcustom.Custom8 <> 'NoSystem') OR
(tblassetcustom.Custom3 = 'yes' AND tblassetcustom.Custom4 = '') OR
(tblassetcustom.Custom3 = 'yes' AND tblassetcustom.PurchaseDate = '')
)
ORDER BY tblassets.Firstseen DESC

By switching to Left Join, you ensure that all assets are included—even if they don't have a group or are only in the "Default Group."

------------------------------------------------
Union Home Mortgage's "Lansweeper Guy"
------------------------------------------------

Reports & Analytics

Ask about reports you're interested in and share reports you've created. Subscribe to receive daily updates of reports shared in the Community.

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now