
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎07-10-2014 07:51 PM
I'm trying to edit the canned report of encryted volumes but I want to limit it to Laptops and only show those with OFF for C: volume. I have this and it's apparently all wrong. Any help would be great.
Thanks
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,
TsysChassisTypes.Chassistype,
TsysChassisTypes.ChassisName
From tblEncryptableVolume
Inner Join tblAssets On tblEncryptableVolume.AssetId = tblAssets.AssetID
Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
Inner Join TsysChassisTypes On TsysChassisTypes.ChassisName = tblAssets.OScode
Where (TsysChassisTypes.Chassistype = 8 Or TsysChassisTypes.Chassistype = 9 Or
TsysChassisTypes.Chassistype = 10) And tblAssets.Lastseen <> '' And
tblAssets.Assettype = -1
Thanks
Solved! Go to Solution.
Labels:
- Labels:
-
Report Center
1 ACCEPTED SOLUTION
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎07-11-2014 12:06 AM
You're mislinking from the asset's OS to try identifying the chassis type. You need to link tblAssets.AssetID -> tblSystemEnclosure.AssetID to get the chassis type code and, if you want the description, tblSystemEnclosure.ChassisTypes -> tSysChassisTypes.ChassisType.
Alternatively, a shortcut that's worked for my inventory is to link tblAssets.AssetID -> tblPortableBattery.AssetID.
My thinking: if it's a portable device (notebook/laptop), it has a portable battery. My inventory bears this out.
If all you want are notebooks, you can use an INNER JOIN. If you want all devices, use a LEFT JOIN and identify the notebooks with something like
Based on the default LANSweeper query:
If you don't want to use my notebook shortcut, remove the references to tblPortableBattery and add your chassis type filters.
Alternatively, a shortcut that's worked for my inventory is to link tblAssets.AssetID -> tblPortableBattery.AssetID.
My thinking: if it's a portable device (notebook/laptop), it has a portable battery. My inventory bears this out.
If all you want are notebooks, you can use an INNER JOIN. If you want all devices, use a LEFT JOIN and identify the notebooks with something like
CASE WHEN tblPortableBattery.AssetID IS NULL
THEN 'desktop'
ELSE 'notebook'
END
Based on the default LANSweeper query:
SELECT Top 1000000
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 AS icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblEncryptableVolume.DriveLetter,
CASE WHEN tblEncryptableVolume.ProtectionStatus = 0 THEN 'OFF'
WHEN tblEncryptableVolume.ProtectionStatus = 1 THEN 'ON'
ELSE 'UNKNOWN'
END AS ProtectionStatus,
tblEncryptableVolume.LastChanged,
TsysChassisTypes.ChassisName,
CASE WHEN tblPortableBattery.AssetID IS NULL
THEN 'desktop'
ELSE 'notebook'
END AS Classification
FROM
tblAssets
INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
INNER JOIN tblEncryptableVolume ON tblAssets.AssetID = tblEncryptableVolume.AssetId
INNER JOIN tblSystemEnclosure ON tblAssets.AssetID = tblSystemEnclosure.AssetID
INNER JOIN TsysChassisTypes ON tblSystemEnclosure.ChassisTypes = TsysChassisTypes.Chassistype
INNER JOIN tblPortableBattery ON tblAssets.AssetID = tblPortableBattery.AssetID
WHERE
tblAssetCustom.State = 1
AND tblAssets.Assettype = -1
AND tblAssets.Lastseen <> ''
ORDER BY
tblAssets.AssetName,
tblEncryptableVolume.DriveLetter
If you don't want to use my notebook shortcut, remove the references to tblPortableBattery and add your chassis type filters.
1 REPLY 1
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎07-11-2014 12:06 AM
You're mislinking from the asset's OS to try identifying the chassis type. You need to link tblAssets.AssetID -> tblSystemEnclosure.AssetID to get the chassis type code and, if you want the description, tblSystemEnclosure.ChassisTypes -> tSysChassisTypes.ChassisType.
Alternatively, a shortcut that's worked for my inventory is to link tblAssets.AssetID -> tblPortableBattery.AssetID.
My thinking: if it's a portable device (notebook/laptop), it has a portable battery. My inventory bears this out.
If all you want are notebooks, you can use an INNER JOIN. If you want all devices, use a LEFT JOIN and identify the notebooks with something like
Based on the default LANSweeper query:
If you don't want to use my notebook shortcut, remove the references to tblPortableBattery and add your chassis type filters.
Alternatively, a shortcut that's worked for my inventory is to link tblAssets.AssetID -> tblPortableBattery.AssetID.
My thinking: if it's a portable device (notebook/laptop), it has a portable battery. My inventory bears this out.
If all you want are notebooks, you can use an INNER JOIN. If you want all devices, use a LEFT JOIN and identify the notebooks with something like
CASE WHEN tblPortableBattery.AssetID IS NULL
THEN 'desktop'
ELSE 'notebook'
END
Based on the default LANSweeper query:
SELECT Top 1000000
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 AS icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblEncryptableVolume.DriveLetter,
CASE WHEN tblEncryptableVolume.ProtectionStatus = 0 THEN 'OFF'
WHEN tblEncryptableVolume.ProtectionStatus = 1 THEN 'ON'
ELSE 'UNKNOWN'
END AS ProtectionStatus,
tblEncryptableVolume.LastChanged,
TsysChassisTypes.ChassisName,
CASE WHEN tblPortableBattery.AssetID IS NULL
THEN 'desktop'
ELSE 'notebook'
END AS Classification
FROM
tblAssets
INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
INNER JOIN tblEncryptableVolume ON tblAssets.AssetID = tblEncryptableVolume.AssetId
INNER JOIN tblSystemEnclosure ON tblAssets.AssetID = tblSystemEnclosure.AssetID
INNER JOIN TsysChassisTypes ON tblSystemEnclosure.ChassisTypes = TsysChassisTypes.Chassistype
INNER JOIN tblPortableBattery ON tblAssets.AssetID = tblPortableBattery.AssetID
WHERE
tblAssetCustom.State = 1
AND tblAssets.Assettype = -1
AND tblAssets.Lastseen <> ''
ORDER BY
tblAssets.AssetName,
tblEncryptableVolume.DriveLetter
If you don't want to use my notebook shortcut, remove the references to tblPortableBattery and add your chassis type filters.
