I'm not much of an SQL expert; however, does anyone know the SQL query command of obtaining a client's IP address. I thought the following command will show the IP address but it doesn't
select * from VDI_EVENTS.dbo.vdievent_historical
The client IP address is not recorded in the View Events DB for some reason... I have been wanting this feature for a very long time, it seems pretty basic and something a VDI administrator would want to know.
There is a column in the dbo.event and dbo.event_hostorical tables called "EndpointId" but I have never seen it populated from View 5.0 through 7.3.2 (might need other products?). You can't even see the client's IP in Horizon administrators console, that only shows (in the Sessions tab) the MAC address of a Zero Client otherwise it just shows a random string that doesn't seem to correlate to any thing. To my knowledge the only place the client's IP is set is in the "Volatile Environment" registry in the HKCU registry hive while some one is connected.
So pretty sure you have to write a custom logon script to pull the data from the registry then write it to a custom database or file.
The client IP address is in syslog event logging. You can either log it to a syslog server or to a file.
you can read it within the vdesktop it self from the registry key, if there is away to reflect hat in horizon it will be goodapproche
A different approach other than SQL. PowerCLI. If it's helpful this should help you get the IP's of all Horizon machines which you can then use for whatever you need.
You'll need the newest version of PowerCLI and VMWare.HV.Helper module:
You'll need to establish a connection to your vSphere(connect-viserver) environment and Horizon (connect-hvserver) environment first then:
$hvmachines = (Get-HVMachine).base
$machines = $hvmachines.name | foreach {get-vm -name $_}
$machines | foreach{$_.extensiondata} |
select @{Name='Machine';Expression={$_.name}},@{Name='IP';Expression={$_.guest.net.ipaddress}}
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
In our environment 'ClientIPAddress' just shows our F5 load balancer and I don't have any records for 'Forwarded Client Address' (is that in Horizon Enterprise only?).
If a client connects directly to the connection server then that client's IP address will show under 'ClientIPAddress'. If a client connects through a load balancer then one of the floating IPs will show under 'ClientIPAddress' and the actual client IP address will show under 'ForwardedClientIPAddress' as long as the load balancer is configured to insert the 'X-Forwarded-For' header. If 'X-Forwarded-For' isn't configured then the value will be NULL.
If 'X-Forwarded-For' is enabled then the connection server will see something similar to this:
2018-03-23T05:22:33.365-05:00 DEBUG (0D88-1148) <Thread-35> [SimpleAJPService] (ajp:broker:Request5712720) Request from /10.205.1.82: POST /broker/xml
2018-03-23T05:22:33.365-05:00 TRACE (0D88-1148) <Thread-35> [SimpleAJPService] (ajp:broker:Request5712720) Content-Type: application/x-www-form-urlencoded
2018-03-23T05:22:33.365-05:00 TRACE (0D88-1148) <Thread-35> [r] (ajp:broker:Request5712720) Header: host: [virtual.example.org]
2018-03-23T05:22:33.365-05:00 TRACE (0D88-1148) <Thread-35> [r] (ajp:broker:Request5712720) Header: user-agent: [VMware-client]
2018-03-23T05:22:33.365-05:00 TRACE (0D88-1148) <Thread-35> [r] (ajp:broker:Request5712720) Header: accept: [*/*]
2018-03-23T05:22:33.365-05:00 TRACE (0D88-1148) <Thread-35> [r] (ajp:broker:Request5712720) Header: cookie: []
2018-03-23T05:22:33.365-05:00 TRACE (0D88-1148) <Thread-35> [r] (ajp:broker:Request5712720) Header: content-length: [179]
2018-03-23T05:22:33.365-05:00 TRACE (0D88-1148) <Thread-35> [r] (ajp:broker:Request5712720) Header: content-type: [application/x-www-form-urlencoded]
2018-03-23T05:22:33.365-05:00 TRACE (0D88-1148) <Thread-35> [r] (ajp:broker:Request5712720) Header: x-forwarded-for: [10.95.2.59]
2018-03-23T05:22:33.365-05:00 TRACE (0D88-1148) <Thread-35> [SimpleAJPService] (ajp:broker:Request5712720) Forcing content type for XML API request to: text/xml
2018-03-23T05:22:33.365-05:00 TRACE (0D88-1148) <Thread-35> [r] (ajp:broker:Request5712720) Header: content-type: [text/xml]
2018-03-23T05:22:33.365-05:00 TRACE (0D88-1148) <Thread-35> [r] (ajp:broker:Request5712720) Header: vdmconnectionsource: [VkRJQ09OTkkwMS53aHJzZC5uZXQ=]
2018-03-23T05:22:33.365-05:00 TRACE (0D88-1148) <Thread-35> [r] (ajp:broker:Request5712720) Header: gateway-type: [SG-cohosted]
2018-03-23T05:22:33.365-05:00 TRACE (0D88-1148) <Thread-35> [r] (ajp:broker:Request5712720) Header: gateway-location: [Internal]
2018-03-23T05:22:33.365-05:00 DEBUG (0D88-1148) <Thread-35> [SimpleAJPService] (ajp:broker:Request5712720) Gateway headers sent to the broker:
2018-03-23T05:22:33.365-05:00 DEBUG (0D88-1148) <Thread-35> [SimpleAJPService] (ajp:broker:Request5712720) gateway-type = [SG-cohosted]
2018-03-23T05:22:33.365-05:00 DEBUG (0D88-1148) <Thread-35> [SimpleAJPService] (ajp:broker:Request5712720) gateway-location = [Internal]
2018-03-23T05:22:33.365-05:00 TRACE (0D88-1148) <Thread-35> [SimpleAJPService] (ajp:broker:Request5712720) Request task queued.
The POST to the connection server will be from an IP address belonging to the load balancer. In the data being posted, the load balancer will add the additional 'x-forwarded-for' header containing the source client ip address. In this example the load balancer is 10.205.1.82 and the client is 10.95.2.59.
You mentioned you are using an F5 in your environment. Are you using the F5 Horizon iApp template? Did you configure SSL bridging or SSL offloading? If you go to your http service profiles under 'Local Traffic' -> 'Profiles' is 'X-Forwarded-For' enabled?
Be aware that the UAG currently do not support the X-Forwarded-For (XFF) header and "ForwardedClientIPAddress" will show the IP of the loadbalancer (Unless you only have a single UAG, then it works). We are told that engineering is working on this and a future release will support it.