cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
bcallaha_01
Engaged Sweeper

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

1 ACCEPTED SOLUTION
rader
Champion Sweeper III

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.

 

View solution in original post

1 REPLY 1
rader
Champion Sweeper III

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.