Mahesh Ankaraju Posted June 7 Share Posted June 7 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. Link to comment Share on other sites More sharing options...
David Boot-Olazabal Posted June 7 Share Posted June 7 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 Link to comment Share on other sites More sharing options...
Mahesh Ankaraju Posted June 7 Author Share Posted June 7 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. Link to comment Share on other sites More sharing options...
David Boot-Olazabal Posted June 10 Share Posted June 10 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 Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now