SQL

Automating LBI Reporting Services rights

Lawson Business Intelligence (LBI) Reporting Services (RS) offers a lot of great functionality through the concept of “rights”. Rights allow you to do bursting and variable prompts within Crystal Reports. We use this functionality quite a bit for all kinds of things such as creating a right with an employee’s company and employee number so that they only see their own information or loading Activity codes for a project manager and setting that as a prompt so they can select from a list of their activities vs having to type in a cryptic code. This is important to us because we tend to use ODBC rather than DME, so we can’t use the inherent Lawson security that DME offers.

The big downside to using rights is that they’re difficult to load. As of now (LBI 10.4), you can either create the right manually or you can create a file and load it manually. I dislike both of those options because they both involve the word “manual”. We have an enhancement request in to create web calls to allow us to update rights, but it doesn’t exist yet. Consequently, if I really want to automate the loading of rights to LBI, I’m going to have to break the rules.

PLEASE NOTE THAT WHAT I AM ABOUT TO DISCUSS IS NOT SUPPORTED BY LAWSON/INFOR (OR ME). IF YOU CHOOSE TO IMPLEMENT THIS, YOU ARE ON YOUR OWN. YOU HAVE BEEN WARNED.

We have chosen to update the RS rights tables in LBI directly using SQL. You can choose any manner of methodologies to do the updates, but my preference is using TX as part of a process flow to do the updates. We do a full compare between what is currently in Lawson and what is in LBI and then perform the updates accordingly.
In order to add rights to LBI, you need to perform four table updates in the following order (these tables should be in your LawsonRS database):

  1. ERS_RULEMASTER – build the right framework based on an existing structure
  2. ERS_RULEDETAIL – build the filters e.g. how the elements match
  3. ERS_RULEVALUES – set the filter values
  4. ERS_RULEMAPPINGS – Associate the user to the right

If you are deleting rights, you should perform the updates in the reverse order as above.

In the below examples, I will be creating a right for my id called EMPLOYEE. The structure has two elements: COMPANY and EMPLOYEE which correlate to the primary key for the Lawson EMPLOYEE table and will help me to limit HR records for just the person running the report. I’ve chosen to show an example with two elements so that you can see the harder version. It should be easy to to either scale it up or down depending on your needs. These examples are for SQL Server.

--ERS_RULEMASTER
INSERT INTO [LawsonRS].[dbo].[ERS_RULEMASTER]
           ([RULEID]
           ,[RULENAME]
           ,[RULEDESCRIPTION]
           ,[STRUCTUREID]
           ,[RULEOWNER]
           ,[STARTDATE]
           ,[ENDDATE]
           ,[STATUS]
           ,[ALLOWPARTIAL]
           ,[RULEORDER])
     VALUES
           (867
           ,'EMPLOYEE-12345'
           ,'Employee Filter'
           ,11
           ,'APPSUPPORT'
           ,CURRENT_TIMESTAMP
           ,'2080-08-14 04:00:00.000'
           ,'A'
           ,'Y'
           ,0)
GO

--ERS_RULEDETAIL
INSERT INTO [LawsonRS].[dbo].[ERS_RULEDETAIL]
           ([RULEID]
           ,[ELEMENTID]
           ,[RULEGROUPING]
           ,[STRUCTUREID]
           ,[OPERAND]
           ,[ELEMENTVALUE2]
           ,[EXCLUDEINCLUDE]
           ,[ELEMENTORDER])
     VALUES
           (867
           ,1
           ,1
           ,11
           ,'equal to'
           ,null
           ,'i'
           ,1),
           (867
           ,8
           ,1
           ,11
           ,'equal to'
           ,null
           ,'i'
           ,2)
GO

--ERS_RULEVALUES

INSERT INTO [LawsonRS].[dbo].[ERS_RULEVALUES]
           ([RULEID]
           ,[ELEMENTID]
           ,[RULEGROUPING]
           ,[STRUCTUREID]
           ,[SEQUENCEID]
           ,[ELEMENTVALUE1])
     VALUES
           (867
           ,1
           ,1
           ,11
           ,1
           ,'1'), --company number
           (867
           ,8
           ,1
           ,11
           ,1
           ,'12345') --employee id
