Reporting over LAUA was easy because everything was in the database. With later versions of Lawson and the implementation of Lawson Security, everything is stored in your LDAP. (Personally, I think that storing the data in the LDAP is a relative non-issue. However, the implementation that Lawson has done is epically stupid; specifically how it handles the services (Employee, Vendor, Requester and Customer) as strings that must be parsed and cannot be logically searched. Seriously?) Since all of the data is in the LDAP, you have two choices: use the reporting options in the Lawson Security tool or dump the data to a database table. Chances are that you’ll actually use both options, but for very different reasons.
It wasn’t long after we implemented RM (Resource Manager) that we started to understand the potential (and realized) issues. Consequently, we started dumping sections of the LDAP to database tables. Primarily we used the functionality in Lawson Smart Notes to read the LDAP and save the data to a database table. (There is a strong argument for this approach. If you’re interested in how to do it, leave a comment and I’ll make a follow up post). There are several potential problems with this though, namely that we have to have a separate table for each tree in the LDAP and there is no relation between them. A secondary problem is that our LBI is on SQL Server and our Lawson S3 environment is on Oracle. Combining the data is possible in Crystal Reports but cumbersome. Outside of Crytal Reports (and MS Access) it’s “impossible”. We started going down the path of using Lawson Process Flow and Transformation Extender to read the LDAP and write to tables in our Lawson schema, but that’s easier said than done (specifically when you have M:1 relationships like in an employee proxy situation or with a contractor to vendor scenario). We were also doing an “implement as required” approach, which lead to an in-cohesive amalgamation of tables with a fair amount of duplicated data. If you know me at all, you’ll know that this kind of thing makes me crazy. Enter AVAAP. For the record, AVAAP (and their solution) came to us on a very high recommendation from a very trustworthy source (Moffitt Health whom we have a personal connection with and who routinely presents at CUE/Inforum).
If you have implemented Lawson security, or you are planning to implement it, and you own LBI, you should contact AVAAP and buy their Lawson Security Dashboard. As part of the product, you will get a pre-built Framework Services dashboard and 25+ reports that have a fair amount of customization available. I haven’t run across any aspects that it doesn’t report over and they also offer the ability to do Segregation of Duties reporting. Even more importantly, they deliver a COBOL program (that calls a Java program) and dumps the ENTIRE LDAP to a set of tables that can be run on demand (Ours takes about 20 minutes for 36,000 users). I honestly can’t say whether the reports that were delivered are being used except by our Auditing department, but we use the tables for all kinds of things.
Primarily our uses are to add user names to reports. For example, we have a report that we send out for “Unapproved Invoices”. When you run the query over the LOGAN tables, it only has the user name. This is of little help to most people in the case of jbrown45. However, when we join our reports to the tables that AVAAP creates, we’re able to provide the user’s name (James Brown) on the report with the same level of performance that we’ve come to expect from LBI.
My single strongest argument (and how I believe you can justify ROI) is the “Copy User” functionality that is the ultimate point of this post. I’m sure that everyone in a security role has at some point been asked to “set up user x to be exactly like user y”.
This is how we do it:
- An LBI report is run that accepts a user name
- The LBI report returns the full security setup in a CSV output (which is formatted to be opened in Excel)
- The Security Group modifies necessary fields (name, employee, etc)
- The output is passed through a Perl script that reformats into XML to be loaded by the Lawson “loaduser” script
The report could be formatted to produce the XML required by “loaduser”, but we have several other automated processes built around the same CSV format that are subsequently passed into the Perl script. (If you’re interested in the Perl script, leave a comment and I may post it.)
Without further ado, let me give you the single best reason to buy the AVAAP solution (Note that I am not affiliated with AVAAP, nor am I receiving compensation for my recommendation):
–This is Oracle 11g syntax, although I believe that much of it (like PIVOT) will work on SQL Server as well, but the Hierarchy queries will not
SELECT Trim(RES.ZSUID) AS ID, SSOP.RMID AS RMID, OS.OSID AS OSID, OS.OSPASSWORD AS OSPASSWORD,
Trim(RES.ZSNAME) AS NAME, Trim(RES.FNAME) AS FirstName, Trim(RES.LNAME) AS LastName, RES.CHECKLS AS CheckLS,
ZROLES.ROLE_LIST AS "ROLE", ZGROUPS.GROUP_LIST AS "GROUP", Trim(RES.DEFPRODLINE) AS ProductLine, RES.ZSACCESS AS "ACCESS", Trim(RES.ACCTUNTCTRL) AS AccountingUnitControl, Trim(RES.ACTVYGRPCOLCT) AS AcgrpCollect,
Trim(ATTR.CUST_ATTR3) AS ACTimeApprover, Trim(RES.ACTVYLIST) AS ActivityList, RES.ADDINS AS ADDINS, RES.JOBQUEACCESS AS AllowJobQueue, '' AS COMMENTS, Trim(RES.COMPANYCTRL) AS CompanyControl,
Trim(EMAIL) AS Email, Trim(ATTR.CUST_ATTR1) AS ESSGroup, Trim(ATTR.CUST_ATTR5) AS GLNode1, Trim(ATTR.CUST_ATTR6) AS GLNode2, GLS.GLS AS GLStructure, Trim(ATTR.CUST_ATTR7) AS HRNode1,
Trim(ATTR.CUST_ATTR8) AS HRNode2, HRS.HRS AS HRStructure, Trim(ATTR.CUST_ATTR2) AS MSSGroup,
Trim(RES.LWSNOLEDB) AS OLEDBC, Trim(RES.PORTALADMIN) AS PortalAdmin, Trim(RES.PORTALROLE) AS PortalRole, '' AS PrimaryGroup, '' AS PrimaryStructure, Trim(RES.PRCSLVLCTRL) AS ProcessLevelControl,
Trim(ATTR.CUST_ATTR10) AS PROXYACCESS, Trim(RES.WORKFLOWUSR) AS WFUser,
EMP.SERVICE AS "EMPLOYEE^SERVICE", EMP.COMPANY AS "EMPLOYEE^COMPANY", EMP.EMPLOYEE AS "EMPLOYEE^EMPLOYEE", REQ.SERVICE AS "REQUESTER^SERVICE", REQ.REQUESTER AS "REQUESTER^REQUESTER",
VEN.SERVICE AS "VENDOR^SERVICE", VEN.VENDOR_GROUP AS "VENDOR^VENDOR_GROUP", VEN.VENDOR AS "VENDOR^VENDOR"
FROM LAWSON.ZSRESOURCE RES
LEFT OUTER JOIN LAWSON.ZSCUSTATTR ATTR
ON RES.ZSUID = ATTR.ZSUID
LEFT OUTER JOIN (
SELECT ZSUID, Trim(ATTR_VALUE) AS GLS
FROM LAWSON.ZSCUSTATR2
WHERE ATTR_NAME = 'GLStructure'
) GLS ON RES.ZSUID = GLS.ZSUID
LEFT OUTER JOIN (
SELECT ZSUID, Trim(ATTR_VALUE) AS HRS
FROM LAWSON.ZSCUSTATR2
WHERE ATTR_NAME = 'HRStructure'
) HRS ON RES.ZSUID = HRS.ZSUID
INNER JOIN (
SELECT ZSUID, LTrim(SYS_CONNECT_BY_PATH(ZSROLE, ','),',') AS ROLE_LIST
FROM (
SELECT ZSUID, Trim(ZSROLE) ZSROLE, Row_Number() OVER (PARTITION BY ZSUID ORDER BY ZSUID, ZSROLE) ROWNUMBER
FROM LAWSON.ZSLDAPRES
)
WHERE CONNECT_BY_ISLEAF = 1
START WITH ROWNUMBER = 1
CONNECT BY ZSUID = PRIOR ZSUID
AND ROWNUMBER = (PRIOR ROWNUMBER + 1)
ORDER SIBLINGS BY ZSUID, ZSROLE
) ZROLES ON RES.ZSUID = ZROLES.ZSUID
INNER JOIN (
SELECT ZSUID, LTrim(SYS_CONNECT_BY_PATH(ZSGROUP, ','),',') AS GROUP_LIST
FROM (
SELECT ZSUID, Trim(ATTR_VALUE) ZSGROUP, Row_Number() OVER (PARTITION BY ZSUID ORDER BY ZSUID, ATTR_VALUE) ROWNUMBER
FROM LAWSON.ZSRESATTR
WHERE ATTR_NAME = 'Group'
)
WHERE CONNECT_BY_ISLEAF = 1
START WITH ROWNUMBER = 1
CONNECT BY ZSUID = PRIOR ZSUID
AND ROWNUMBER = (PRIOR ROWNUMBER + 1)
ORDER SIBLINGS BY ZSUID, ZSGROUP
) ZGROUPS ON RES.ZSUID = ZGROUPS.ZSUID
LEFT OUTER JOIN (
SELECT ZSUID, Trim(ZSIDENTITY) AS SERVICE, Trim(ZSVALUE) AS REQUESTER
FROM LAWSON.ZSLDAPIDEN
WHERE ZSIDENTITY = 'PROD_REQUESTER'
) REQ ON RES.ZSUID = REQ.ZSUID
LEFT OUTER JOIN (
SELECT * FROM (
SELECT ZSUID, ZSIDENTITY SERVICE, ZSFIELD, Trim(ZSVALUE) AS ZSVALUE FROM LAWSON.ZSLDAPIDEN
WHERE ZSIDENTITY = 'PROD_EMPLOYEE'
)
PIVOT
( Max(ZSVALUE) FOR ZSFIELD IN ('COMPANY' AS COMPANY, 'EMPLOYEE' AS EMPLOYEE) )
) EMP ON RES.ZSUID = EMP.ZSUID
LEFT OUTER JOIN (
SELECT * FROM (
SELECT ZSUID, ZSIDENTITY SERVICE, ZSFIELD, Trim(ZSVALUE) AS ZSVALUE FROM LAWSON.ZSLDAPIDEN
WHERE ZSIDENTITY = 'PROD_VENDOR'
)
PIVOT
( Max(ZSVALUE) FOR ZSFIELD IN ('VENDOR_GROUP' AS VENDOR_GROUP, 'VENDOR' AS VENDOR) )
) VEN ON RES.ZSUID = VEN.ZSUID
LEFT OUTER JOIN (
SELECT * FROM (
SELECT ZSUID, ZSIDENTITY SERVICE, ZSFIELD, Trim(ZSVALUE) AS ZSVALUE FROM LAWSON.ZSLDAPIDEN
WHERE ZSIDENTITY = 'PROD_CUSTOMER'
)
PIVOT
( Max(ZSVALUE) FOR ZSFIELD IN ('CUSTOMER_GROUP' AS CUSTOMER_GROUP, 'CUSTOMER' AS CUSTOMER) )
) CUST ON RES.ZSUID = CUST.ZSUID
LEFT OUTER JOIN (
SELECT * FROM (
SELECT ZSUID, ZSIDENTITY SERVICE, ZSFIELD, Trim(ZSVALUE) AS ZSVALUE FROM LAWSON.ZSLDAPIDEN
WHERE ZSIDENTITY = 'PROD'
)
PIVOT
( Max(ZSVALUE) FOR ZSFIELD IN ('LOGIN' AS OSID, 'PASSWORD' AS OSPASSWORD) )
) OS ON RES.ZSUID = OS.ZSUID
LEFT OUTER JOIN (
SELECT ZSUID, Trim(ZSVALUE) AS RMID
FROM LAWSON.ZSLDAPIDEN
WHERE ZSIDENTITY = 'SSOP'
) SSOP ON RES.ZSUID = SSOP.ZSUID
WHERE RES.ZSUID IN (
SELECT ZSUID FROM LAWSON.ZSRESOURCE
WHERE ZSUID = 'jdoe'
)
Using this report, the subsequent Perl script, and the Lawson provided loaduser, we can produce an EXACT copy of a user from production to test in about 2 minutes. A copy from Prod to Prod takes a little more, as there is some time spent editing the file (username, employee, etc). Still, how does 2 minutes compare to YOUR user “copy” process?
HTH
What I learned today 
