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!



0 votes, average: 0.00 out of 50 votes, average: 0.00 out of 50 votes, average: 0.00 out of 50 votes, average: 0.00 out of 50 votes, average: 0.00 out of 5 (0 votes, average: 0.00 out of 5)
You need to be a registered member to rate this post.
Loading...Loading...
Categories: Uncategorized

Disclaimer: As with everything else at NetIQ Cool Solutions, this content is definitely not supported by NetIQ, so Customer Support will not be able to help you if it has any adverse effect on your environment.  It just worked for at least one person, and perhaps it will be useful for you too.  Be sure to test in a non-production environment.

Leave a Reply

Leave a Comment

  • hpv-eng1 says:

    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

    • rkalfane says:

      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

By: rkalfane
Feb 13, 2008
8:25 am
Reads:
2,053
Score:
Unrated