GO


--ERS_RULEMAPPINGS
INSERT INTO [LawsonRS].[dbo].[ERS_RULEMAPPINGS]
           ([CONSUMERID]
           ,[RULEID]
           ,[CONSUMERTYPE])
     VALUES
           ('myid'
           ,867
           ,1)
GO

Obviously there are some hard-coded values in here that you’re going to have to figure out. First things first, let’s figure out the RULEID that we’re going to create. You will need to add 1 to the value returned by this query.

SELECT MAX(RULEID) AS LASTRULEID 
FROM [LawsonRS].[dbo].[ERS_RULEMASTER]

Next we need to get the Structure ID for the EMPLOYEE structure that we’re updating. You should be able to retrieve this by checking the ERS_STRUCTURE table.

SELECT STUCTUREID 
FROM [LawsonRS].[dbo].[ERS_STRUCTURE]
WHERE STRUCTURENAME = 'EMPLOYEE'

Finally, we need to get the Element IDs for the elements of the structure we updating/creating. This is in the ERS_ELEMENTS table.

SELECT ELEMENTID 
FROM [LawsonRS].[dbo].[ERS_ELEMENTS]
WHERE ELEMENTNAME = 'EMPLOYEE'

Finally, here is a sample query on existing rights for you to compare your Lawson data to to create the updates.

SELECT MAP.CONSUMERID, RM.RULENAME, S.STRUCTUREID, S.STRUCTURENAME,  E.ELEMENTID, 
    E.ELEMENTNAME, RD.OPERAND, RV.ELEMENTVALUE1, RD.RULEGROUPING, RD.ELEMENTORDER,
    RM.RULEOWNER, RM.STARTDATE, RM.ENDDATE
FROM ERS_RULEMASTER AS RM
    INNER JOIN ERS_RULEDETAIL AS RD
        ON RM.RULEID = RD.RULEID
    INNER JOIN ERS_RULEVALUES AS RV
        ON RM.RULEID = RV.RULEID
        AND RD.ELEMENTID = RV.ELEMENTID
        AND RD.RULEGROUPING = RV.RULEGROUPING
        AND RD.STRUCTUREID = RV.STRUCTUREID
    INNER JOIN ERS_STRUCTURE S
        ON RD.STRUCTUREID = S.STRUCTUREID
    INNER JOIN ERS_ELEMENTS E
        ON RD.ELEMENTID = E.ELEMENTID
    INNER JOIN ERS_RULEMAPPINGS MAP
        ON RD.RULEID = MAP.RULEID
WHERE S.STRUCTURENAME = 'EMPLOYEE'
ORDER BY MAP.CONSUMERID

So there you have it. The ability to automate the loading of rights into Lawson LBI Reporting Services.

HTH

Advertisements

Lawson Security Reporting

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

Processflow JDBC

After a lot of frustration trying to get the SQL nodes to work on various versions of Lawson System Foundation, I’m posting what we’ve learned so far. There seems to be a lot of misinformation out there, so here’s what we have working.

On LSF9, for SQL Server, you can only use the SQL 2000 drivers (at least in our experience). In 9.0.1, the 2005 and 2008 drivers seem to work fine. Oracle works the same in both versions.

SQL Server 2000:
JDBC Driver: com.microsoft.jdbc.sqlserver.SQLServerDriver
URL: jdbc:microsoft:sqlserver://<server>;DatabaseName=<DBName>
Jar files: msbase.jar, mssqlserver.jar, msutil.jar

SQL Server 1.2/2.0
JDBC Driver: com.microsoft.sqlserver.jdbc.SQLServerDriver
URL: jdbc:sqlserver://<server>;DatabaseName=<DBName>
Jar files: sqljdbc4.jar

Oracle
JDBC Driver: oracle.jdbc.driver.OracleDriver
URL: jdbc:oracle:thin:@<server>:<port>:<SID>
Jar files: classes12.jar, classes12.zip, ojdbc6.jar*

