Add Filed

[Martin_Engie]

Hello all,
I’m on Ver 7.1
I have a wonderful report wich give me all I need for reporting but the filed to know if it’s a laptop or not,

Select Top 1000000 tsysAssetTypes.AssetTypeIcon16 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblEncryptableVolume.DriveLetter,
Case
When tblEncryptableVolume.ProtectionStatus = 0 Then 'OFF'
When tblEncryptableVolume.ProtectionStatus = 1 Then 'ON'
Else 'UNKNOWN'
End As ProtectionStatus,
tblEncryptableVolume.LastChanged,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tsysOS.OSname As OS,
tblAssets.SP As SP,
tblAssets.Firstseen,
tblAssets.Lastseen
From tblEncryptableVolume
Inner Join tblAssets On tblEncryptableVolume.AssetId = tblAssets.AssetID
Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Where (tblEncryptableVolume.ProtectionStatus = 0 Or
tblEncryptableVolume.ProtectionStatus = 1)
Order By tblEncryptableVolume.ProtectionStatus

I have here a second which just give me the missing information,

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.AssetName,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
TsysChassisTypes.ChassisName,
tblAssets.IPAddress As IP,
tblAssets.Lastseen
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblSystemEnclosure On
tblAssets.AssetID = tblSystemEnclosure.AssetID
Inner Join TsysChassisTypes On TsysChassisTypes.Chassistype =
tblSystemEnclosure.ChassisTypes
Where tblAssets.AssetID Not In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where (tblSoftwareUni.softwareName Like '%Sophos%' Or
tblSoftwareUni.softwareName Like '%Guardian Edge%' Or
tblSoftwareUni.softwareName Like '%Symantec Endpoint Encryption%'))
And tblAssets.Lastseen <> '' And (TsysChassisTypes.Chassistype = 8 Or
TsysChassisTypes.Chassistype = 9 Or TsysChassisTypes.Chassistype = 10 Or
TsysChassisTypes.Chassistype = 14) And tblAssets.Assettype = -1
Order By tblAssets.AssetUnique

Is anyone know how I can add the field

“

And (TsysChassisTypes.Chassistype = 8 Or
TsysChassisTypes.Chassistype = 9 Or TsysChassisTypes.Chassistype = 10 Or
TsysChassisTypes.Chassistype = 14) “

To make filter on laptop as/when needed,

Thank you !

[JacobH]

Nooooo problem. Glad it works for you.

[Martin_Engie]

Perfect !
Thanks a lot !

[JacobH]

You can left join tblPortableBattery versus trying to keep track of different chassis types - this is what I generally do.

Here is your modified report:

Select Top 1000000 tsysAssetTypes.AssetTypeIcon16 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
Case
    When tblPortableBattery.AssetID Is Null Then 'Desktop'
    Else 'Laptop'
  End As [Desktop/Laptop],
tblEncryptableVolume.DriveLetter,
Case
When tblEncryptableVolume.ProtectionStatus = 0 Then 'OFF'
When tblEncryptableVolume.ProtectionStatus = 1 Then 'ON'
Else 'UNKNOWN'
End As ProtectionStatus,
tblEncryptableVolume.LastChanged,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tsysOS.OSname As OS,
tblAssets.SP As SP,
tblAssets.Firstseen,
tblAssets.Lastseen
From tblEncryptableVolume
Inner Join tblAssets On tblEncryptableVolume.AssetId = tblAssets.AssetID
Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join tblPortableBattery On tblAssets.AssetID = tblPortableBattery.AssetID
Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Where (tblEncryptableVolume.ProtectionStatus = 0 Or
tblEncryptableVolume.ProtectionStatus = 1)
Order By tblEncryptableVolume.ProtectionStatus

Also, your report includes virtual machines. To filter out virtual machines you can add

and tblAssetCustom.Model Not Like '%Virtual%'

in the WHERE clause.