OBIEE Get all users and roles from RPD

In this post http://obiee101.blogspot.com/2009/06/obiee-get-all-users-from-rpd.html I showed you how to get the users from the RPD. Taking that as a starting point it's a small step to get users and the roles they have and put the export in an XLS:

'Read_Usergroups.VBS'John Minkjan'http:// http://www.obiee101.blogspot.com/
'Get all the users from a repository
'1: Make an UDML export of the PRD using nqgenudml.exe
'2: Change the filename/location in this script
'3: Run the script from the command line cscript Read_Usergroups.VBS > users.txt
'4: Put the export in a XLS Pivot table

Set objFSO = CreateObject("Scripting.FileSystemObject")
'point this to your UDML EXPORT
Set objFile = objFSO.OpenTextFile("E:\usergroup.txt", ForReading)
Const ForReading = 1
Dim arrFileLines()
dim strRLine
dim strTemp1
dim strTemp2
dim strTemp3
dim intRoles
intRoles = 0
i = 0
WScript.Echo "USER_NAME;FULL_NAME;ROLE;COUNT"
Do Until objFile.AtEndOfStream
strRline = objFile.ReadLine
if left(strRline,12) = "DECLARE USER" then Redim Preserve arrFileLines(i)
strTemp1 = MID(strRLine ,15 , 50)
strTemp1 = MID(strTemp1 ,1 , instr(strTemp1, """")-1)
IF instr(strRline,"}" ) >0 THEN
strTemp2 = MID(strRLine , instr(strRline,"{")+ 1, (instr(strRline,"}") - (instr(strRline,"{")+ 1)))
ELSE
strTemp2 = ""
END IF
arrFileLines(i) = strTemp1 &";" & strtemp2
intRoles = 1
i = i + 1
end if
if intRoles >= 1 then
if instr(strRline,"HAS ROLES (" ) >0 then
intRoles =2
end if
if intRoles =2 and instr(strRline,"HAS ROLES (" ) =0 then
strTemp3 = MID(strRline,instr(strRline, """")+1,50)
strTemp3 = MID(strTemp3,1,instr(strTemp3, """")-1)
WScript.Echo arrFileLines(i-1) &";" & strTemp3 &";1"
end if
if intRoles =2 and instr(strRline,")" ) >0 then intRoles = 0
end if
end ifLoop
objFile.Close

Till Next Time