* The correct ojdbc.jar file depends on your version. This is the jar file for Oracle 11g. Oracle 10g/9i (I’m not sure) uses ojdbc14.jar

To run on the server, you must add the appropriate jar files in the $GENDIR/bpm/jar directory (or %GENDIR%/bpm/jar if you’re on Windows).

To run on your machine, it depends. Using the “old” version of process flow designer, you need to make sure that the jar files are on your local machine and you’ve added the jar files to the CLASSPATH in the designer.bat file.

It should look something like this (for SQL server 2005/2008):
SET CLASSPATH=.;.\lib\secLS.jar;.\lib\bpmsso.jar;.\lib\MvxAPI.jar;.\lib\bpm-clients.jar;.\lib\bpm-commons.jar;.\lib\activation.jar;.\lib\collections.jar;.\lib\jakarta-oro-2.0.jar;.\lib\jbcl.jar;.\lib\jcfield450K.jar;.\lib\js.jar;.\lib\xbean.jar;.\lib\mailapi.jar;.\lib\mercjava.jar;.\lib\pf-xmljava.jar;.\lib\pf-rel802.jar;.\lib\SheridanActvList.zip;.\lib\smtp.jar;.\lib\xercesImpl.jar;.\lib\xml-apis.jar;.\lib\lawson-httpclient.jar;C:\JDBC\sqljdbc4.jar

For the 9.0.1 Designer version, you need to add the appropriate jars to the “External Jar” section.
Window>Preferences…>”External Jars”
Click “New…” and select the appropriate jar file.

HTH

LBI: Smart Notification queries

Have you ever worked in Smart Notes and just HATED life? This happens to me quite a bit, but mostly because the stupid User Picker is in no particular order. Another pain point is that I can’t see everything that another user is subscribed to. Here’s how I deal with it…

How to figure out what a user is subscribed to and lessen the pain of finding the user in User Picker.

--Get list of subscribed items for user 
SELECT ACS.ACCESSID as Userid, ENTRYSTRING as SN_Name, ENTRYDESC as Description
FROM ENPBATREE TREE 
INNER JOIN ENPENTRYACCESS ACS 
ON TREE.TREEENTRYID = ACS.TREEENTRYID 
WHERE ACS.ACCESSID IN ( 
  SELECT USERID 
  FROM ENPUSERMAP 
  WHERE USERNAME = 'jdoe') 

The result looks something like:

SN_Name Description
FIN_PeriodClosed Period Closed for company
FIN_Accounting Unit Changes Accounting Unit Changes
--Get list of groups for user 
SELECT U.USERNAME as GROUP
FROM ENPUSERMAP U 
INNER JOIN ENPGROUPMAP G 
ON U.USERID = G.GROUPID 
WHERE G.USERID IN ( 
  SELECT USERID 
  FROM ENPUSERMAP 
  WHERE USERNAME = 'jdoe') 

The result is something like this:
GROUP
Finance
SUPERLUSER
LBI AP

If your organization is anything like mine, with several hundred groups, at least you can use the IE “find” function to quickly find your group. With 50 per page, it isn’t so awful…

(Note: While I recognize that EFS/ERS has a “Manage User” screen, this is of limited help sometimes in SN. As of 9.0.3.1, Smart Notes synchronizes independently of EFS. Unfortunately, SN syncs the ENTIRE LDAP into the tables, regardless of what EFS has. We have EFS set to only sync a group called LBIUSER where we add all LBI users. We only have about 3000 users in this group so the sync is several minutes, but not unwieldy. Smart Notes synchronization on the other hand, takes over two hours on a good day. If we run it during busy periods it can bring down the system. Therefore we only sync once a week. Hence why I need to figure out SN groups separately.)

HTH

Lawson: Elapsed Job Time

I work in support. Quite frankly, sometimes the users are their own worst enemies. It’s hard to convince them of this, but every so often it’s possible. If you’re proactive about things and you play your cards right, they might not even mind you telling them this.

