cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Daniel_B
Lansweeper Alumni
List of all configured Share folders, including the share permissions set on each folder


Meets all the following criteria:
- Active Asset
- Windows Asset
- Network Share folders configured

Sorted on:
- Asset name

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tblAssets.Description,
tsysOS.OSname,
tblSharesUni.Name,
tblSharesUni.Path,
tsysOS.Image As icon,
tblSharePermissions.trustee As [for user/group],
Case When tblSharePermissions.readAccess = 1 Then 'y' Else 'n' End As [Read],
Case When tblSharePermissions.writeAccess = 1 Then 'y' Else 'n' End As Write,
Case When tblSharePermissions.fullAccess = 1 Then 'y' Else 'n' End As [Full],
Case When tblSharePermissions.denyAccess = 1 Then 'y' Else 'n' End As Denied,
tblShares.Lastchanged
From tblShares
Inner Join tblAssets On tblShares.AssetID = tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblSharesUni On tblShares.ShareUniqueID =
tblSharesUni.ShareUniqueID
Inner Join tblSharePermissions
On tblShares.ShareID = tblSharePermissions.ShareID
Where tblAssetCustom.State = 1 And tblSharesUni.Type = 0
Order By tblAssets.AssetName
18 REPLIES 18
Rami_Ferwana
Engaged Sweeper
Great Support Thank you
Rami_Ferwana
Engaged Sweeper
Can you add the display name ,login name, department, title and manager name for [for user/group]?

I need a report with shared folder permissions contain users from different departments
Rami Ferwana wrote:
Can you add the display name ,login name, department, title and manager name for [for user/group]?

Please try the report below.
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tblAssets.Description,
tsysOS.OSname,
tblSharesUni.Name,
tblSharesUni.Path,
tsysOS.Image As icon,
tblSharePermissions.trustee As [for user/group],
tblADusers.Username,
tblADusers.Userdomain,
tblADusers.Displayname,
tblADusers.Department,
tblADusers.Title,
tblADObjects.sAMAccountName As Manager,
Case When tblSharePermissions.readAccess = 1 Then 'y' Else 'n' End As [Read],
Case When tblSharePermissions.writeAccess = 1 Then 'y' Else 'n' End As Write,
Case When tblSharePermissions.fullAccess = 1 Then 'y' Else 'n' End As [Full],
Case When tblSharePermissions.denyAccess = 1 Then 'y' Else 'n' End As Denied,
tblShares.Lastchanged
From tblShares
Inner Join tblAssets On tblShares.AssetID = tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblSharesUni On tblShares.ShareUniqueID =
tblSharesUni.ShareUniqueID
Inner Join tblSharePermissions
On tblShares.ShareID = tblSharePermissions.ShareID
Left Join tblADusers On tblADusers.Userdomain + '\' + tblADusers.Username =
tblSharePermissions.trustee
Left Join tblADObjects On tblADObjects.ADObjectID =
tblADusers.ManagerADObjectId
Where tblAssetCustom.State = 1 And tblSharesUni.Type = 0
Order By tblAssets.AssetName
Susan.A wrote:
Rami Ferwana wrote:
Can you add the display name ,login name, department, title and manager name for [for user/group]?

Please try the report below.
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tblAssets.Description,
tsysOS.OSname,
tblSharesUni.Name,
tblSharesUni.Path,
tsysOS.Image As icon,
tblSharePermissions.trustee As [for user/group],
tblADusers.Username,
tblADusers.Userdomain,
tblADusers.Displayname,
tblADusers.Department,
tblADusers.Title,
tblADObjects.sAMAccountName As Manager,
Case When tblSharePermissions.readAccess = 1 Then 'y' Else 'n' End As [Read],
Case When tblSharePermissions.writeAccess = 1 Then 'y' Else 'n' End As Write,
Case When tblSharePermissions.fullAccess = 1 Then 'y' Else 'n' End As [Full],
Case When tblSharePermissions.denyAccess = 1 Then 'y' Else 'n' End As Denied,
tblShares.Lastchanged
From tblShares
Inner Join tblAssets On tblShares.AssetID = tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblSharesUni On tblShares.ShareUniqueID =
tblSharesUni.ShareUniqueID
Inner Join tblSharePermissions
On tblShares.ShareID = tblSharePermissions.ShareID
Left Join tblADusers On tblADusers.Userdomain + '\' + tblADusers.Username =
tblSharePermissions.trustee
Left Join tblADObjects On tblADObjects.ADObjectID =
tblADusers.ManagerADObjectId
Where tblAssetCustom.State = 1 And tblSharesUni.Type = 0
Order By tblAssets.AssetName


Is there any way that the name of the owner/user who created/altered the "share" is included in the report ?
Bradford
Engaged Sweeper II
It's working now. Thanks so much guys!
Daniel_B
Lansweeper Alumni
I've modified the report in the first post again. It still had a syntax mistake in the alias names.
Bradford
Engaged Sweeper II
Thanks Daniel, this looks like exactly what I need...

Unfortunately though when I paste in the code and save I get this error:

"Error while saving: "There was an error parsing the query. [ Token line number = 1,Token line offset = 299,Token in error = Read ]""

I just copied it manually (vs copy and pasting) and I am still getting the same error.

Please help, I do not know enough about SQL to understand what I am doing at this point, or what type of error this is.

Thanks!
Daniel_B
Lansweeper Alumni
Thanks for the reminder. We have corrected it in the report above.
sukaitsu
Champion Sweeper
Great report! I noticed there needs to be ' ' surrounding the End As aliases in your case statement.

Case When tblSharePermissions.readAccess = 1 Then 'y' Else 'n' End As 'Read',
Case When tblSharePermissions.writeAccess = 1 Then 'y' Else 'n' End As 'Write',
Case When tblSharePermissions.fullAccess = 1 Then 'y' Else 'n' End As 'Full',
Case When tblSharePermissions.denyAccess = 1 Then 'y' Else 'n' End As 'Denied',


Thank you,

Jeffrey
Thank you, Jeffrey Smith Enterprise Applications Security (319) 499-6310 JefSmith@geico.com