We constantly have a demand for knowing who is registered to a specific role and know who downloaded what documnet and when.
Below are a couple SQL queries that will help accomplish those tasks.
Users attached to a role:
Output is in the form of:
UserID:RoleName:Username:Email:FirstName:LastName:DisplayName:CompanyName:PhoneNumber
----------Beginning----------
USE [DotNetNuke]
GO
DECLARE @rid AS VARCHAR(255)
SET @rid = 'Role name goes here%'
SELECT u.[UserID],r.[RoleName],u.[Username],u.[Email],u.[FirstName],u.[LastName],u.[DisplayName],
up.[PropertyValue] 'CompanyName',up1.[PropertyValue] 'PhoneNumber'
FROM [UserRoles] ur INNER JOIN [Roles] r ON ur.[RoleID] = r.[RoleID]
INNER JOIN [Users] u ON ur.[UserID]=u.[UserID]
INNER JOIN ([UserProfile] up INNER JOIN [ProfilePropertyDefinition] ppd ON up.[PropertyDefinitionID]=ppd.[PropertyDefinitionID])
ON up.[UserID]=u.[UserID]
inner JOIN ([UserProfile] up1 INNER JOIN [ProfilePropertyDefinition] ppd2 ON up1.[PropertyDefinitionID]=ppd2.[PropertyDefinitionID])
ON up1.[UserID]=u.[UserID]
WHERE r.[RoleName] LIKE @rid AND ppd.[PropertyName]='CompanyName' AND ppd2.[PropertyName]='Telephone'
----------End----------
Downloads by folder (or Role):
Output is in the form of:
path:EntryId:LastModified:OriginalFileName:DownloadDate:UserId:Username:Email:FirstName:LastName:DisplayName:Company
-----beginning------
USE [DotNetNuke]
DECLARE @projectfolder varchar(256)
SET @projectfolder = '/Name of role or folder goes here%'
SELECT dbo.DMXDAV_GetPath(en.CollectionId,en.PortalId,NULL) AS 'path',en.EntryId,en.LastModified,en.OriginalFileName
,MIN(lg.Datime) AS 'DownloadDate',
lg.UserId,u.[Username],u.[Email],u.[FirstName],u.[LastName],u.[DisplayName],up.PropertyValue AS 'Company'
FROM dbo.DMX_Entries en
INNER JOIN dbo.DMX_Log lg ON en.EntryId = lg.EntId
INNER JOIN dbo.Users u ON lg.UserId=u.UserID
LEFT JOIN (dbo.UserProfile up
INNER JOIN dbo.ProfilePropertyDefinition pd ON up.PropertyDefinitionID = pd.PropertyDefinitionID)
ON u.UserID = up.UserID
WHERE en.CollectionId<>en.EntryId AND (dbo.DMXDAV_GetPath(en.CollectionId,en.PortalId,NULL)) like @projectfolder
AND lg.Action='Download' AND pd.PropertyName='CompanyName'
GROUP BY dbo.DMXDAV_GetPath(en.CollectionId,en.PortalId,NULL),en.EntryId,en.LastModified,en.OriginalFileName,
lg.UserId,u.[Username],u.[Email],u.[FirstName],u.[LastName],u.[DisplayName],DATEPART(dy,lg.Datime),up.PropertyValue
UNION SELECT dbo.DMXDAV_GetPath(en.CollectionId,en.PortalId,NULL) AS 'path',en.EntryId,en.LastModified,en.OriginalFileName
,MIN(lg.Datime) AS 'DownloadDate',
lg.UserId,u.[Username],u.[Email],u.[FirstName],u.[LastName],u.[DisplayName],'N/A' AS 'Company'
FROM dbo.DMX_Entries en
INNER JOIN dbo.DMX_Log lg ON en.EntryId = lg.EntId
INNER JOIN dbo.Users u ON lg.UserId=u.UserID
WHERE en.CollectionId<>en.EntryId AND (dbo.DMXDAV_GetPath(en.CollectionId,en.PortalId,NULL)) like @projectfolder
AND lg.Action='Download' AND NOT EXISTS
(SELECT TOP 1 * FROM dbo.UserProfile INNER JOIN dbo.ProfilePropertyDefinition ON dbo.UserProfile.PropertyDefinitionID = dbo.ProfilePropertyDefinition.PropertyDefinitionID
WHERE dbo.UserProfile.UserID=u.UserID AND PropertyName='CompanyName')
GROUP BY dbo.DMXDAV_GetPath(en.CollectionId,en.PortalId,NULL),en.EntryId,en.LastModified,en.OriginalFileName,
lg.UserId,u.[Username],u.[Email],u.[FirstName],u.[LastName],u.[DisplayName],DATEPART(dy,lg.Datime)
ORDER BY u.LastName
-----end-------
**for this one we found a slight flaw in DMXDAV_GetPath that we had to correct. We would get into an infinate loop if the entryID=collectionID which occured on some oprhaned entries. Fix is below.
<!--[if gte mso 9]>
Normal
0
false
false
false
EN-US
X-NONE
X-NONE
MicrosoftInternetExplorer4
<!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;
mso-font-charset:1;
mso-generic-font-family:roman;
mso-font-format:other;
mso-font-pitch:variable;
mso-font-signature:0 0 0 0 0 0;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;
mso-font-charset:0;
mso-generic-font-family:swiss;
mso-font-pitch:variable;
mso-font-signature:-520092929 1073786111 9 0 415 0;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{mso-style-unhide:no;
mso-style-qformat:yes;
mso-style-parent:"";
margin:0in;
margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-fareast-font-family:Calibri;
mso-fareast-theme-font:minor-latin;}
.MsoChpDefault
{mso-style-type:export-only;
mso-default-props:yes;
font-size:10.0pt;
mso-ansi-font-size:10.0pt;
mso-bidi-font-size:10.0pt;}
@page Section1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;
mso-header-margin:.5in;
mso-footer-margin:.5in;
mso-paper-source:0;}
div.Section1
{page:Section1;}
-->
<!--[if gte mso 10]>
--SBL 2010-01-27 added exit if entryID=collectionID
IF EXISTS (SELECT EntryId FROM dbo.DMX_Entries
WHERE EntryId=@EntryId AND PortalId=@PortalId AND CollectionId<>EntryId)