One of the best indicators of “something can be better” is long running jobs. There are some jobs that are just going to take forever (I’m looking at you PO135), but there are other jobs where the run time will vary widely depending on the parameters. If your users tend to run jobs wide open, it can impact your system performance and their satisfaction because they have to wade through pages of reports that they don’t want.

My solution to this is to do a review of elapsed job times. I like to do this in the context of specific programs by user. I try to check for a specific program from a user that runs longer than the others. Usually this indicates an issue. Sometimes it’s legit, but if you see the same username (especially if it’s the same jobname) in the same elapsed time range, you’ve got a problem.

The question is, how do you identify these situations?

For those of you familiar with LID, the “jobschd” command runs off the GEN QUEUEDJOB table. The different screens are just different selections over the same table. In the example below, we look only at “Completed” jobs. To do our comparison we do something like this:

(Apologies to SQL server or DB2 readers, this query is as Oracle specific as they get – hopefully the context makes sense. As a decoding reference: Lawson stores the and time as a numeric which means no leading zeroes. You will have to parse the time into time formats, inserting leading 0’s when appropriate).

SELECT TOKEN, USERNAME, JOBNAME, JOBQUEUE, ACTSTARTDATE, 
 SUBSTR(REPLACE(LPAD(' ',6-LENGTH(ACTSTARTTIME))||ACTSTARTTIME,' ','0'),1,2)||':'|| 
    SUBSTR(REPLACE(LPAD(' ',6-LENGTH(ACTSTARTTIME))||ACTSTARTTIME,' ','0'),3,2)||':'|| 
    SUBSTR(REPLACE(LPAD(' ',6-LENGTH(ACTSTARTTIME))||ACTSTARTTIME,' ','0'),5,2) STARTTIME, 
    STOPDATE, 
  SUBSTR(REPLACE(LPAD(' ',6-LENGTH(STOPTIME))||STOPTIME,' ','0'),1,2)||':'|| 
    SUBSTR(REPLACE(LPAD(' ',6-LENGTH(STOPTIME))||STOPTIME,' ','0'),3,2)||':'|| 
    SUBSTR(REPLACE(LPAD(' ',6-LENGTH(STOPTIME))||STOPTIME,' ','0'),5,2) AS ENDTIME, 
  TO_TIMESTAMP(STOPDATE||SUBSTR(REPLACE(LPAD(' ',6-LENGTH(STOPTIME))||STOPTIME,' ','0'),1,2)||':'|| 
    SUBSTR(REPLACE(LPAD(' ',6-LENGTH(STOPTIME))||STOPTIME,' ','0'),3,2)||':'|| 
    SUBSTR(REPLACE(LPAD(' ',6-LENGTH(STOPTIME))||STOPTIME,' ','0'),5,2),'DD-MON-YYHH24:MI:SS') - 
  TO_TIMESTAMP(ACTSTARTDATE||SUBSTR(REPLACE(LPAD(' ',6-LENGTH(ACTSTARTTIME))||ACTSTARTTIME,' ','0'),1,2)||':'|| 
    SUBSTR(REPLACE(LPAD(' ',6-LENGTH(ACTSTARTTIME))||ACTSTARTTIME,' ','0'),3,2)||':'|| 
    SUBSTR(REPLACE(LPAD(' ',6-LENGTH(ACTSTARTTIME))||ACTSTARTTIME,' ','0'),5,2),'DD-MON-YYHH24:MI:SS') ELAPSED 
FROM QUEUEDJOB 
WHERE ACTSTARTDATE BETWEEN '01-OCT-10' AND '31-OCT-10'
  AND QJBSET6_SS_SW = 'N'

The result is something like this:

TOKEN USERNAME JOBNAME JOBQUEUE ACTSTARTDATE STARTTIME STOPDATE ENDTIME ELAPSED
RQ500 lawson TEST RQ 7-Oct-10 16:34:08 7-Oct-10 16:34:16 +000:00:08.00
PR197 batch PR197CHP PR 7-Oct-10 16:39:57 7-Oct-10 17:42:20 +001:02:23.00
GL292 user1 BALSHEET ********** 7-Oct-10 17:05:25 7-Oct-10 17:05:27 +000:00:02.00
GL292 user1 BALSHEET ********** 7-Oct-10 17:06:17 7-Oct-10 17:06:18 +000:00:01.00

