topic Re: View DB SQL how to extract Client's IP address in Horizon Desktops and Apps

View DB SQL how to extract Client's IP address

natixis_it — Thu, 14 Sep 2017 16:11:50 GMT

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

Re: View DB SQL how to extract Client's IP address

danflynn — Wed, 21 Feb 2018 19:03:39 GMT

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.

Re: View DB SQL how to extract Client's IP address

BenFB — Thu, 22 Feb 2018 00:29:35 GMT

The client IP address is in syslog event logging. You can either log it to a syslog server or to a file.

Re: View DB SQL how to extract Client's IP address

HussamRabaya — Thu, 22 Feb 2018 04:22:23 GMT

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

Re: View DB SQL how to extract Client's IP address

jrodsguitar — Thu, 22 Feb 2018 20:20:59 GMT

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:

PowerCLI-Example-Scripts/Modules/VMware.Hv.Helper at master · vmware/PowerCLI-Example-Scripts · GitHub

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}}

Re: View DB SQL how to extract Client's IP address

SteveWH — Sat, 24 Feb 2018 15:12:45 GMT

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

Re: View DB SQL how to extract Client's IP address

danflynn — Fri, 23 Mar 2018 19:41:16 GMT

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?).

Re: View DB SQL how to extract Client's IP address

SteveWH — Fri, 23 Mar 2018 21:02:46 GMT

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?

Re: View DB SQL how to extract Client's IP address

BenFB — Fri, 23 Mar 2018 22:57:54 GMT

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.