PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 01/10/2017 3:53 PM by  Brian Allen
NT Username to Real Username
 6 Replies
Sort:
You are not authorized to post a reply.
Author Messages
pops
Systems Analyst
Private
Advanced Member
(85 points)
Advanced Member
Posts:39


Send Message:

--
01/09/2017 3:34 PM

    We are running Lawson 10.0.7.  I am working on a SQL query to identify some journal entries out of the GLTRANS table and I need to be able to show the real username instead of the operator that begins with NT00....  I cannot seem to find any SQL table that I can join to in order to provide this information in the results of the query.  Does anybody have any idea?

     

    Thank you in advance and Happy New Year!!

     

    Allen

    JimY
    Private
    Private
    Veteran Member
    (1089 points)
    Veteran Member
    Posts:389


    Send Message:

    --
    01/10/2017 6:39 AM
    Unfortunately there isn't one. I had to run the listusermap command with the -a option, output it to a file and then massage it to load into a table. I ended up creating a Perl script and flow to do this. The other problem is if it is an old user that is no longer around their user id may not show up.
    Joan Herzfeldt
    System Analyst
    Genesis Health System
    Veteran Member
    (134 points)
    Veteran Member
    Posts:50


    Send Message:

    --
    01/10/2017 8:22 AM
    Hi pops - Our DBA created a crosswalk table (probably similar to what JimY did) that I can access via sql or Crystal. I'll ask him about it and post again.
    We actually published a crosswalk report on our super users dashboard so they could figure out who did what.
    Randy
    Systems Administrator
    Rochester Regional Health
    Advanced Member
    (85 points)
    Advanced Member
    Posts:31


    Send Message:

    --
    01/10/2017 8:33 AM
    Pretty much the same as JimY. We use an IPA nightly to dump (listusermap) a flat file we then read into a custom table. We also use an HR feed to setup a second custom table of all active employee's. Both tables contain the User ID so I can generate a report of NT ID, User ID, name , email ect.
    eah
    Lawson Admin
    Canal Barge Company
    New Member
    (7 points)
    New Member
    Posts:3


    Send Message:

    --
    01/10/2017 9:02 AM
    Allen, were doing the same using listusermap, but I did open an incident and was told in ENV 10.0.9 there will a fix to display the username on windows. We'll see....
    Joan Herzfeldt
    System Analyst
    Genesis Health System
    Veteran Member
    (134 points)
    Veteran Member
    Posts:50


    Send Message:

    --
    01/10/2017 10:14 AM
    Thank you to our DBA, Jason Barman... here are some details about how he accomplished this for us.
    In order to convert from NT_ID to username/userid, we need to combine info from GEN and the Lawson Security LDAP repository. You can utilize whatever tools you have/are comfortable with to accomplish this. I'm using SQL Server Integrations Services (SSIS) as my basic framework.

    1) Query into GEN to pull the UID and NT_ID
    SELECT CONVERT(INT, CONVERT(VARBINARY, Replace([USERNAME], 'NT', '0x'), 1)) AS
    [UID],
    [USERNAME] AS
    [NT_ID]
    FROM dbo.USERINFO
    ORDER BY [UID]

    2) Query into Lawson Security LDAP on your LSF server to pull USERNAME, UID and EMPLOYEE

    I wrote a C# script to use as a datasource in SSIS. I utilized the SearchRequest class from System.DirectoryServices.Protocols. Hopefully, you're familiar with querying LDAP, as it's entirely different. If not, there's good examples available online. I used the Microsoft documentation. Your LDAP schema is most likely a bit different from ours, so you'll need to modify the targetDN accodingly. Here's what I used, which should get you to the right place.

    targetDN = "CN=lsfprod,OU=svcxref,O=lwsnSecData,O=lwsn,DC=genesishealth";
    ldapSearchFilter = "(cn=*)";
    string[] attributesToReturn = new string[] { "lwsnssoAllAttrValueList", "lwsnssoListOfIDs" };

    You'll find USERNAME and UID in the lwsnssoAllAttrValueList attribute. It's a multivalue attribute and the sub-attributes aren't necessarily always in the same order for every record, so you'll have to parse each record to make sure you get eveything sorted out properly.

    lwsnssoAllAttrValueList.DOMAIN_USER --> USERNAME
    lwsnssoAllAttrValueList.UID --> RAWUID

    You can get EMPLOYEE from the [0] instance of lwsnssoListOfIDs:

    lwsnssoListOfIDs[0] --> EMPLOYEE

    3) Clean up the LDAP data

    You'll need to clean up the LDAP query results. UID and USERNAME need to be formatted a bit. I do the following in a Derived column transformation in SSIS:

    UID is: (DT_I4)TOKEN(RAWUID,":",2)
    USERNAME is: TOKENCOUNT(USERNAME,"\\") == 2 ? (TOKEN(TOKEN(USERNAME,":",2),"\\",2)) : (TOKEN(USERNAME,":",2))

    4) Join the LDAP query results to the GEN query results based on UID.

    I'm taking the results of this and loading it into a table in LAWSON to be used as a 'crosswalk' table that can be joined to in order to convert NT_ID to username and/or employee_id. This table is refreshed daily, but you could refresh it as often as you need.
    Brian Allen
    Private
    Private
    Veteran Member
    (276 points)
    Veteran Member
    Posts:94


    Send Message:

    --
    01/10/2017 3:53 PM
    I just ran across this info on a lookup tool that was released that may be helpful to some.

    Description:
    Infor has provided a new tool to look up the friendly domain\username format from the ten character NT id (e.g. NT00000004). This is a browser-based tool delivered via Infor Lawson for Ming.le and is available in LSF 10.0.5 or above. To get this tool, install JTs: 828174, 832914, and 835723. See the PDF attachment for visual detail.



    https://www.inforxtreme.com/espubli...archResult
    You are not authorized to post a reply.