Report on Computers that USB Storage devices are plugged (Flash drives)

Hi all

SCCM is having reports with too much information’s. But most of these are irrelevant for our use. So I think it’s always better to have your own way of reports by writing custom queries.

One of my customer requested that they want to monitor all computers that plugging USB storage devices and time. I have searched this on SCCM and other forums, but couldn’t find anything, but there is lot of reports giving so many details on USB devices. So I have opened the SCCM database tables and wrote a custom query. Then add this query to a report. Customize it. And create an email subscription to this report. Now every morning IT manager will get a report on all the computers and time of USB pen drives are plugged in. tell me its not cool.  

So this is the full steps

  1. Enable Asset IntelligenceAssent intelligence should be enabled and you have to enable the Win32_USBDevice inventory Class.

    If you are doing this first time then you have to wait till next hardware cycle to complete. Then only all the USB device information will populate to the database.

    13

  2. Create the SQL Query
    I have created the following script to get required information. This script gather data from two tables USB_DEVICE_DATA and USB_DEVICE_HIST. second table is the history table. And I will filter these data only for USB Storage devices (USBSTOR). I only required knowing the computer that USB flash drives have plugged in and the time. So I have only generating the SystemName00 and TimeKey column.

select USB_DEVICE_DATA.SystemName00, USB_DEVICE_DATA.TimeKey from USB_DEVICE_DATA where 
dbo.USB_DEVICE_DATA.Service00 = ‘USBSTOR’
union all 
select USB_DEVICE_HIST.SystemName00, USB_DEVICE_HIST.TimeKey from USB_DEVICE_HIST
where dbo.USB_DEVICE_HIST.Service00 = ‘USBSTOR’
order by TimeKey desc

This query will produce the list of computers of all time. But in my scenario I only need to know USB storage devices that plugged in on last 2 days. So I have modified the SQL query to following.

select USB_DEVICE_DATA.SystemName00, USB_DEVICE_DATA.TimeKey from USB_DEVICE_DATA

where

dbo.USB_DEVICE_DATA.Service00 = ‘USBSTOR’ and TimeKey > GETDATE() – 2

union all

select USB_DEVICE_HIST.SystemName00, USB_DEVICE_HIST.TimeKey from USB_DEVICE_HIST

where dbo.USB_DEVICE_HIST.Service00 = ‘USBSTOR’ and TimeKey > GETDATE() – 2

order by TimeKey desc

 

  • Create the Customize Report
  • In SCCM console select monitoring tab, right click on reports and select create report.
  • Select SQL based report, Give a name and path of the report.  Click next11

     

  • Then it will Open SQL server Report builder application
  • Right Click on Datasets and select Add Dataset
  • Select  Dataset embedded to my report
  • Select  the available data source
  • Copy and paste the query as following image12
  • Click Fields and it will automatically add fields.
  • Click OK
  • Click insert and select Table, Table Wizard
  • Select the created dataset Next, then Drag two fields to the Values.
  • Click Next and select the style and finish.
  • Add your report headers, Logos and everything that your requirements and Save the report.
  • Select the Report from Console and click Run.14

If your SCCM database is populated with data, following report is displayed.

Note – all data will report after the Hardware cycle of SCCM Client. when Same USB plugged to the same computer, Hardware cycle should run in middle.

Advertisements

5 thoughts on “Report on Computers that USB Storage devices are plugged (Flash drives)

  1. Very helpful article! I have now implemented this in our organisation. Do you know if SCCM can also tell when USB’s are unplugged and how to add this to the report?

  2. It did work.. but is showing incorrect information. Showed USB as connected@ 11 PM with nobody using or connected to the server locally or remotely. Plus no related events.. Not sure what’s happening here..

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s