Comparing columns within the same report

[Tyler_M]

Hi Everyone,

I'm looking for a way to exclude drive letters from specific assets to they don't show up in our daily reports. In our case, we monitor certain drives in another application and don't need the noise in our Lansweeper reports. I setup a Custom field for a comma separated list of drive letters to ignore.

I'd only like the asset to appear in the report if the drive caption doesn't appear in the contents of Custom5. Is this possible or should I go about this a different way?

This is the report I'm working with.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.Lastseen,
tblDiskdrives.Caption As Drive,
Cast(tblDiskdrives.Freespace / 1024 / 1024 / 1024 As numeric) As FreeGB,
Cast(tblDiskdrives.Size / 1024 / 1024 / 1024 As numeric) As TotalSizeGB,
Ceiling(tblDiskdrives.Freespace / (Case tblDiskdrives.Size
When 0 Then 1
Else tblDiskdrives.Size
End) * 100) As [%SpaceLeft],
tblDiskdrives.Lastchanged As LastChanged,
tblAssetCustom.Custom5
From tblAssets
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.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 tblDomainroles On tblDomainroles.Domainrole =
tblComputersystem.Domainrole
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where Cast(tblDiskdrives.Size / 1024 / 1024 / 1024 As numeric) <> 0 And
Ceiling(tblDiskdrives.Freespace / (Case tblDiskdrives.Size
When 0 Then 1
Else tblDiskdrives.Size
End) * 100) < 11 And tblState.Statename = 'Active' And
tblDomainroles.Domainrolename In ('Stand-alone server', 'Member server',
'Primary domain controller', 'Backup domain controller') And
Case tblDiskdrives.DriveType
When 3 Then 'Local Disk'
End = 'Local Disk'
Order By tblAssets.AssetName,
Drive

[Tyler_M]

That worked perfectly, thank you!

The final report for anyone else interested:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysOS.OSname As OS,
tblAssets.Lastseen,
tblDiskdrives.Caption As Drive,
Cast(tblDiskdrives.Freespace / 1024 / 1024 / 1024 As numeric) As FreeGB,
Cast(tblDiskdrives.Size / 1024 / 1024 / 1024 As numeric) As TotalSizeGB,
Ceiling(tblDiskdrives.Freespace / (Case tblDiskdrives.Size
When 0 Then 1
Else tblDiskdrives.Size
End) * 100) As [%SpaceLeft],
tblDiskdrives.Lastchanged As LastChanged,
tblAssetCustom.Custom5 As [Excluded Storage]
From tblAssets
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.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 tblDomainroles On tblDomainroles.Domainrole =
tblComputersystem.Domainrole
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where Cast(tblDiskdrives.Size / 1024 / 1024 / 1024 As numeric) <> 0 And
Ceiling(tblDiskdrives.Freespace / (Case tblDiskdrives.Size
When 0 Then 1
Else tblDiskdrives.Size
End) * 100) < 16 And tblState.Statename = 'Active' And
tblDomainroles.Domainrolename In ('Stand-alone server', 'Member server',
'Primary domain controller', 'Backup domain controller') And
Case tblDiskdrives.DriveType
When 3 Then 'Local Disk'
End = 'Local Disk' And CharIndex(Left(tblDiskdrives.Caption, 1),
IsNull(tblAssetCustom.Custom5, '')) = 0
Order By tblAssets.AssetName,
Drive

[RCorbeil]

Alternatively,

AND CharIndex(tblDiskdrives.Caption, IsNull(tblAssetCustom.Custom5, '')) = 0

would probably work as well.

[RCorbeil]

Ah, my bad; I neglected to handle NULL. Sorry about that. I normally test code against my own inventory before posting but couldn't in this case since I don't have anything set up the way you're using Custom5.

Try this:

AND (tblAssetCustom.Custom5 IS NULL   -- never populated, nothing to filter against, so include the record
     OR CharIndex(tblDiskdrives.Caption, tblAssetCustom.Custom5) = 0  -- populated, even if blank; include the record if current drive is not in the list
    )

[RCorbeil]

Assuming your Custom5 list formats the drive captions to match tblDiskdrives.Caption (e.g. 'D:,E:,F:'), you should be able to add this to your WHERE clause:

  AND CharIndex(tblDiskdrives.Caption, tblAssetCustom.Custom5) = 0

If the formatting isn't the same, e.g. if you don't include the colons ('D,E,F' or even 'DEF'), you could achieve the same effect with

  AND CharIndex(Left(tblDiskdrives.Caption, 1), tblAssetCustom.Custom5) = 0

[Tyler_M]

RC62N wrote:
Assuming your Custom5 list formats the drive captions to match tblDiskdrives.Caption (e.g. 'D:,E:,F:'), you should be able to add this to your WHERE clause:

  AND CharIndex(tblDiskdrives.Caption, tblAssetCustom.Custom5) = 0

If the formatting isn't the same, e.g. if you don't include the colons ('D,E,F' or even 'DEF'), you could achieve the same effect with

  AND CharIndex(Left(tblDiskdrives.Caption, 1), tblAssetCustom.Custom5) = 0

That's close, thank you. However, I'm running into a problem when Custom5 is changed on a asset. It seems like the database treats it differently than NULL. For example - using = 0 I only get assets that had a value in Custom5 at one time, but no longer do. Assets that have never had a value in the field and are still NULL in the database do not show up in the report. If I change it to NOT NULL, the existing assets that have never had the custom5 field filled out appear, but those that had the field previously do not. This would be a problem as I'd like the custom field to be a convenient toggle for reporting.