Report for Discs in Drives e.g. CDs/DVDs etc.

[alewis]

This may be a bit of an odd request, but I think for me it'd be helpful to have a report to tell you which computers have discs in the drive - only because we use a number of CD/DVD tools for diagnostics, windows installation etc. and although we normally remember to remove the disc, there are instances where they're found a few weeks/months later still in the drive.

It'd further be helpful to know what the disc name is (if possible) but if not, that's not too big of a deal.

[RCorbeil]

Give this a shot:

Select Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblDiskdrives.Description,
  tblDiskdrives.FileSystem,
  tblDiskdrives.Caption,
  tblDiskdrives.Volumename
From
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Where
  tblAssetCustom.State = 1
  AND tblDiskdrives.DriveType = 5   -- Compact Disc
  AND tblDiskdrives.Size > 0        -- not blank
  

[alewis]

RC62N wrote:
Give this a shot:

Select Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblDiskdrives.Description,
  tblDiskdrives.FileSystem,
  tblDiskdrives.Caption,
  tblDiskdrives.Volumename
From
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Where
  tblAssetCustom.State = 1
  AND tblDiskdrives.DriveType = 5   -- Compact Disc
  AND tblDiskdrives.Size > 0        -- not blank
  

Many thanks, this worked great 🙂

[ajokerst]

You can try this code. However, keep in mind that it also gets empty disks (CD-RW) as well. It definitely has room for improvement. Perhaps someone here can refine it?

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.Description As Description
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Where tblDiskdrives.Description = 'CD-ROM Disc' And tblDiskdrives.Freespace =
  '0' And tblAssetCustom.State = 1