12-03-2014 12:12 PM
Is there a way to use DQL to demonstrate the sum of the sum of total access for sites? For example, you can generate reports to show the number of people with access levels of create, read, write, and delete per site, but can you also demonstrate that total per person? We are looking to determine an individual's level of access to each site monitored by DI, summed per level of access.
In addition to this, can you export the graphs and dashboards generated to Excel? You can save the raw data in table format, but the graphs would also be helpful.
Thanks!
Evan
12-05-2014 06:26 AM
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
12-16-2014 09:28 AM
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
12-16-2014 09:45 AM
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
12-18-2014 12:05 PM
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
12-18-2014 10:29 PM
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
01-09-2015 09:44 AM
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
01-21-2015 10:00 AM
Any update on this?
01-22-2015 05:55 AM
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