Reporting on Mapped Drives

[bcallaha_01]

I'm looking for a way to report on all my devices mapped drives in my environment, but I want to exclude specific drive mappings that are standard.  Essentially finding users who are "going rouge" and creating their own shares that we do not know about.  I am using this report below that successfully reports on all mapped drives, but I want to exclude any shares that begin with the server \\SERVERNAME\  What would I need to add to this report to make this happen?

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblMappedDrives.Driveletter,
tblMappedDrives.RemotePath,
tblMappedDrives.Username As DriveOfUser,
tblMappedDrives.Lastchanged As LastChanged
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblMappedDrives On tblAssets.AssetID = tblMappedDrives.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblState.Statename = 'Active'
Order By tblAssets.Domain,
tblAssets.AssetName,
tblMappedDrives.Driveletter

[rader · Accepted solution]

Add "Not Like '%SERVERNAME%' to the tblMappedDrives.RemotePath statement.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  tblAssets.SP,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblMappedDrives.Driveletter,
  tblMappedDrives.RemotePath,
  tblMappedDrives.Username As DriveOfUser,
  tblMappedDrives.Lastchanged As LastChanged
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblMappedDrives On tblAssets.AssetID = tblMappedDrives.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
      tblAssets.LocationID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblMappedDrives.RemotePath Not Like '%SERVERNAME%' And
  tblState.Statename = 'Active'
Order By tblAssets.Domain,
  tblAssets.AssetName,
  tblMappedDrives.Driveletter

 That should filter out all the standard drive maps in your environment.