Emailing Users with Required AD Groups to get access to a specific Dashboard

[Mahesh Ankaraju]

Hi, 

Looking for a tools/ way to find the AD Groups/ local Groups that a user must be part of to gain access to a specific Dashboard. Currently it can be found using analyst but is there a way to extract these details using API.

More details, we keep receiving emails from USERS (mostly new, some existing users who lost access to AD Groups because of reconciliation etc) saying they are not able to open dashboard and when investigated it is found that they are not part of the AD Groups that will give them the access to that dashboard. I am looking for a way to automate a email reply with such groups to them based on the dashboard path that they are trying to access. May be using API and other functionalities like email from Spotfire and achieve this.

[David Boot-Olazabal]

Hi Manesh,

You can use the USERS and GROUPS tables, from the Spotfire server database, to find out which user is related to which group. In there, you should also find the email address of a given user.
When you join this with your library data (different LIBRARY tables to be used from the Spotfire server database), you can create an overview of what you want to see.

I am not sure about the automated way of an email reply, since I assume the receiving email is outside Spotfire. I don't see yet, how you could add the proper data from your created Spotfire User/Group/Library access overview and import that into a automated mail reply.
But the  again, you may have an application in which you can create an automated reply and add external data, based on certain triggers (the sender's email address in this case).

Kind regards,

David

[Mahesh Ankaraju]

Will you be able help me with a sql query to get that view you spoke in your comment? some thing like Dashboard name-ad groups having access-users., if that is possible i can have something built to find the ad group/ local group against the dashboard name and library path and have it sent to user.

[David Boot-Olazabal]

Hi Mahesh,

You can use this SQL for combining Users, Groups and LIB_ITEMS (where the information about the dashboard and path is stored):
 

SELECT l.item_id,l.title, l.format_version, l.content_size
,la.GROUP_ID, g.group_name, u.user_name, u.display_name, u.email,
concat(p15.title,'/',p14.title,'/',p13.title,'/',p12.title,'/',p11.title,'/',p10.title,'/',p9.title,'/',p8.title,'/',p7.title,'/',p6.title,'/',p5.title,'/',p4.title,'/',p3.title,'/',p2.title,'/',p1.title ) as Folder1, 
concat(p15.title,'/',p14.title,'/',p13.title,'/',p12.title,'/',p11.title,'/',p10.title,'/',p9.title,'/',p8.title,'/',p7.title,'/',p6.title,'/',p5.title,'/',p4.title,'/',p3.title,'/',p2.title ) as Folder2, 
concat(p15.title,'/',p14.title,'/',p13.title,'/',p12.title,'/',p11.title,'/',p10.title,'/',p9.title,'/',p8.title,'/',p7.title,'/',p6.title,'/',p5.title,'/',p4.title,'/',p3.title ) as Folder3, 
concat(p15.title,'/',p14.title,'/',p13.title,'/',p12.title,'/',p11.title,'/',p10.title,'/',p9.title,'/',p8.title,'/',p7.title,'/',p6.title,'/',p5.title,'/',p4.title) as Folder4, 
concat(p15.title,'/',p14.title,'/',p13.title,'/',p12.title,'/',p11.title,'/',p10.title,'/',p9.title,'/',p8.title,'/',p7.title,'/',p6.title,'/',p5.title) as Folder5, 
concat(p15.title,'/',p14.title,'/',p13.title,'/',p12.title,'/',p11.title,'/',p10.title,'/',p9.title,'/',p8.title,'/',p7.title,'/',p6.title) as Folder6, 
concat(p15.title,'/',p14.title,'/',p13.title,'/',p12.title,'/',p11.title,'/',p10.title,'/',p9.title,'/',p8.title,'/',p7.title) as Folder7, 
concat(p15.title,'/',p14.title,'/',p13.title,'/',p12.title,'/',p11.title,'/',p10.title,'/',p9.title,'/',p8.title) as Folder8, 
concat(p15.title,'/',p14.title,'/',p13.title,'/',p12.title,'/',p11.title,'/',p10.title,'/',p9.title) as Folder9, 
concat(p15.title,'/',p14.title,'/',p13.title,'/',p12.title,'/',p11.title,'/',p10.title) as Folder10, 
concat(p15.title,'/',p14.title,'/',p13.title,'/',p12.title,'/',p11.title) as Folder11, 
concat(p15.title,'/',p14.title,'/',p13.title,'/',p12.title) as Folder12, 
concat(p15.title,'/',p14.title,'/',p13.title) as Folder13, 
concat(p15.title,'/',p14.title) as Folder14, 
p15.title as Folder15, 
count (distinct c.user_id) as NumberUsers
FROM [dbo].LIB_ITEMS l
LEFT JOIN lib_items p1 on p1.ITEM_ID=l.PARENT_ID
LEFT JOIN lib_items p2 on p2.ITEM_ID=p1.PARENT_ID
LEFT JOIN lib_items p3 on p3.ITEM_ID=p2.PARENT_ID
LEFT JOIN lib_items p4 on p4.ITEM_ID=p3.PARENT_ID
LEFT JOIN lib_items p5 on p5.ITEM_ID=p4.PARENT_ID
LEFT JOIN lib_items p6 on p6.ITEM_ID=p5.PARENT_ID
LEFT JOIN lib_items p7 on p7.ITEM_ID=p6.PARENT_ID
LEFT JOIN lib_items p8 on p8.ITEM_ID=p7.PARENT_ID
LEFT JOIN lib_items p9 on p9.ITEM_ID=p8.PARENT_ID
LEFT JOIN lib_items p10 on p10.ITEM_ID=p9.PARENT_ID
LEFT JOIN lib_items p11 on p11.ITEM_ID=p10.PARENT_ID
LEFT JOIN lib_items p12 on p12.ITEM_ID=p11.PARENT_ID
LEFT JOIN lib_items p13 on p13.ITEM_ID=p12.PARENT_ID
LEFT JOIN lib_items p14 on p14.ITEM_ID=p13.PARENT_ID
LEFT JOIN lib_items p15 on p15.ITEM_ID=p14.PARENT_ID
LEFT OUTER JOIN USERS c ON c.USER_ID=l.CREATED_BY
LEFT OUTER JOIN USERS m ON m.USER_ID=l.MODIFIED_BY
LEFT OUTER JOIN LIB_ACCESS la ON la.ITEM_ID= p1.ITEM_ID
LEFT OUTER JOIN GROUPS g ON g.GROUP_ID=la.GROUP_ID
LEFT OUTER JOIN GROUP_MEMBERS gm ON gm.[GROUP_ID]= g.[GROUP_ID]
LEFT OUTER JOIN USERS u on u.USER_ID=gm.MEMBER_USER_ID
 WHERE l.item_type IN ( 
    SELECT type_id 
      FROM LIB_ITEM_TYPES 
      WHERE label IN ('folder','dxp'))
and g.group_name not in ('Administrator','Scheduled Updates Users','Library Administrator')
GROUP BY
l.item_id,l.title, l.format_version, l.content_size,la.GROUP_ID, g.group_name, u.user_name, u.display_name, u.email,
concat(p15.title,'/',p14.title,'/',p13.title,'/',p12.title,'/',p11.title,'/',p10.title,'/',p9.title,'/',p8.title,'/',p7.title,'/',p6.title,'/',p5.title,'/',p4.title,'/',p3.title,'/',p2.title,'/',p1.title ),
concat(p15.title,'/',p14.title,'/',p13.title,'/',p12.title,'/',p11.title,'/',p10.title,'/',p9.title,'/',p8.title,'/',p7.title,'/',p6.title,'/',p5.title,'/',p4.title,'/',p3.title,'/',p2.title ),
concat(p15.title,'/',p14.title,'/',p13.title,'/',p12.title,'/',p11.title,'/',p10.title,'/',p9.title,'/',p8.title,'/',p7.title,'/',p6.title,'/',p5.title,'/',p4.title,'/',p3.title ),
concat(p15.title,'/',p14.title,'/',p13.title,'/',p12.title,'/',p11.title,'/',p10.title,'/',p9.title,'/',p8.title,'/',p7.title,'/',p6.title,'/',p5.title,'/',p4.title), 
concat(p15.title,'/',p14.title,'/',p13.title,'/',p12.title,'/',p11.title,'/',p10.title,'/',p9.title,'/',p8.title,'/',p7.title,'/',p6.title,'/',p5.title), 
concat(p15.title,'/',p14.title,'/',p13.title,'/',p12.title,'/',p11.title,'/',p10.title,'/',p9.title,'/',p8.title,'/',p7.title,'/',p6.title), 
concat(p15.title,'/',p14.title,'/',p13.title,'/',p12.title,'/',p11.title,'/',p10.title,'/',p9.title,'/',p8.title,'/',p7.title), 
concat(p15.title,'/',p14.title,'/',p13.title,'/',p12.title,'/',p11.title,'/',p10.title,'/',p9.title,'/',p8.title), 
concat(p15.title,'/',p14.title,'/',p13.title,'/',p12.title,'/',p11.title,'/',p10.title,'/',p9.title), 
concat(p15.title,'/',p14.title,'/',p13.title,'/',p12.title,'/',p11.title,'/',p10.title), 
concat(p15.title,'/',p14.title,'/',p13.title,'/',p12.title,'/',p11.title), 
concat(p15.title,'/',p14.title,'/',p13.title,'/',p12.title), 
concat(p15.title,'/',p14.title,'/',p13.title), 
concat(p15.title,'/',p14.title), 
p15.title

You have to find out how much levels deep you have to go, for your path structure. The example above goes 15 levels deep.

In my own set up, since I have only 2 levels, the table would look like this:

And you can also add more information to the query of course, such as last login date for any given user. You can run a Select * From USERS to find out which columns are of interest.

Kind regards,

David