Report to show USB mass storage devices

[Steve_dodds]

I would like to have a report that would list all computers and servers that have a USB mass storage device connected. I have tried tweaking a built-in report but I didnt get the results I wanted and I know very little about databases. Does anyone have a report like this or can help get me going in the right direction?

Thanks
Steve

[ict-user]

We use a startup script to scan usb ports for storage at startup and when plugged in.
The script is amazing. A lot of options and it can also trigger Lansweeper when adding one simple line.

USBMonitor_20v4.vbs
http://www.akaplan.com/blog/2010/04/usb-monitor-for-removable-drives/

Added the following at about line 506

Function GetDriveLetter()
If bDebug Then WScript.Echo "Getting drive letter of USB drive"
Dim colDiskDrives, oDiskDrive, colDrives, colLDrives, oLDrive
Dim strWQL, colPartitions, oPartition
Dim i

Set colDiskDrives = oWMI.ExecQuery("SELECT * FROM Win32_DiskDrive where InterfaceType='USB' and size > 0",,48)

For Each oDiskDrive In colDiskDrives 'Get USB Drives. DeviceID looks like \\.\PHYSICALDRIVE1
strModel = oDiskDrive.Caption
strWQL = "ASSOCIATORS OF {Win32_DiskDrive.DeviceID='" & oDiskDrive.deviceID & _
"'} WHERE AssocClass = Win32_DiskDriveToDiskPartition"
Set colPartitions = oWMI.ExecQuery(strWQL,,48)
For Each oPartition In colPartitions
i = i + 1
'WScript.Echo "Partition: " & oPartition.DeviceID 'Device ID looks like Disk #1, Partition #0
strWQL = "ASSOCIATORS OF {Win32_DiskPartition.DeviceID='" & oPartition.DeviceID & _
"'} WHERE AssocClass = Win32_LogicalDiskToPartition"

Set colLDrives = oWMI.ExecQuery(strWQL,,48)
For Each oLDrive In colLDrives
GetDriveLetter = oLDrive.DeviceID 'Device ID looks like E:
Next
Next
Next
WshShell.run "\\lansweeper-servername\lansweeper$\lspush.exe lansweeper-servername",0
End Function

[Steve_dodds]

Fantastic! Thank you very much for the help!

Steve

[Daniel_B · Accepted solution]

Mass storage devices have an entry in table tblDiskdrives and tblDiskdrives.Drivetype is 2. You should be able to list them using the following report. Please note that Lansweeper must have scanned the computers while the mass storage device was connected.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  Case When tblComputersystem.Domainrole > 1 Then 'Server' Else 'Workstation'
  End As [Server/Workstation],
  tsysOS.OSname,
  tsysOS.Image As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblDiskdrives.Caption As [Drive letter],
  tblDiskdrives.Description,
  Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 As numeric) As Size,
  Cast(Cast(tblDiskdrives.Freespace As bigint) / 1024 / 1024 As numeric) As
  Freespace
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblDiskdrives.Description Not Like '%floppy%' And
  tblDiskdrives.DriveType = 2 And tblAssetCustom.State = 1
Order By tblAssets.AssetName,
  [Drive letter]