→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
RTI_IT
Champion Sweeper
Hello,

I need assistance in generating two separate reports.

The first, showing usernames mapped to the share "Millnet". The full path is was "\\server3\maillnet", and the assigned letter varied. I just need to see each username that was mapped to it.

The second would be usernames connected to the network printer "P1 Eng - Canon iR C5030". The full path was "\\The-NARF\P1 Eng - Canon iR C5030".

Thank you,
Ed
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
Thanks for the screenshot. For mapped drives you need another table (tblMappedDrives). Please find a modified report below.

Select Top 1000000 tblMappedDrives.Username,
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Lastseen,
tblMappedDrives.Driveletter,
tblMappedDrives.RemotePath
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblMappedDrives On tblAssets.AssetID = tblMappedDrives.AssetID
Where tblMappedDrives.RemotePath Like '%millnet%' And tblAssetCustom.State = 1
Order By tblMappedDrives.Username,
tblAssets.AssetName,
tblMappedDrives.Driveletter

View solution in original post

3 REPLIES 3
Daniel_B
Lansweeper Alumni
Thanks for the screenshot. For mapped drives you need another table (tblMappedDrives). Please find a modified report below.

Select Top 1000000 tblMappedDrives.Username,
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Lastseen,
tblMappedDrives.Driveletter,
tblMappedDrives.RemotePath
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblMappedDrives On tblAssets.AssetID = tblMappedDrives.AssetID
Where tblMappedDrives.RemotePath Like '%millnet%' And tblAssetCustom.State = 1
Order By tblMappedDrives.Username,
tblAssets.AssetName,
tblMappedDrives.Driveletter
RTI_IT
Champion Sweeper
Thank you for the fast reply! I tried to run the reports but it seems like they are showing the server that hosts the share, not the users who use the share. I am trying to clean up the logon scripts so I need to know which users are mapped to which share. What would need to be changed to show that?

Daniel_B
Lansweeper Alumni
Please use the reports below for the information you are after. Instructions for running reports can be found here. If you are interested in building or modifying reports, we would recommend:
- Reviewing some SQL tutorials, as the Lansweeper report builder uses standard SQL queries. This seems like a good tutorial.
- Updating to Lansweeper 5.2, if you haven't already. Lansweeper 5.2 includes a database dictionary, which is linked at the top of the report builder.

Assets and users connected to a share:

Select Top 1000000 tblSharesUni.Caption,
tblSharesUni.Name,
tblSharesUni.Path,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Username,
tblAssets.Userdomain,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Lastseen
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblShares On tblAssets.AssetID = tblShares.AssetID
Inner Join tblSharesUni On tblSharesUni.ShareUniqueID =
tblShares.ShareUniqueID
Where tblSharesUni.Path Like '%maillnet%' And tblAssetCustom.State = 1
Order By tblAssets.Username,
tblSharesUni.Name


Assets and usernames connected to a printer:

Select Top 1000000 tblPrinters.Caption,
tblPrinters.Portname,
tblPrinters.Sharename,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Username,
tblAssets.Userdomain,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Lastseen
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblPrinters On tblAssets.AssetID = tblPrinters.AssetID
Where tblPrinters.Caption Like '%P1 Eng - Canon iR C5030%' And
tblAssetCustom.State = 1
Order By tblAssets.Username