Automatic Role Matrix

Absent Member.
Absent Member.
0 3 5,579
0 Likes
With a Little Help from the Charming Python

Table of Contents


Automatic Roles Matrix

    With a Little Help from the Charming Python

        Introduction

        Content of the attached archive

        Using the script

        Steps to create the spreadsheet document

        Technical Details

            Easy parsing of command-line options

            Loading Data

            Algorithm to build the matrix

        Summary






Introduction


When working with groups, roles or other resources, it may be useful to generate visual reports where you can see who has what, and if it could be a problem - for example, if a user has both ROLE_XXX and ROLE_YYY, and these roles are exclusives. A good representation of these links (who has these roles and which roles has this user) is a matrix with for-instance roles in the X-axis and users in the Y-axis.





Roles Matrix





Using the generate_matrix Python script attached to this article and a spreadsheet tool like OpenOffice Calc, you can produce such a report in a couple of minutes (including nice background colors!). The script can use an LDIF export of groups/roles and their members, or any other links you want; it can also directly connect to a LDAP server to retrieve data. It then generates a .CSV file that you can directly open and improve visually.





Content of the attached archive


Here is the content of the matrix.zip file:





./Matrix
+- generate_matrix
+- ldifstruct.py
+- groups.ldif
+- roles.ldif
+- matrix_totals.csv
+- matrix_totals.ods
+- matrix_totals.pdf
+- matrix_alpha.csv
+- matrix_alpha.ods
+- matrix_alpha.pdf
+- docs
+- matrix.txt
+- matrix.html
+- images_matrix
+- *.png