I would love to tell you that there’s an easy sort to this, but frankly you need to dump to Excel or Lotus so it’s easier to see where your issues are. I usually start by sorting on ELAPSED descending. If there’s nothing that really jumps out, then I sort by TOKEN and by ELAPSED descending. Then if I hit a big jump between #1 and #2 for a token, we may have issues.

Because I’m including the job queue, it’s also easy to see when users are “jumping the queue”. We’ve recently completed the switch from LAUA security to LS9 security. Sometimes it’s not as dialed in as we need it to be and this is a great identifier. For us, each system has it’s own job queue and no one should ever use ****. I can tell right away that we have an issue with “user1”.

The other big gotcha about this technique is that if the job went into ‘Needs Recovery’ the elapsed time will reflect that, which isn’t necessarily correct.

HTH

Lawson – Posting Accounting Units for a parent

Given the structure that Lawson stores the accounting information (both accounts and accounting units), it can be difficult to retrieve data because of the parent/child structure. This post is a corollary to the post on the Org Chart here. This technique is for Oracle and uses a Hierarchical query.

The business case is that we need to be able to retrieve all Accounting Unit’s for a given Summary Accounting Unit. This makes it easier to run reports because we don’t have to expect the users to type in a list of Accounting Units. My recommendation is to create either a view or stored procedure for this so it can be used by many users in their reports without having to recreate it for each report.

SELECT COMPANY, ACCT_UNIT, DESCRIPTION, ACTIVE_STATUS, LEVEL_DEPTH
FROM (
  SELECT COMPANY, ACCT_UNIT, DESCRIPTION, ACTIVE_STATUS, POSTING_FLAG, LEVEL_DEPTH,
    OBJ_ID, PARENT_OBJ_ID, LEVEL
  FROM LAWSON.GLNAMES
  CONNECT BY PRIOR OBJ_ID = PARENT_OBJ_ID
  START WITH ACCT_UNIT = 'INFOSVCS')
WHERE POSTING_FLAG = 'P'

This will return all posting Accounting Units that are a child of INFOSVCS, regardless of what level they’re at.

COMPANY ACCT_UNIT DESCRIPTION ACTIVE_STATUS LEVEL_DEPTH
1 123456 IS Application Services A 3
1 987654 IS Operations A 4

As you can see, we’re using the CONNECT BY PRIOR statement to loop through the results and join the PARENT_OBJ_ID to the previous record’s OBJ_ID. I’m using an inline view for the hierarchical query results and then filtering for only Posting Accounting Units. Because Oracle will apply the filter while selecting records, they will not be available to be put into the hierarchy, so it must be done afterward.

HTH

Edit: Comments have been disabled due to spam

ImageNow license monitoring

Today is going to be a bonus day of sorts.  This post is specifically going to be about reporting on ImageNow licensing, but if you read carefully, there may be a few hidden gems that you can use for other things.

It’s budget time for us right now.  To me that doesn’t mean a whole lot, except for the fact that my boss (and director) are going to start asking for license usage.  Perceptive has done a great job of giving us an overview of the licensing within the ImageNow Server Administrator (ISA).  What about if you’re not on the server?  What if someone else wants to know the licensing situation, but they don’t have access to the server?  (For those of you who submitted the idea of making ISA available via WebNow, I upvoted it).

Here’s a little bit of SQL (SQL Server version) that will more or less give you the ISA Current usage License view for your ImageNow client and WebNow license usage.  I’ve excluded everything else because they shouldn’t be an issue.

