DQL: Sum the Sum of Total Access, Exporting Dashboards

Hi Evan,

If you are referring to activity performed by user on each site, following query will provide user activity count per sharepoint site collection grouped by access type. Please adjust the web-app name and activity timeperiod as per your need.

FROM    activity
GET    path.msu.name as site_coll_name,
user.principal_name as user_name,
operation,
sum(count) as access_count
where
path.device.name = "<Web-app OR filer name>"
and timestamp >= datetime("2014-07-01", "YYYY-MM-DD") AND
timestamp <= datetime("2014-12-04", "YYYY-MM-DD")
groupby  
path.msu.name,
user.name,
user.domain,
operation

If you are referring to grouping user access by permission levels on various sites / control points in the sharepoint hieararchy, DQL along with some post-processing can be utilized to fetch such information.

Reference Queries for example:

DQL:

FROM     permission
GET
path.absname,
user_trustee.principal_name,
msu.name,
object_type,
isinherited,
group_trustee.name,
group_trustee.memberusers.principal_name,
readable_permission
IF
msu.device.name = "<Web-app name>"

SQL:

select
user,
msu_name as site_coll,
readable_permission,
count(path_absname) as no_of_control_points
from
(select distinct path_absname,
(case when user_trustee_principal_name != "" then user_trustee_principal_name
else gtmu.group_trustee_memberusers_principal_name end) as user,
msu_name,
readable_permission,
isinherited
from
permission p
left outer join
group_trustee_memberusers gtmu on p.permission_rowid = gtmu.permission_rowid
where isinherited = 0
and user!="") as distinct_users
group by
user,
msu_name,
readable_permission

You can tweak the queries to get result as per your need.

For exports, while Data Insight dashboard and tabular data all over the UI can be exported in CSV format for excel, graphs are not exportable today. What kind of information are you looking to export? Most of the summary information can be generated using custom DQL reporting, if not available through canned reports.

Thanks,

Rishi

Thanks for the help Rishi.  This information is great.  In regard to the information I am looking to export, I can obtain the information I need through the CSV export option on the DI dashboard, I was just looking to see if I could export the graphs as well and not have to create my own each time I exported data.

Regards,

Evan

Evan:

The dashboard data is calculated. If you wish to refresh it other than the default at midnight you can use the Compute Now button from the global settings Advanced Analytics Configuration page. You can also change the history scope or default run time.

Exporting is only available for the resulting table  as the graphs are a rendering of the data points. You can use a spreadsheet macro to create your graphs and import your table on each export. Other than a screenshot which is representative at the time you viewed the dashboard report the graphs cannot be captured from the application in the latest released version.

FYI Rod

An update on the DQL you provided Rishi.  I attempted to use the following query (based on the DQL you provided) and received a blank result:

FROM    activity

GET    path.msu.name as site_coll_name,

user.principal_name as user_name,

operation,

sum(count) as access_count

where

path.device.name = "<http://teamnavigator.rf.lilly.com/sites/2301691>"

and timestamp >= datetime("2014-07-01", "YYYY-MM-DD") AND

timestamp <= datetime("2014-12-17", "YYYY-MM-DD")

groupby 

path.msu.name,

user.name,

user.domain,

operation

I'm not sure where the error in the DQL is, but it would be great to get this resolved.

Thanks,

Evan

Hi Evan,

path.device.name expects a web application name. That would be "teamnavigator.rf.lilly.com" in your case. If you want to execute a query against a site-collection, please utilize path.msu.name instead.

Let me know if you need any further information. Also, for additional documentation and quick reference, please refer to Data Insight's programmer's reference guide that comes with the product.

Thanks,

Rishi

Hello Rishi,

Your comment was marked as solved by someone other than me.  The solution you provided still gave me a blank response when creating a report.  I'm not sure how to fix the error, or I may have misunderstood your original solution.  Please let me know your thoughts on this when you get the chance.

Thanks,

Evan

Any update on this?

Hi Evan,

Can you post the exact query you are trying now with web-app name? Are you getting any error while saving the report?

Thanks,

Rishi