This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

Custom Reports with Custom Quieries breaks after upgrading to Enterprise Reporter 3.0

Hi,

I need help in fixing my Custom Report Queries when I upgraded Enterprise Reporter to version 3.0.  Somehow the database schema changed.  All of my custom reports display no results.

Here is an example of "Folder Permission with Membership"

=============================================

declare @rootfolder nvarchar(200);

set @rootfolder='AUDITORS';


SELECT LEFT(REPLACE(tblNTFS.Path, '\\tfc4avfs01.tfcnet.texas.gov\share\', ''), LEN(REPLACE(tblNTFS.Path, '\\tfc4avfs01.tfcnet.texas.gov\share\', '')) - 1) AS [Path (NTFS)], [Type (ACE)] =
CASE [tblACE].[AceType]
WHEN 0 THEN N'Allow'
WHEN 1 THEN N'Deny'
ELSE ''
END,

CASE WHEN LEN([tblAccount_1].[SAMAccountDomain])
= 0 THEN [tblAccount_1].[SAMAccountName] ELSE [tblAccount_1].[SAMAccountName] END AS [Account Name (Account)],
SUBSTRING(vwAccountMembers.MemberAccountName, 8, 200) AS [Member Name (Account Member)], CASE dbo.udfBitmaskLookup(N'ACE', N'AccessMask', 1033,
tblACE.AccessMask)
WHEN 'Create files/Write data, Create folders/append data, Write extended attributes, Delete subfolder and files, Write attributes, Delete, Change permissions, Take ownership'
THEN 'Read/Write/Delete' WHEN 'Full control' THEN 'Read/Write/Delete'
WHEN 'List folder/read data, Create files/Write data, Create folders/append data, Read extended attributes, Write extended attributes, Traverse folder/execute file, Delete subfolder and files, Read attributes, Write attributes, Delete, Read permissions'
THEN 'Read/Write/Delete'
WHEN 'List folder/read data, Create files/Write data, Create folders/append data, Read extended attributes, Write extended attributes, Traverse folder/execute file, Delete subfolder and files, Read attributes, Write attributes, Delete, Read permissions, Change permissions'
THEN 'Read/Write (research)'
WHEN 'List folder/read data, Read extended attributes, Traverse folder/execute file, Read attributes'
THEN 'Read Only'
WHEN 'List folder/read data, Read extended attributes, Traverse folder/execute file, Read attributes, Change permissions'
THEN 'Read Only'
WHEN 'Modify, Synchronize'
THEN 'Create/Modify'
WHEN 'Read and execute, Synchronize' THEN 'Read Only' END AS [Permissions (ACE)],
base.CollectedTime AS [Last Collected Time (Computer)], tblComputer.ComputerName AS [Computer Name (Computer)]
FROM tblNTFSComputer AS base INNER JOIN
tblComputer ON base.ComputerID = tblComputer.ComputerID LEFT OUTER JOIN
tblACL AS tblACL ON tblComputer.ComputerID = tblACL.ComputerID AND (tblACL.LDM_TOMBSTONED = 0 OR
tblACL.LDM_TOMBSTONED IS NULL) FULL OUTER JOIN
tblNTFS AS tblNTFS ON base.NTFSComputerID = tblNTFS.NTFSComputerID AND tblACL.ACLID = tblNTFS.ACLID LEFT OUTER JOIN
tblAccount AS tblAccount ON tblACL.OwnerAccountID = tblAccount.AccountID AND (tblAccount.LDM_TOMBSTONED = 0 OR
tblAccount.LDM_TOMBSTONED IS NULL) LEFT OUTER JOIN
tblACE AS tblACE ON tblACL.ACLID = tblACE.ACLID AND (tblACE.LDM_TOMBSTONED = 0 OR
tblACE.LDM_TOMBSTONED IS NULL) LEFT OUTER JOIN
tblAccount AS tblAccount_1 ON tblACE.AccountID = tblAccount_1.AccountID AND (tblAccount_1.LDM_TOMBSTONED = 0 OR
tblAccount_1.LDM_TOMBSTONED IS NULL) LEFT OUTER JOIN
vwAccountMembers AS vwAccountMembers ON tblAccount_1.AccountID = vwAccountMembers.GroupAccountID
WHERE (tblNTFS.Path IN (N'\\TFC4AVFS01.tfcnet.texas.gov\Share\' + @rootfolder ) OR
tblNTFS.Path LIKE N'\\TFC4AVFS01.tfcnet.texas.gov\Share\' + @rootfolder + '\%') AND (tblACE.AppliesTo <> 1) AND (tblNTFS.IsFolder = 1) AND (1 = 1) AND (tblACE.IsInherited = 0) AND
(1 = 1) AND (1 = 1) AND (base.LDM_TOMBSTONED = 0 OR
base.LDM_TOMBSTONED IS NULL) AND (vwAccountMembers.MemberAccountType IS NOT NULL)
ORDER BY [Account Name (Account)], [Path (NTFS)], CASE [tblACE].[AceType] WHEN 0 THEN N'Allow' WHEN 1 THEN N'Deny' ELSE '' END, [Member Name (Account Member)]

================================================================================

 

Here is an example of : Folder Groups with Membership>

declare @rootfolder nvarchar(200);

set @rootfolder='AUDITORS';


SELECT DISTINCT [Account Name (Account)], [Member Name (Account Member)]
FROM (SELECT CASE WHEN LEN([tblAccount_1].[SAMAccountDomain])
= 0 THEN [tblAccount_1].[SAMAccountName] ELSE [tblAccount_1].[SAMAccountName] END AS [Account Name (Account)],
SUBSTRING(vwAccountMembers.MemberAccountName, 8, 200) AS [Member Name (Account Member)], tblNTFS.Path, tblACE.IsInherited
FROM tblNTFSComputer AS base INNER JOIN
tblComputer ON base.ComputerID = tblComputer.ComputerID LEFT OUTER JOIN
tblACL AS tblACL ON tblComputer.ComputerID = tblACL.ComputerID AND (tblACL.LDM_TOMBSTONED = 0 OR
tblACL.LDM_TOMBSTONED IS NULL) FULL OUTER JOIN
tblNTFS AS tblNTFS ON base.NTFSComputerID = tblNTFS.NTFSComputerID AND tblACL.ACLID = tblNTFS.ACLID LEFT OUTER JOIN
tblAccount AS tblAccount ON tblACL.OwnerAccountID = tblAccount.AccountID AND (tblAccount.LDM_TOMBSTONED = 0 OR
tblAccount.LDM_TOMBSTONED IS NULL) LEFT OUTER JOIN
tblACE AS tblACE ON tblACL.ACLID = tblACE.ACLID AND (tblACE.LDM_TOMBSTONED = 0 OR
tblACE.LDM_TOMBSTONED IS NULL) LEFT OUTER JOIN
tblAccount AS tblAccount_1 ON tblACE.AccountID = tblAccount_1.AccountID AND (tblAccount_1.LDM_TOMBSTONED = 0 OR
tblAccount_1.LDM_TOMBSTONED IS NULL) LEFT OUTER JOIN
vwAccountMembers AS vwAccountMembers ON tblAccount_1.AccountID = vwAccountMembers.GroupAccountID
WHERE (vwAccountMembers.MemberAccountType IS NOT NULL) AND (tblAccount_1.SAMAccountName <> 'Domain Admins') AND (tblACE.AppliesTo <> 1) AND (tblNTFS.Path IN (N'\\TFC4AVFS01.tfcnet.texas.gov\Share\' + @rootfolder ) OR
tblNTFS.Path LIKE N'\\TFC4AVFS01.tfcnet.texas.gov\Share\' + @rootfolder + '\%') AND (tblComputer.ComputerName IN (N'TFC4AVFS01.tfcnet.texas.gov')) AND
(tblACE.IsInherited = 0) AND (tblNTFS.IsFolder = 1)) AS derivedtbl_1

===========================================================================================

 

Any help with modifying this query is appreciated.

 

Mike