Using Horizon 8 REST API for audit and reporting

By -Roch Norwa

Using Horizon 8 REST API for audit and reporting

Posted on 2023-03-28
Posted in Horizon
1 Comment

When talking about audit and reporting in Horizon 8 first thing that comes up is Event Database obviously. This SQL Server based data repo stores massive amount of interesting information, including but not limited to all the users actions related to their virtual desktops and apps. Every time a new session is connected, or disconnected, or user gets logged off from his desktop, Horizon Agent sends this event data along with additional information to the Connection Server, and from there it lands in one of the database tables and gets stored. This data might come handy in multiple situations. Administrators might be requested to provide this information during authority investigations, forensics, work time and productivity audits, or for any other legal requests.

Over the years, multiple methods of accessing and exporting this data were developed:
1. Event tab for for a Pool view in Horizon Administrator Console

2. Direct SQL Queries like the one for last unique login for all users:

SELECT a.ModuleAndEventText,a.Time FROM event_historical a INNER JOIN (SELECT ModuleAndEventText, MAX(Time) as Time FROM event_historical GROUP BY ModuleAndEventText ) AS b ON a.ModuleAndEventText = b.ModuleAndEventText AND a.Time = b.Time WHERE EventType = ‘Agent_CONNECTED’;

3. PowerCLI Connect-HVEvent and Get-HVEvent cmdlets: https://github.com/vmware/PowerCLI-Example-Scripts/blob/master/Modules/VMware.Hv.Helper/docs/Get-HVEvent.md

4. Horizon View Events Database Utility Fling: https://flings.vmware.com/horizon-view-events-database-export-utility

5. Horizon Events Notifier Fling: https://flings.vmware.com/horizon-event-notifier

6. Finally, since Horizon 8 2106 with two new REST API methods: GET/external/v1/audit-events and GET/external/v1/audit-events/extended-attributes

And the last point is what will be discussed in this article as this is a modern way of accessing data between applications and will help you understand how to integrate Horizon 8 with your other systems using this method. An example might be pulling data from Horizon into your existing SIEM or Service Desk system.

Except navigating the database in Management Studio, a good place to start to understand logic behind this component is official documentation, which outline schemaa, tables and event types: https://docs.vmware.com/en/VMware-Horizon/2212/horizon-console-administration/GUID-27B7E9C6-DEE4-4E0D-BA65-41C5DB06EF0E.html

From the perspective of this article, the most important table was presented below. It outlines audit events for user events that can be used for reporting purposes, including

Event Type	Severity	ModuleAndEventText
AGENT_CONNECTED	INFO	User ${UserDisplayName} has logged in to a new session on machine ${MachineName}
AGENT_DISCONNECTED	INFO	User ${UserDisplayName} has disconnected from machine ${MachineName}
AGENT_ENDED	INFO	User ${UserDisplayName} has logged off machine ${MachineName}
AGENT_PENDING	INFO	The agent running on machine ${MachineName} has accepted an allocated session for user ${UserDisplayName}
AGENT_PENDING_ EXPIRED	WARNING	The pending session on machine ${MachineName} for user ${UserDisplayName} has expired
AGENT_RECONFIGURED	INFO	Machine ${MachineName} has been successfully reconfigured
AGENT_RECONNECTED	INFO	User ${UserDisplayName} has reconnected to machine ${MachineName}
AGENT_RESUME	INFO	The agent on machine ${MachineName} sent a resume message
AGENT_SHUTDOWN	INFO	The agent running on machine ${MachineName} has shut down, this machine will be unavailable
AGENT_STARTUP	INFO	The agent running on machine ${MachineName} has contacted the connection server and sent a startup message
AGENT_SUSPEND	INFO	The agent on machine ${MachineName} sent a suspend message

Table 1: List of events generated by Horizon Agent

If you’re not familiar with REST API, I encourage you to read through this great article from Chris Halstead on TechZone: https://techzone.vmware.com/resource/using-vmware-horizon-server-rest-api

For the purpose of this article, we will use a Swagger-UI instead of Postman to simplify the task. Please remember to user your lab or UAT before executing on any production environment if this is the first time for you.

To get started, we need to access the web interface available on every Horizon Connection Server using the URL: https://cs.yourdomain.yoursuffix/rest/swagger-ui.html
Once there, we need to start with authenticating and getting the authorization token for further API calls. Go to Auth section, and select POST /login method and click “Try It Out” button to provide you Horizon Administrator credentials in JSON format:

Click Execute, and you’ll get you your authorization token. You should copy it and add Bearer in front, so it has this exact format:

