medvmwadm
Enthusiast
Enthusiast

How to retrieve list of all named users

Hello:

This may sound simple but so far I have not been able to determine how to do this.  As the subject says, we are wanting to retrieve the list of named users in our Horizon environment.  So, under the Licensing section we have the total named user count.  We now wish to download that list of unique users.  I have even looked through the PowerCLI documentation and so far do not see how to do this.  

Does anybody have a simple way or know of how to do this?  Thank you in advance.

0 Kudos
4 Replies
Mickeybyte
Enthusiast
Enthusiast

@medvmwadm 

You could do this by querying the event database and retrieve all unique usernames that logged in or reconnected to any session within a given time frame. 

The following query should give you the list of usernames and their last connection date/time from September

SELECT strValue AS NamedUser, MAX(Time) AS LastConnected
FROM event_historical INNER JOIN event_data_historical ON event_historical.EventID = event_data_historical.EventID
WHERE event_data_historical.Name = 'UserDisplayName' 
  AND (event_historical.EventType = 'AGENT_CONNECTED' OR event_historical.EventType = 'AGENT_RECONNECTED')
  AND Time BETWEEN '2021-09-01 00:00:00' AND '2021-10-01 00:00:00'
GROUP BY strValue

 You can remove the TIME clause if you don't want to limit on date/time.

There could be a slight difference between the returned number of users and the actual Named Users shown on the license page. On my LAB setup, the Named Users show 7 and my query only returns 4 results. I haven't been able to test it on larger environments. Let me know if the difference is too big, then I can have a deeper look into it.

Regards,
Mickeybyte
0 Kudos
medvmwadm
Enthusiast
Enthusiast

Mickeybyte,

Unfortunately, our environment is fairly strict and we don't have direct SQL access to the DB.  I was hoping there was something in Horizon specifically but thank you for this, we may end up having to look at something like this if we can't find another solution.

We also have vROPS and was able to export a list there, but it's vastly different from the total named user count in Horizon.  It's been 10 months since we've reset our named user count in Horizon, however I'm seeing a 33% difference which seems odd.

0 Kudos
Mickeybyte
Enthusiast
Enthusiast

@medvmwadm 

Since Horizon version 2106, you can retrieve events using the API. @Magneet has written a blog post about it recently: [API]New way to gather Horizon Events – Retouw.nl Maybe that can help you?

 

Regards,
Mickeybyte
0 Kudos
medvmwadm
Enthusiast
Enthusiast

Mickeybyte,

Thanks for the info on 2106.  We're still on Horizon 7.13 here.  But that might give us more reasons to upgrade at some point.

 

0 Kudos