→ 🚀What's New? Join Us for the Fall Product Launch! Register Now !

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
alewis
Engaged Sweeper
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.
3 REPLIES 3
RCorbeil
Honored Sweeper II
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
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
Engaged Sweeper II
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