Get the groups of a customeruser in otrs

I am extending OTRS with an app and need to get the groups a customeruser is in. I want to do this by communicating with the SessionGet-Endpoint (https://doc.otrs.com/doc/api/otrs/6.0/Perl/Kernel/GenericInterface/Operation/Session/SessionGet.pm.html)

The Endpoint for SessionGet returns a lot of information about the user but not the groups he is in. I am not talking about agents who can login to the backend of otrs but customerusers.

I am using OTRS 6 because it was the only one available in docker. I created the REST-endpoints in the backend and everything works well. There is a new functionality why I need to get the information about the groups.

Had a look at the otrs system-config but could not figure out if it is possible to include this information in the response.

Solution

Although I am a programmer, I did not want to write perl because of ... reasons.

I had a look at the file which handles the incoming request at /opt/otrs/Kernel/GenericInterface/Operation/Session/SessionGet.pm and traced the calls to the actual file where the information is collected from the database in /opt/otrs/Kernel/System/AuthSession/DB.pm. In line 169 the SQL-statement is written so it came to my mind that I just can extend this to also get the information of the groups, because, as I said, I did not want to write perl...

A typical response from this endpoint looks like this:

{
    "SessionData": [
        {
            "Value": "2",
            "Key": "ChangeBy"
        },
        {
            "Value": "2019-06-26 13:43:18",
            "Key": "ChangeTime"
        },
        {
            "Value": "2",
            "Key": "CreateBy"
        },
        {
            "Value": "2019-06-26 13:43:18",
            "Key": "CreateTime"
        },
        {
            "Value": "XXX",
            "Key": "CustomerCompanyCity"
        },
        {
            "Value": "",
            "Key": "CustomerCompanyComment"
        }
        ...
}

A good thing would be to just insert another Value-Key-pair with the IDs of the groups. The SQL-statement queries only one table $Self->{SessionTable} mostly called otrs.sessions.

I used the following resources to create a SQL-statement which extends the existing SQL-statement with the needed information. You can find it here:

$DBObject->Prepare(
        SQL => "
            (
                SELECT id, data_key, data_value, serialized FROM $Self->{SessionTable} WHERE session_id = ? ORDER BY id ASC
            ) 
            UNION ALL
            (
                SELECT 
                (
                    SELECT MAX(id) FROM $Self->{SessionTable} WHERE session_id = ?
                ) +1 
                AS id,
                'UserGroupsID' AS data_key,
                (
                    SELECT GROUP_CONCAT(DISTINCT group_id SEPARATOR ', ') 
                    FROM otrs.group_customer_user 
                    WHERE user_id = 
                    (
                        SELECT data_value 
                        FROM $Self->{SessionTable} 
                        WHERE session_id = ? 
                        AND data_key = 'UserID' 
                        ORDER BY id ASC
                    )
                )
                AS data_value,
                0 AS serialized
            )",
        Bind => [ \$Param{SessionID}, \$Param{SessionID}, \$Param{SessionID} ],
    );

Whoever needs to get the groups of a customeruser can replace the existing code with the one provided. At least in my case it works very well. Now, I get the expected key-value-pair:

{
    "Value": "10, 11, 6, 7, 8, 9",
    "Key": "UserGroupsID"
},

I used the following resources:

Happy coding,

Nico