Details:

  • generate_matrix: the main script to generate the CSV matrix of roles/users or other type of links

  • ldifstruct.py: the LDIFStruct library found in different languages in the third part of the scripting article (see the first part and the second part)

  • *.ldif: sample LDIF files to test the script

  • matrix*.csv, matrix*.ods, matrix*.pdf: an example of CSV, OpenOffice Calc and PDF reports you can generate

  • matrix.txt: the Wiki source of this article

  • matrix.html: the result of the conversion from Wiki to HTML (see Wiki to CoolSolutions Converter)

  • /communities/media/u3740/*.png: all the pictures used in this article








Using the Script



You can call the script using a number of options from the command-line. You can get the list of options anytime using the -h or --help option:





/Matrix> ./generate_matrix -h
usage: generate_matrix [options]
generate roles matrix in csv format
-h or --help for help

example: generate_matrix -f groups.ldif > matrix.csv
generate_matrix -f groups.ldif -s alpha -a -t "My Roles"
-o "Totals" > matrix.csv
generate_matrix -f roles.ldif -A myUserMembership -a > matrix.csv
generate_matrix -m ldap -B ou=roles,o=org -D cn=admin,o=org -W
generate_matrix -m ldap -B ou=roles,o=org -S sub -H localhost
-D cn=admin,o=org -w mypass -F (objectClass=myRole)
> matrix.csv

options:
--version show program's version number and exit
-h, --help show this help message and exit
-c, --changelog display changelog
-m MODE input mode: file or ldap [default: file]
-f FILE read data from LDIF file
-H SERVER LDAP server [default: localhost]
-D BINDN bind DN
-w PASSWD bind password
-W prompt for bind password
-B BASEDN base DN for search
-S SCOPE search scope: base, one or sub [default: sub]
-F FILTER LDAP filter [default: (objectClass=groupOfNames)]
-A ATTRIBUTE LDAP membership attribute [default: member]
-t "MY TITLE" title of the report [default: "ROLES MATRIX"]
-o "TOTALS" label for totals [default: "TOTALS"]
-s SORTTYPE sort type: alpha or totals [default: totals]
-r sort by desc order, alpha or totals sort type [default]
-a sort by asc order, alpha or totals sort type
-v verbose mode
-q quiet mode [default]








You can test the script with the groups.ldif files or the roles.ldif found in the attached archive.





1. To generate a totals reverse-ordered matrix, use the following command:





/Matrix/> ./generate_matrix -f groups.ldif > matrix.csv








2. To generate an alphabetical-ordered matrix with different labels in the report, use the following command:





/Matrix/> ./generate_matrix -f groups.ldif -s alpha -a -t "My Roles" -o "Totals" > matrix.csv








3. To generate a totals-ordered matrix from an export with a different attribute from "member", use the following command:





/Matrix/> ./generate_matrix -f roles.ldif -A myUserMembership -a > matrix.csv








4. To retrieve data from an LDAP server, you must specify a bind DN and a password. It is also better to specify the base DN for the search, the attribute to use for the link, and an LDAP filter to exactly retrieve the objects you want.





/Matrix/> ./generate_matrix -m ldap -B ou=roles,o=org -S sub -H localhost -D cn=admin,o=org -w mypass -F (objectClass=myRole) > matrix.csv







From there, you have a .CSV file you can now open in your favorite spreadsheet tool (which is hopefully OpenOffice Calc).





Creating the Spreadsheet Document



Note: The following screenshots show the steps using OpenOffice Calc. You can get the same results using Excel.








5. Once you have generated your matrix.csv file using the generate_matrix Python script, double-click it from your file explorer. This should launch OpenOffice Calc or Excel, depending on your machine. If not, you can first open your spreadsheet tool and then open/import the CSV file.





Step 1






OpenOffice Calc will see that it is a .CSV file and will ask for the separator and the text delimiter to use.

6. Make sure the value is"Semicolon" for the separator, which may be checked by default.





Step 2







The document should look like the following:





Step 3







7. Change the text orientation to vertical for the first line. To do this,

a) Select the line and right-click it.
b) Choose "Format cells..." in OpenOffice Calc.
c) In the Alignment tab, set the orientation to vertical.





Step 4







Step 5







8. Change the column width either by selecting them and double clicking between two columns in the header, or by selecting auto-width, or by manually setting the width of the columns. You should end up with the following report:





Step 6







Improving the Document

1. You can improve this report by selecting all the columns and using the "Auto-filter" feature, to enhance the look of the document:





Step 7







2. Of course, a bit of color is always nice to have:





Step 8







3. It might also be a good idea to format the document for printing, by setting the page format, a header, a footer, and the scale. Here is how to format the page:





Step 9







4. Then you can format the header and set it:





Step 10







Step 11







5. You can format the footer and set it:





Step 12







Step 13







6. You can define the scale, if you want all your document to fit in one page, for instance:





Step 14







7. From there, you can export the result as a .PDF, and you should get the following:





Step 15







Step 16







You can record all these steps in a macro that will automatically format the report for you. It works pretty well on OpenOffice Calc and Excel.





Technical Details



Easy Parsing of command-line options

Python offers a very easy and powerful way to handle command-line arguments and options using the OptParser module. To use this feature,

1. Create a OptParser object at the beginning of your script, using the following code:





from optparse import OptionParser

# Handle command-line options and arguments
parser = OptionParser()








2. Define your options with the add_option method. You can use strings, multiple-choices or booleans for instance, and you can use short or long options:





parser.add_option( "-m", "--mode", action="store", type="choice", metavar="MODE", dest="mode", default="file", help="input mode: file or ldap [default: %default]", choices=["file","ldap"] )
parser.add_option( "-f", "--filename", action="store", type="string", metavar="FILE", dest="filename", help="read data from LDIF file" )
parser.add_option( "-r", "--reverse", action="store_true", dest="sort_reverse", default=True, help="sort by desc order, alpha or totals sort type [default]" )
parser.add_option( "-a", "--ascending", action="store_false", dest="sort_reverse", help="sort by asc order, alpha or totals sort type" )








You can see here the different types: "choice", "string" or boolean. For the two first options, you can use a "metavar" label, which will be used for calling the help option from the command-line.

3. Parse the command-line arguments and options by calling the parse_args() method. The options list will contain the values for each option and the args list will contain everything not listed in the options. The script will automatically check if the options are valid and well-written.





(options, args) = parser.parse_args()
print options
print args








At this point you have a script that handles command-line options with help. The test.py script is as follows:





#!/usr/bin/python
from optparse import OptionParser
parser = OptionParser()
parser.add_option( "-m", "--mode", action="store", type="choice", metavar="MODE", dest="mode", default="file", help="input mode: file or ldap [default: %default]", choices=["file","ldap"] )
parser.add_option( "-f", "--filename", action="store", type="string", metavar="FILE", dest="filename", help="read data from LDIF file" )
parser.add_option( "-r", "--reverse", action="store_true", dest="sort_reverse", default=True, help="sort by desc order, alpha or totals sort type [default]" )
parser.add_option( "-a", "--ascending", action="store_false", dest="sort_reverse", help="sort by asc order, alpha or totals sort type" )
(options, args) = parser.parse_args()
print options
print args








4. Make the script executable by using chmod +x test.py

5. Call the script to get the usage:





/Matrix> ./test.py -h
usage: test.py [options]

options:
-h, --help show this help message and exit
-m MODE, --mode=MODE input mode: file or ldap [default: file]
-f FILE, --filename=FILE
read data from LDIF file
-r, --reverse sort by desc order, alpha or totals sort type
[default]
-a, --ascending sort by asc order, alpha or totals sort type








6. Test the script to see the results:





/Matrix> ./test.py arg1 arg2 arg3 {'sort_reverse': True, 'mode': 'file', 'filename': None}
['arg1', 'arg2', 'arg3']

/Matrix> ./test.py -a -m ldap -f groups.ldif arg1 arg2 arg3 {'sort_reverse': False, 'mode': 'ldap', 'filename': 'groups.ldif'}
['arg1', 'arg2', 'arg3']

/Matrix> ./test.py --ascending --mode=ldap --filename=groups.ldif arg1 arg2 arg3
{'sort_reverse': False, 'mode': 'ldap', 'filename': 'groups.ldif'}
['arg1', 'arg2', 'arg3']







You can then use options.mode, options.filename, options.sort_reverse, args.arg1, args.arg2 and args.arg3 in your script. Have a look at the generate_matrix script to see more details.





Loading Data


The script can use two modes to load data: either by loading a LDIF file using the LDIFStruct library or by directly connecting to a LDAP server using Python LDAP module.





The following code loads data from an LDIF file (soon in the Building Your Scripts to Manipulate LDIF Export - Part 3 article):





from ldifstruct import LDIFStruct

myLDIFStruct = LDIFStruct()
myLDIFStruct.load( roles_users, options.filename )








The roles_users array will look like the following:





{
"cn=ROLE_AAAAA,...": [ "cn=User01,...", "cn=User02,..", ... ],
...
}








The following code connects directly to a LDAP server and gets data using a LDAP filter, a base DN and a membership attribute:





l = ldap.initialize( "ldap://" + options.ldap_server )
l.simple_bind_s( options.ldap_binddn, options.ldap_passwd )
scopes = { "sub": ldap.SCOPE_SUBTREE, "one": ldap.SCOPE_ONELEVEL, "base": ldap.SCOPE_BASE }
roles_users = dict( l.search_s( options.ldap_basedn, scopes[ options.ldap_scope ], options.ldap_filter, [ options.ldap_attribute ] ) )








It takes very few lines to connect to a LDAP server and to do a search. The good thing here is that the roles_users array has exactly the same format as when loaded with a file. Have a look at Python LDAP documentation for more details on using LDAP calls in your scripts.





Algorithm for Building the Matrix

Once the data is loaded in memory (in an associative array roles_users as represented in the first part of the scripting article, the keys contain the DN of all the roles.





1. For each role, extract the number of members and the list of all users. The pseudo-code for this is the following:





for each role in roles list
| if role has members
| | add members to the users list
| | store number of members for this role in an array
| end if
end for








Instead of using a normal list object, the script uses a set object when all values are unique.

2. For each role, add all the members to this object. The equivalent Python code for doing this is the following:





# Get list of all users and member totals for each role
users = set()
roles_totals = dict.fromkeys( roles_users.keys(), 0 )
for role in [ f for f in roles_users.keys() if roles_users[ f ].has_key( options.ldap_attribute ) ]:
users.update( roles_users[ role ][ options.ldap_attribute ] )
roles_totals[ role ] = len( roles_users[ role ][ options.ldap_attribute ] )








3. In the script, build a dictionary where each key is a role, and the value is the number of users. The content of the roles_totals after the second line looks like this:





{
...
...
"ROLE_AAAAA": 0,
"ROLE_BBBBB": 0,
"ROLE_CCCCC": 0,
"ROLE_DDDDD": 0,
"ROLE_EEEEE": 0,
"ROLE_FFFFF": 0,
"ROLE_GGGGG": 0,
...
...
}








After cycling through roles and update members numbers, the roles_totals array looks like this:





{
...
...
"ROLE_AAAAA": 4,
"ROLE_BBBBB": 25,
"ROLE_CCCCC": 3,
"ROLE_DDDDD": 4,
"ROLE_EEEEE": 9,
"ROLE_FFFFF": 9,
"ROLE_GGGGG": 3,
...
...
}








4. Order these totals by sorting this array by values. This can be a bit tricky, but here is a possible solution using Python (the key parameter enables to choose the sort criteria):





roles = [k for (k,v) in sorted( roles_totals.items(), key=itemgetter(1), reverse=options.sort_reverse )]








5. To get an alphabetical sorted list of roles instead of ordering by totals, you can get the keys of the structure and sort them like this:





roles = sorted( roles_users.keys() )








Now you have an ordered list of roles and all the users in a list.

6. You can cycle through all the users and build the matrix using the following pseudo code:





for each user in the users list
| total = 0
| start building the CSV line
| for each role in roles list
| | if the role has members and if the user is in this role
| | | add a "X" in the line
| | | total = total + 1
| | end if
| end for
| add total to the end of the line
end for








In the Python code, each line is stored in an array with the total number of "X" in it so we can sort them. The equivalent code looks like the following:





# Generate lines for each user
lines = []
for user in users:
line = '"%s"' % ( re.sub( "cn=(.*?),.*", "\\1", user ), )
total = 0
for role in roles:
line = line + ';'
if roles_users[ role ].has_key( options.ldap_attribute ) and user in roles_users[ role ][ options.ldap_attribute ]:
line = line + '"X"'
total += 1
lines.append( (total,line + ';"%s"' % total) )








The content of the groups.ldif file found in the attached archive looks like the following:





version: 1

dn: cn=ROLE_11111,dc=roles,o=org,dc=data
member: cn=user001,dc=users,o=org,dc=data
member: cn=user002,dc=users,o=org,dc=data
member: cn=user003,dc=users,o=org,dc=data
member: cn=user004,dc=users,o=org,dc=data
member: cn=user005,dc=users,o=org,dc=data
member: cn=user006,dc=users,o=org,dc=data
member: cn=user007,dc=users,o=org,dc=data
member: cn=user008,dc=users,o=org,dc=data
member: cn=user009,dc=users,o=org,dc=data
member: cn=user010,dc=users,o=org,dc=data

dn: cn=ROLE_22222,dc=roles,o=org,dc=data

dn: cn=ROLE_33333,dc=roles,o=org,dc=data
member: cn=user008,dc=users,o=org,dc=data
member: cn=user009,dc=users,o=org,dc=data
member: cn=user010,dc=users,o=org,dc=data

dn: cn=ROLE_44444,dc=roles,o=org,dc=data
member: cn=user002,dc=users,o=org,dc=data
member: cn=user003,dc=users,o=org,dc=data
member: cn=user011,dc=users,o=org,dc=data
member: cn=user008,dc=users,o=org,dc=data

dn: cn=ROLE_55555,dc=roles,o=org,dc=data
member: cn=user001,dc=users,o=org,dc=data
member: cn=user002,dc=users,o=org,dc=data
member: cn=user003,dc=users,o=org,dc=data
member: cn=user012,dc=users,o=org,dc=data
member: cn=user004,dc=users,o=org,dc=data
member: cn=user005,dc=users,o=org,dc=data
member: cn=user006,dc=users,o=org,dc=data
member: cn=user007,dc=users,o=org,dc=data
member: cn=user013,dc=users,o=org,dc=data
member: cn=user014,dc=users,o=org,dc=data
member: cn=user015,dc=users,o=org,dc=data
member: cn=user016,dc=users,o=org,dc=data
member: cn=user017,dc=users,o=org,dc=data
member: cn=user018,dc=users,o=org,dc=data
member: cn=user019,dc=users,o=org,dc=data
member: cn=user020,dc=users,o=org,dc=data
member: cn=user021,dc=users,o=org,dc=data
member: cn=user022,dc=users,o=org,dc=data
member: cn=user023,dc=users,o=org,dc=data
member: cn=user024,dc=users,o=org,dc=data
member: cn=user025,dc=users,o=org,dc=data
member: cn=user008,dc=users,o=org,dc=data
member: cn=user009,dc=users,o=org,dc=data
member: cn=user010,dc=users,o=org,dc=data








7. Run the script using ./generate_matrix -f groups.ldif > matrix.csv to get the matrix.csv file with the following content:





"ROLES  MATRIX";"ROLE_BBBBB";"ROLE_UUUUU";"ROLE_55555";"ROLE_QQQQQ";"ROLE_TTTTT";"ROLE_NNNNN";"ROLE_66666";"ROLE_11111";"ROLE_FFFFF";"ROLE_EEEEE";"ROLE_KKKKK";"ROLE_HHHHH";"ROLE_LLLLL";"ROLE_IIIII";"ROLE_SSSSS";"ROLE_PPPPP";"ROLE_WWWWW";"ROLE_DDDDD";"ROLE_VVVVV";"ROLE_RRRRR";"ROLE_AAAAA";"ROLE_ZZZZZ";"ROLE_44444";"ROLE_GGGGG";"ROLE_33333";"ROLE_OOOOO";"ROLE_CCCCC";"ROLE_JJJJJ";"ROLE_MMMMM";"ROLE_XXXXX";"ROLE_22222";"TOTALS"
"user002";"X";"X";"X";"X";"X";"X";;"X";"X";"X";"X";"X";"X";"X";"X";"X";"X";"X";"X";"X";"X";"X";"X";"X";;"X";"X";;;;;"25"
"user003";"X";"X";"X";"X";"X";"X";;"X";"X";"X";"X";"X";"X";"X";"X";"X";"X";"X";"X";"X";"X";"X";"X";"X";;;"X";;;;;"24"
"user001";"X";"X";"X";"X";"X";"X";;"X";"X";"X";"X";"X";"X";"X";"X";"X";"X";"X";"X";"X";"X";"X";;"X";;;"X";;;;;"23"
"user008";"X";"X";"X";"X";"X";"X";"X";"X";;;"X";"X";"X";"X";"X";"X";"X";"X";"X";;;"X";"X";;"X";;;"X";;;;"21"
"user026";"X";"X";;"X";"X";"X";;;;;"X";"X";"X";"X";"X";;;;;"X";"X";;;;;"X";;"X";"X";;;"15"
"user025";"X";"X";"X";"X";"X";"X";"X";;;;"X";"X";"X";"X";"X";;;;;;;;;;;;;;;;;"12"
"user010";"X";"X";"X";"X";"X";"X";"X";"X";"X";"X";;;;;;;;;;;;;;;"X";;;;;;;"11"
"user009";"X";"X";"X";"X";"X";"X";"X";"X";"X";"X";;;;;;;;;;;;;;;"X";;;;;;;"11"
"user004";"X";"X";"X";"X";"X";;"X";"X";"X";"X";;;;;;"X";;;;;;;;;;;;;;;;"10"
"user019";"X";"X";"X";"X";"X";"X";"X";;;;"X";"X";;;;;;;;;;;;;;;;;;;;"9"
"user007";"X";"X";"X";"X";"X";;;"X";"X";"X";;;;;;;;;;;;;;;;"X";;;;;;"9"
"user005";"X";"X";"X";"X";"X";;"X";"X";"X";"X";;;;;;;;;;;;;;;;;;;;;;"9"
"user006";"X";"X";"X";"X";"X";;;"X";"X";"X";;;;;;;;;;;;;;;;;;;;;;"8"
"user024";"X";"X";"X";"X";"X";"X";"X";;;;;;;;;;;;;;;;;;;;;;;;;"7"
"user023";"X";"X";"X";"X";"X";"X";"X";;;;;;;;;;;;;;;;;;;;;;;;;"7"
"user022";"X";"X";"X";"X";"X";"X";"X";;;;;;;;;;;;;;;;;;;;;;;;;"7"
"user021";"X";"X";"X";"X";"X";"X";"X";;;;;;;;;;;;;;;;;;;;;;;;;"7"
"user020";"X";"X";"X";"X";"X";"X";"X";;;;;;;;;;;;;;;;;;;;;;;;;"7"
"user018";"X";"X";"X";"X";"X";"X";"X";;;;;;;;;;;;;;;;;;;;;;;;;"7"
"user017";"X";"X";"X";"X";"X";"X";"X";;;;;;;;;;;;;;;;;;;;;;;;;"7"
"user016";"X";"X";"X";"X";"X";"X";"X";;;;;;;;;;;;;;;;;;;;;;;;;"7"
"user015";"X";"X";"X";"X";"X";"X";"X";;;;;;;;;;;;;;;;;;;;;;;;;"7"
"user014";"X";"X";"X";"X";"X";"X";"X";;;;;;;;;;;;;;;;;;;;;;;;;"7"
"user013";"X";"X";"X";"X";"X";"X";"X";;;;;;;;;;;;;;;;;;;;;;;;;"7"
"user012";"X";"X";"X";;;"X";;;;;"X";"X";;;;;"X";;;;;;;;;;;;;;;"7"
"user011";;;;;;;;;;;;;"X";;;;;;;;;;"X";;;;;;;;;"2"
"user029";;;;;;;"X";;;;;;;;;;;;;;;;;;;;;;;;;"1"
"user028";;;;;;;"X";;;;;;;;;;;;;;;;;;;;;;;;;"1"
"user027";;;;;;;"X";;;;;;;;;;;;;;;;;;;;;;;;;"1"
"TOTALS";"25";"25";"24";"24";"24";"21";"21";"10";"9";"9";"8";"8";"7";"6";"6";"5";"5";"4";"4";"4";"4";"4";"4";"3";"3";"3";"3";"2";"1";"0";"0"








What is interesting about sorting colomns and rows by totals is that on the top-left you will find the users with the most roles, and the roles with the most users. This could be pretty useful to detect users with too many roles. Ordering by name makes it easier to find a user or a role.





Now you are ready to generate the final spreadsheet!





Summary



The matrix reports created by the generate_matrix script are very quick to create, and it is very easy to open them in your spreadsheet tool and improve them visually. If you are interested in the technical details, the script demonstrates how to use the LDIFStruct library (in the upcoming third part of the scripting article series) and how to connect to a LDAP server to retrieve data. You can also see how to navigate through the structure in memory to build the matrix. A few lines of code enable you to either use LDIF exports or directly connect to an LDAP server to generate your reports. Happy creative reporting!



3 Comments
Absent Member.
Absent Member.
Hi Reza,

the tool works fine, but is there a possibility to have more attributes from the users, e.g. Surname, Given Name, department, a.s.o. ?

|cn|surname|givenname|ROLE-A|ROLE-x|

Kind regards,
Alex
Absent Member.
Absent Member.
Thanks for the feedback!

A new tool should be soon published (Automatic Tree Reports) where multiple attributes are displayed for a given entry.

You will be able to have a look and improve this script... Basically you have to specify in the LDAP search the different attributes you want to get and then extract the information from the LDIFStruct associative array.

Regards,

Reza
Absent Member.
Absent Member.
There is no link to download the script, maybe moved somewhere else?
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.