--Report of connected users
SELECT USR.USR_NAME, USR.USR_FIRST_NAME, USR.USR_LAST_NAME,
  CASE WHEN LIC.LIC_TYPE = 0
    THEN 'CLIENT' ELSE 'WEB'
  END AS LIC_TYPE,
  LIC.COMPUTER_NAME,
  LIC.LAST_UPDATE,
  DATEDIFF("n",LIC.LOGIN_TIME,GETUTCDATE()) AS ACTIVE_TIME,
  CASE WHEN LIC.IS_ACTIVE = 1
    THEN 'ACTIVE' ELSE 'INACTIVE'
  END AS STATUS
FROM IN_LIC_MON LIC
INNER JOIN IN_USR USR
ON LIC.USR_ID = USR.USR_ID
WHERE LIC.LIC_TYPE IN (0,9)
ORDER BY LIC.LIC_TYPE, USR_NAME

The output looks like (depending on your db version):

USR_NAME USR_FIRST_NAME USR_LAST_NAME LIC_TYPE COMPUTER_NAME LAST_UPDATE ACTIVE_TIME STATUS
jdoe1 Jane Doe CLIENT GW310GHB1 2010-08-27 18:32:02.0 422 ACTIVE
adoe Amy Doe CLIENT GWGX17HB1 2010-08-27 18:01:35.0 92 ACTIVE
...
ldoe2 Lawrence Doe WEB UH45Y4JK1 2010-08-27 18:08:28.0 25 ACTIVE


The intent is that you would put this into a Crystal report and share on your BI dashboard.

**Note that all dates/times stored in the ImageNow DB are UTC.  In order to get an accurate representation of the logged in time, you have to do a UTC comparison.  For other db users, this field/calculation (including datediff) should be the only thing you have to change.   I’m also not including combo licenses because we don’t have any, so I don’t know the license number.  If anyone does and can share, I’d be more than happy to update the SQL.

I sure some of you are saying, “But Jim, that’s nice if I want to see what it is right now, but what about trends?”.  Okay, so basically we do something similar and capture it to a file.   Personally, I have a script that runs every 5 minutes and captures the output to a flat file that we can then review.

Here’s the SQL for it (SQL Server version again):

--Graph of used licenses by type 
SELECT GETDATE(), 
CASE TMP.LIC_TYPE 
  WHEN 0 
    THEN REPLICATE('C',TMP.LIC_USED_COUNT)+'&nbsp;'+CAST(TMP.LIC_USED_COUNT AS VARCHAR)+'/'+CAST(TMP.LIC_COUNT AS VARCHAR)
  WHEN 9 
    THEN REPLICATE('W',TMP.LIC_USED_COUNT)+'&nbsp;'+CAST(TMP.LIC_USED_COUNT AS VARCHAR)+'/'+CAST(TMP.LIC_COUNT AS VARCHAR)
  ELSE REPLICATE('X',TMP.LIC_COUNT) 
END AS GRAPH 
FROM ( 
  SELECT MON.LIC_TYPE, 
    COUNT(MON.USR_ID) LIC_USED_COUNT, 
    LIC.LIC_COUNT 
  FROM IN_LIC_MON MON 
  INNER JOIN 
    (SELECT TYPE3, SUM(NUM_USRS) LIC_COUNT 
    FROM IN_LIC LIC 
    GROUP BY TYPE3) AS LIC 
  ON MON.LIC_TYPE = LIC.TYPE3 
  GROUP BY MON.LIC_TYPE, LIC.LIC_COUNT) TMP

The output looks like:

2010-08-27 15:05:13.097 CCCCCCCCCCCCCCCCCCCCCCCC 24/101 
2010-08-27 15:05:13.097 WWWWWWWWWWW 11/79 
2010-08-27 15:05:13.097 XX 
2010-08-27 15:10:39.47  CCCCCCCCCCCCCCCCCCCCC 21/101 
2010-08-27 15:10:39.47  WWWWWWWWWWWWW 13/79 
2010-08-27 15:10:39.47  XX
...
2010-08-27 17:10:39.47  CCCCCCCCCCC 11/101 
2010-08-27 17:10:39.47  WWWWWWWWWWWWWWWWWWWW 20/79 
2010-08-27 17:10:39.47  XX

