When i first started managing View environments one of the first questions i had was, “who uses it and how often?”
We are talking about view 4.0, no vRealize suite to help us, just Events database which stores all that information but View administrator console does not provide an optimal way to visualize all the data events database can produce.
Sure you can list all events and export these to CSV, but these are limited to a couple thousand and also list a lot of information that we don’t need. In a busy environment those 2000 events can be just half a day information. One might argue the hard coded 2000 limit can be increased in ADAM but this will make your events console very slow and still won’t help us filtering the content.
So what is the best way to do it? Simply query the events database and extract the data!
The below code will help you
$SqlServer='serverHostname.Domain.com'
$dbUser='readonly_Account'
$dbPWD='readonlyPASS'
$domainName='DomainFriendlyName\'
$eventsDB='eventsDB.dbo.evtevent'
$fileout=get-date -Format dd-d-yyy
$fileout+=".csv"
$d=Invoke-Sqlcmd -Database viewevents -Hostname $SqlServer -Username $dbUser -Password $dbPWD -Query "select getdate() -90, getdate()"
$a=$d.Column1
$b=$d.Column2
$query="
With Result as
(select Convert(varchar(10),CONVERT(date,Time,106),103) as Date, replace(ModuleAndEventText,'User ${domainName}', '' ) as Username, DesktopID from ${eventsDB} where
(DesktopID like '%') and
(EventType = 'AGENT_CONNECTED' or EventType = 'AGENT_RECONNECTED') and time
between '${a}' and '${b}')
select distinct Date,ltrim(rtrim(left (Username, CHARINDEX(' ',Username)-1))) as Username,DesktopId as Pool from Result order by 1 desc
"
Invoke-Sqlcmd -Database viewevents -Hostname $SqlServer -Username $dbUser -Password $dbPWD -Query $query
I’ll quickly go over the lines:
1-5 – These are parameters, they will be the same as your Horizon View envionment (or you can create a separate account for this purpose)
$eventsDB holds not only the database but also the event table. In my case i have a prefix of evt so the complete table is eventsDB.dbo.evtevent
I’ve highlighted those as these will need to be changed according to your environment!
The following 2 lines define $fileout, this will be the output filename in case we which to create one.
After we are also getting two dates, the current one and three months from now (this off course can be adjusted to your needs)
Why don’t we get these dates from PowerShell? Well this way we know for sure the output format will be the exact same as the one from the database because it comes from it!
15-14 – This is our actual SQL query. I’m by far not an expert in SQL so please feel free to improve it, I’m sure there is room for it!
We are targeting two things, Pool and EventType
In this case i’m targeting all pools with (DesktopID like '%') but you can certainly adjust it with wildcards and/or using multiple OR queries (as we used for EventType)
As for eventType there are many, we are only focusing on two;
- AGENT_CONNECTED
- AGENT_RECONNECTED
(EventType = 'AGENT_CONNECTED' or EventType = 'AGENT_RECONNECTED') and time
between '${a}' and '${b}') The above will include all the agent connected/reconnected events between the two dates we have queried earlier
The next line is simply to trim the username, so we get a clean report!
Finally the last line is what actually calls the query from any computer to the SQL server.
Note:Invoking SQL cmds remotely will only work with computers that have the SQL powershell CMDLETS installed
This script as it is will dump all the data to the screen, but we can definitely export it to a file or even automate it.
To output to a file just modify the last line (26) to:
Invoke-Sqlcmd -Database viewevents -Hostname $SqlServer -Username $dbUser -Password $dbPWD -Query $query |export-csv .\$fileout -NoTypeInformationHope it helps!