Query on Report

[Srikanth08]

Hi Guys,

I need one query for reporting the systems with CD drive enabled. Iam having OU in active directory where the CD drive is enabled. i need report for that ou what is the system name username and dept..

[Hemoco]

A sample report can be seen below. Replace what we’ve marked in bold with your own OU, making sure there’s no spaces in the OU path.

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblADComputers.OU,
tblAssets.IPAddress,
tblAssets.Username,
tblAssets.Userdomain,
tblADusers.Department As UserDepartment,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblCDROMDrive.Caption,
tblCDROMDrive.Capabilities,
tblCDROMDrive.Drive,
tblCDROMDrive.Manufacturer,
tblCDROMDrive.SCSIBus,
tblCDROMDrive.SCSILogicalUnit,
tblCDROMDrive.SCSIPort,
tblCDROMDrive.SCSITargetId,
tblCDROMDrive.Lastchanged
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain
Inner Join tblCDROMDrive On tblAssets.AssetID = tblCDROMDrive.AssetID
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Where tblADComputers.OU = 'CN=Computers,DC=lansweeper,DC=local' And
tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName,
tblCDROMDrive.Caption