Monday, June 22, 2009

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

3 comments:

Arghya Roy said...

A Basic Quesion. What language is this?

John Minkjan said...

@Arghya

VBS => Visual Basic Script

regards

John

Owen said...

To future users, the original script would not run as written due to some line break issues with the If loops. To save you headaches if this ever comes up, here is a revision of the code that should work if you copy paste into a text file and rename it to .vbs:

'''''''''''''''''''''''''''''''''
'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

Const ForReading = 1
Set objFSO = CreateObject("Scripting.FileSystemObject")

'point this to your UDML EXPORT
Set objFile = objFSO.OpenTextFile("C:\YourFileNameHere.txt", ForReading)

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 If
Loop
objFile.Close