Track USB Storage Devices connected to assets via a Historical report

[micos]

Hello team,

Is there any report available to track USB Storage disks activities? I mean when were being connected on assets, as well when they are disconnected?

We need a historical report, not only when the USB Storage disks were detected during the last scan. 

[Mister_Nobody · Accepted solution]

NG Query:
+ List AD Groups with prefix 'USB'

Select Top 1000000 tblAssets.assetid,
  tblAssets.domain,
  tblAssets.AssetName,
  tblAssets.Userdomain,
  tblAssets.Username,
  Stuff((Select ', ' + tblADGroups.Name As [text()]
    From tblADMembership Inner Join tblADGroups On
          tblADMembership.ParentAdObjectID = tblADGroups.ADObjectID Inner Join
      tblADusers On tblADMembership.ChildAdObjectID = tblADusers.ADObjectID
    Where tblADusers.Username = tblAssets.Username And tblADusers.Userdomain =
      tblAssets.Userdomain And tblADGroups.Name Like 'USB%' For Xml Path('')),
  1, 2, '') groups,
  tblFloppyHist.Model,
  tblFloppyHist.Size,
  tblFloppyHist.FirmwareRevision,
  tblFloppyHist.SerialNumber,
  Case tblFloppyHist.Action
    When 1 Then 'Added'
    When 2 Then 'Removed'
    When 3 Then 'Updated'
  End As Action,
  tblFloppyHist.InterfaceType,
  tblFloppyHist.Lastchanged
From tblFloppyHist
  Inner Join tblAssets On tblAssets.AssetID = tblFloppyHist.AssetID And
      tblFloppyHist.InterfaceType = 'USB'
Order By tblAssets.AssetName,
  tblFloppyHist.Lastchanged Desc

[micos]

hello,

Super, its working now. Many thanks @Mister_Nobody  Great job!

[Mister_Nobody]

I have tried to write SQL report but LS has no documentation about history tables:

Select Top 1000000 tblAssets.assetid,
  tblAssets.AssetName,
  tblFloppyHist.Model,
  tblFloppyHist.Size,
  tblFloppyHist.FirmwareRevision,
  tblFloppyHist.SerialNumber,
  Case tblFloppyHist.Action
    When 1 Then 'Added'
    When 2 Then 'Removed'
    When 3 Then 'Updated'
      End As Action,
  tblFloppyHist.InterfaceType,
  tblFloppyHist.Lastchanged,
  tblFloppyHist.Action As Action1
From tblFloppyHist
  Inner Join tblAssets On tblAssets.AssetID = tblFloppyHist.AssetID
Where tblFloppyHist.InterfaceType = 'USB'
Order By tblAssets.AssetName,
  tblFloppyHist.Lastchanged Desc

[Mister_Nobody]

No history for USB Devices.

For USB Storage you can see

https://LS_server/Report/report.aspx?det=changes&page=Floppy

[micos]

Hello,

I cant reach this page. 

[Mister_Nobody]

Did you put your LS servername?

Are you LS admin?

[micos]

Yeah, I m an admin. 

The report you shared with me has no results while executing. (For sure there are 100+ workstations with USB Storage disks hooked up)

[Mister_Nobody]

Did you enable floppy history into Scanned Item Interval?

[micos]

floppy history?  Is there any relation to USB Storage disks? you mean this? 

[Mister_Nobody]

Any doubts in my words?