VMware Horizon Community
natixis_it
Enthusiast
Enthusiast

View DB SQL how to extract Client's IP address

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

8 Replies
danflynn
Contributor
Contributor

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.

Reply
0 Kudos
BenFB
Virtuoso
Virtuoso

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

Reply
0 Kudos
HussamRabaya
VMware Employee
VMware Employee

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

Reply
0 Kudos
jrodsguitar
Enthusiast
Enthusiast

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 · GitH...

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

Blog: https://powershell.house/
Reply
0 Kudos
SteveWH
Enthusiast
Enthusiast

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

danflynn
Contributor
Contributor

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

Reply
0 Kudos
SteveWH
Enthusiast
Enthusiast

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?

Reply
0 Kudos
BenFB
Virtuoso
Virtuoso

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.