Need help with two separate reports.

[RTI_IT]

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

[Daniel_B · Accepted solution]

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]

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]

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