cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Steve_dodds
Engaged Sweeper
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
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
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]

View solution in original post

3 REPLIES 3
ict-user
Engaged Sweeper III
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
Engaged Sweeper
Fantastic! Thank you very much for the help!

Steve
Daniel_B
Lansweeper Alumni
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]