The client IP address is recorded in the Horizon Event database. You need to select records from the 'event' table that have 'EventType' of 'BROKER_USERLOGGEDIN'. You then inner join the 'event_data' table on 'EventID'.
Example:
select *
from event e
inner join event_data ed
on e.eventid = ed.eventid
where e.eventtype = 'BROKER_USERLOGGEDIN'
order by e.time desc
When you execute the query you will notice multiple rows are returned for each login event. Each row contains a different attribute related to the login: 'ForwardedClientIpAddress', 'UserSID', 'ClientIpAddress', 'BrokerSessionId', 'UserDisplayName', and 'TotalUsers'. If you want to transpose rows to columns so data is returned in one row per login event you will need to pivot.
Example:
select *
from (
select
e.time as "Time",
e.node as "Connection Server",
ed.name,
ed.strvalue
from event e
inner join event_data ed
on e.eventID = ed.eventID
where e.eventtype = 'BROKER_USERLOGGEDIN'
) t
PIVOT
(
MAX(strvalue)
FOR name in ("ClientIPaddress", "ForwardedClientIPaddress", "UserDisplayname")
) p
order by time desc
It's important to note that the 'event' and 'event_data' tables only contain recent data. If you want to include older data in your query you need to union the historical event data.
Example:
select *
from (
select
e.time as "Time",
e.node as "Connection Server",
ed.name,
ed.strvalue
from event e
inner join event_data ed
on e.eventID = ed.eventID
where e.eventtype = 'BROKER_USERLOGGEDIN'
) t
PIVOT
(
MAX(strvalue)
FOR name in ("ClientIPaddress", "ForwardedClientIPaddress", "UserDisplayname")
) p
union
select *
from (
select
eh.time as "Time",
eh.node as "Connection Server",
edh.name,
edh.strvalue
from event_historical eh
inner join event_data_historical edh
on eh.eventID = edh.eventID
where eh.eventtype = 'BROKER_USERLOGGEDIN'
) t
PIVOT
(
MAX(strvalue)
FOR name in ("ClientIPaddress", "ForwardedClientIPaddress", "UserDisplayname")
) p
order by time desc