Notice the graph that it’s producing?  This makes it easy to spot when you have peaks (and how much your server is actually being used after hours).  This is one of the gems.  You don’t have to dump data and put it into Excel or Access or whatever to “see” your data.  Other db folks will have to convert the replicate function, but as far as I’m aware, all the ImageNow supported db’s have this capability.  Just make sure you view it in a fixed width font. You may also want to consider outputting your license types to different files.

For the final bit, here’s a fully functional set of Iscript code that is very similar to the script I  run.  I have a windows scheduler task that runs it every 5 minutes.

// ------------------------------------------------------------------- 
// Function:    licUsageGraph 
// Purpose:     record license usage 
// ------------------------------------------------------------------- 

#link  
#include  

//Declare constants 
#define LOG_DIR         "e:\\inserver6\\log\\"                  //logfile directory 

// Set and open logfiles 
var doclogfile = LOG_DIR + "licensing_" + getDateString("FileName") + ".log"; 
logfile = Clib.fopen(doclogfile, "a"); 
licfile = Clib.fopen(LOG_DIR + "licenseusage.txt", "a"); 


function main() 
{ 
    //Create db connection 
    var db = new Database(); 
    var err = db.connect("ODBC","ImageNow 6","user","password"); 
    if (err != 0){logging("Could not connect to db",logfile);return false;} 
    var strSQL = "SELECT CAST(GETDATE() AS VARCHAR) AS DATETIME,  " + 
        "CASE TMP.LIC_TYPE " + 
        "    WHEN 0 THEN REPLICATE('C',TMP.LIC_USED_COUNT)+' '+CAST(TMP.LIC_USED_COUNT AS VARCHAR)+'/'+CAST(TMP.LIC_COUNT AS VARCHAR) " +
        "    WHEN 9 THEN REPLICATE('W',TMP.LIC_USED_COUNT)+' '+CAST(TMP.LIC_USED_COUNT AS VARCHAR)+'/'+CAST(TMP.LIC_COUNT AS VARCHAR) " +
        "    ELSE REPLICATE('X',TMP.LIC_COUNT) " + 
        "END AS GRAPH " + 
        "FROM ( " + 
        "  SELECT MON.LIC_TYPE, " + 
        "    COUNT(MON.USR_ID) LIC_USED_COUNT, " + 
        "    LIC.LIC_COUNT " + 
        "   FROM IN_LIC_MON MON " + 
        "  INNER JOIN  " + 
        "    (SELECT TYPE3, SUM(NUM_USRS) LIC_COUNT " + 
        "    FROM IN_LIC LIC " + 
        "    GROUP BY TYPE3) AS LIC" + 
        "  ON MON.LIC_TYPE = LIC.TYPE3 " + 
        "  GROUP BY MON.LIC_TYPE, LIC.LIC_COUNT) TMP" 

    // Open cursor 
    var usrcursor = db.cursor(strSQL); 

    //Check for records in cursor 
    if (!usrcursor.first()) 
    { 
        Clib.fputs("No records returned for SQL " + strSQL + "\n", logfile); 
        return false; 
    } 

    //Get data from cursor -- print first line, then print all others with while loop 
    Clib.fputs(usrcursor.DATETIME + " " + usrcursor.GRAPH + "\n", licfile); 

    while (usrcursor.next()) 
    { 
        Clib.fputs(usrcursor.DATETIME + " " + usrcursor.GRAPH + "\n", licfile); 
    } 

    //Close Cursor 
    usrcursor.close(); 

    if (db.connected()) 
    { 
        var strSQLDXErr = db.disconnect(); 
        if (strSQLDXErr != 0) 
        { 
            Clib.fputs("Could not disconnect from db \n" 
            + "Error is: " + db.majorErrorCode() + "-" + db.majorErrorMessage() + "\n" 
            + db.minorErrorCode() + "-" + db.minorErrorMessage() + "\n",logfile); 
        } 
    } 

    Clib.fclose(licfile); 
    Clib.fclose(logfile); 
}

HTH