Bearer eyJhbGciOiJSUzI1NiJ9.eyJ1c2VyLXNpZCI6IlMtMS01LTIxLTM2Nzc0Mjk3NS0zNDM1OTQ3NDI2LTMyMzExNTQzNzEtMTExNSIsImNsb3VkQWRtaW4iOmZhbHNlLCJicm9rZXIgaWQiOiJIQ1MxIiwidG9rZW4gdHlwZSI6ImFjY2VzcyB0b2tlbiIsInN1YmplY3QiOiJ3c2FkbWluIiwiZG9tYWluIjoidmRlbW8iLCJkb21haW4tdHJ1c3QtdHlwZSI6IlBSSU1BUllfRE9NQUlOIiwidXNlci1sb2NhbGUiOiJlbiIsInNlc3Npb24taWQiOiJlY2IzMDdiYi00MTNjLTQxZDQtYWM3NC0zM2Q4ODYxOTM0MTYiLCJzdWIiOiJ3c2FkbWluIiwiaWF0IjoxNjgwMDM1MDQzLCJqdGkiOiJmMTIwNTZhMC02ZjA5LTRkNWMtOWExMS0xYThiMjkyMzJiNTYiLCJuYmYiOjE2ODAwMzUwNDMsImV4cCI6MTY4MDAzNjg0M30.nm3JreEWOG1j54gBkUeK-d_GctTDtFAWjgUa0rVe5jxmxXf7ILGyWaU7YJVT-cOLlL3l3H9X1-OgTfqinuCfgQiOhnu-lzggw4hzDV1k_8DsDXge3fh25CagOkIn45tF-MU276xv-6P-63j72KoeCOVvMzesEwAf_c2cjsA_-isoq8sxamqxC8JOEF0BH2uc9J3S6JObx7UO7szwr1fVirWz5A9QNRnkPPQEzksvYJ1o8q96CmdHat-yFgE9PrZ7D8KMVbczQLdIpHJq5wp97iK56Y1xl_XqC-8EwsUTYoh_ZnfzIwqV2_6GGjMvUyjvw58WmaOUpxhyx5FgSihSow

Copy the full text (Bearer + token_data) and use it to Authorize to the REST API:

Once done, we’re good to strike some API calls! Scroll down to External section, expand and search for GET /audit-events

By default, without filtering and pagination this API call will return a maximum of 1000 last events. In our case, we want only certain events related to user session connections. For that, we need to build a filter. Use the data from the Event Database to filter on different criteria.

In our case we will use Chain filters (And, Or) which are logical AND/OR operations on more than one of the predefined filters objects with following object schema –

{

“type”: <filter type>,

“filters”: [<filter object>, <filter object>]

}

For example in our case –

{

“type”:”And”,

“filters”: [

{

“type”:”Equals”,

“name”:”module”,

“value”:”Agent”

{

“type”:”Equals”,

“name”:”type”,

“value”:”AGENT_CONNECTED”

}

]

}

Minify and put this filter JSON data into the filter field and click Execute:

As a response you will get event details formatted in JSON:

 {
    "id": 212127,
    "user_id": "S-1-5-21-367742975-3435947426-3231154371-1208",
    "type": "AGENT_CONNECTED",
    "severity": "INFO",
    "module": "Agent",
    "machine_dns_name": "w1021h2master.vdemo.pl",
    "time": 1679586104633,
    "desktop_pool_name": "Windows10",
    "machine_id": "e7186928-be64-424c-bd7d-327b4b794adf",
    "message": "User VDEMO\\wsuser has logged in to a new session on machine Windows-10-21H2-Enterprise"
  }

As you can notice, there are two data types that require further transition: a time value is in timestamp fortmat and should be converted to a date/time format; user_id is in a SID format and should be converted to domain\username or UPN format as you wish. Both can be done using your preferred web application programming language like Java, PHP, Python, C# etc.

You can also extend the filters with additional conditions like time range. For that, a “Between” filter type should be used. Remember to use timestamp format:

{

“type”: ”Between”,

“name”: time,

“fromValue”: 1679586104633,

“toValue”: 1879546154632

}

Pagination

To adjust the API responses to your web application needs Pagination can be used. Following key points needs to be considered though:

Pagination parameters in REST API are enabled on selected List APIs, which is indicated in their respective swagger API reference.
Any Pagination enabled List API will be capable of receiving two optional query params to support pagination – page and size.
If pagination is intended, then BOTH page and size are mandatory parameters.
If either page or size is present, the resulting list will not be paginated.
No assumptions will be made for page defaulting to 1 if only size is present.
page and size has to be positive (greater than 0) numeric literals only (Example /rest/<List API>?page=2&size=10).
There is a limit set on maximum value of size which is 1000.
An optional response header ‘HAS_MORE_RECORDS‘ will be made available by API, to indicate that there are more results available.

To build a use case example, let’s say an API has 100 records when fetched without using any pagination params, then following scenarios are possible with pagination params –

when called with /rest/<List API>?page=1&size=10
- will fetch records from 1 to 10 with response header ‘HAS_MORE_RECORDS’
when called with /rest/<List API>?page=3&size30
- will fetch records from 61 to 90 with response header ‘HAS_MORE_RECORDS’
when called with /rest/<List API>?page=4&size30
- will fetch records from 91 to 100 without response header ‘HAS_MORE_RECORDS’
when called with /rest/<List API>?page=5&size30
- will return HTTP 400 response

This concludes the REST API guide for reporting. If you have any questions or comments give me a shout!

Written by

Roch Norwa

+ posts

Using Horizon 8 REST API for audit and reporting