online agents - DB query

[lliu]

In the dashboard there is an area "Online Agents/Customers" showing who is currently logged in to the system. How do I query this info directly from MySQL database? I couldn't find tables containing current login users. I am using OTRS 3.0.6.

Thanks.

[crythias]

it's buried in sessions. there's a session id and a field with all key-value pairs that are (as far as I can tell) base64 encoded.
Read up on Kernel/Output/HTML/DashboardUserOnline.pm

Code: Select all

        my @Sessions = $Self->{SessionObject}->GetAllSessionIDs();
        for (@Sessions) {
            my %Data = $Self->{SessionObject}->GetSessionIDData( SessionID => $_ );

and
Kernel/System/AuthSession/DB.pm

Code: Select all

    # read data
    $Self->{DBObject}->Prepare(
        SQL => "SELECT $Self->{SQLSessionTableValue} FROM "
            . " $Self->{SQLSessionTable} WHERE $Self->{SQLSessionTableID} = ?",
        Bind => [ \$Param{SessionID} ],
    );
    while ( my @Row = $Self->{DBObject}->FetchrowArray() ) {
        $Strg = $Row[0];
    }

    # split data <-- read below here, because this splits the second field into data parts.

Code: Select all

sub GetAllSessionIDs { #<-- where the session IDs originate
    my ( $Self, %Param ) = @_;

    # read data
    my @SessionIDs;
    return if !$Self->{DBObject}->Prepare(
        SQL => "SELECT $Self->{SQLSessionTableID} FROM $Self->{SQLSessionTable}",
    );
    while ( my @Row = $Self->{DBObject}->FetchrowArray() ) {
        push @SessionIDs, $Row[0];
    }
    return @SessionIDs;
}

[lliu]

Thanks Crythias. Is there a way to run mySQL query and decode the data, to show user login/out time?

[crythias]

Thanks Crythias. Is there a way to run mySQL query and decode the data, to show user login/out time?

Not particularly. It only shows you sessions that exist. It's *possible* that you might know when a user has logged in, but logged out would destroy the session entry.