12 Replies Latest reply on Apr 11, 2019 9:50 AM by sjesse

    Session Count Per Pool Report

    epa80 Enthusiast

      Is anyone aware of a way to create a report, detailing the session count/desktops available of VM pools, on a set time window (say report out ever 3 hours as an example)? We have Log Insight. We have agents deployed to all of our linked clones, agents deployed to our brokers/templates. We also have VROps (although in a bit of a propped up state only, not fully configured yet). It seems that among one of these I should be able to do it. I also flirted with the idea of using the Horizon Toolbox, but, not sure that has e-mail/reporting capabilities.

       

      If it's there in VROps staring me in the face and I just don't see it, please let me know.

       

      Looking for an e-mail to pop out every 3 hours and just say pool 1 = x sessions, y VMs available. pool 2, pool 3, etc. etc

       

      A predecessor of mine had a similar report going on through PowerCLI, but, it's a bit outdated and messy. Just wanted to see what others may be doing for a fresh start.

       

      Thanks.

        • 1. Re: Session Count Per Pool Report
          epa80 Enthusiast

          Let me also add we did find a report template for "Horizon Pool Usage Overview" that sounded perfect. However, whenever it spits out, while it shows all the pools in our Horizon world, it is blank for any session statistics.

          • 2. Re: Session Count Per Pool Report
            Ray_handels Master
            Community WarriorsvExpert

            I have tried to make a decent logging with VRops because we had the exact same question. I googled the interwebz like a madman, tried the forum but to no avail. We eventually just dumped vROPS and might look into it when it is half a decent product for Horizon. Having 15 tabs doesn't mean you get the correct information.

            I was able to get the correct information out of it though. You need to create the report on the actual Horizon View pools, not using the reporting itself. But it then spits out the current usage which, to me, sonds quite useless. You could possibly create a report every 3 hours but we liked to have a status from a few months back. Also, there is no way to use ODBC connection to the database and it is not supoorted to even do that (as per VMware Support).

             

            We eventuallt decided to do this ourselves. We have script writing information into a database every 5 or 10 minutes and use SQL Reporting server to spit out the reports. This way we can keep track of highest logon rate per day per pool and stuff like that.

            If you ever find a tool that is capable of just that please let me know. We also did try VMTurbo but weren;t able to get the info out of it that we needed..

            • 3. Re: Session Count Per Pool Report
              beutlern Enthusiast

              Create a super metric on the pool object in vROPS.

              I have no idea anymore about the details but this gave me a perfectly fine report (historical recording as well).

              • 4. Re: Session Count Per Pool Report
                TvdT Lurker

                Dear epa80,

                 

                I also had the problem that I could not determine the number of sessions per pool and time after the Horizon Toolbox seems no longer to be developed since Horizon 7.5.

                 

                So I wrote a script that I execute each hour by scheduled task on one connection broker per pod.

                It works outstanding with VMware Horizon 7.5.1, Powershell 4 and Horizon Power-CLI on a Windows 2008 R2 Server.

                 

                It logs for each pool the following fields into a csv file:

                   

                CountNameDatumZeit
                67Pool 00101.04.201911:00:00
                243Pool 00501.04.201911:00:00
                122Pool 01401.04.201911:00:00
                83Pool 00101.04.201912:00:00
                252Pool 00501.04.201912:00:00
                124Pool 01401.04.201912:00:00

                 

                Each new month, it starts a new csv file.

                 

                With a pivot table it is easy to get the maximum per day and pool or to see the workload over the day.

                 

                Here's the script (also attached):

                 

                Add-PSsnapin VMware.View.Broker

                 

                #Set the connection broker to check. This isn't used since the commands are run locally

                $ConnectionServer = "localhost"

                 

                #For loggin create a timestamp

                $Datum = Get-Date -format dd.MM.yyyy

                $Zeit  = Get-Date -format H:mm:00

                $Pfad = "C:\Service\PowerCLI-Skripte\Active-Sessions\log\active-sessions-"

                $Pfad += Get-Date -format yyyy-MM

                $Pfad += "-POD1.csv"

                Get-RemoteSession | Select-Object pool_id | Group-Object -Property pool_id | Select-Object Count, Name, @{'Name'='Datum';'Expression'={$Datum}} , @{'Name'='Zeit';'Expression'={$Zeit}}| Export-CSV -Path $Pfad -Delimiter ";" -NoTypeInformation -append

                 

                Sorry for:

                - the script is for "German environment"

                - the concatenation of the variable $Pfad is suboptimal

                 

                Hope this helps

                 

                Tim

                • 5. Re: Session Count Per Pool Report
                  BenFB Expert

                  Ray_handels ,

                   

                  Querying the SQL database directly is fully supported and documented at a high-level.

                   

                  Sample Database Queries and Views

                   

                  I've been looking into ways to pull this information based on the logs sent to syslog. It seems doable if you have a way way to write/visualize queries.

                  • 6. Re: Session Count Per Pool Report
                    Ray_handels Master
                    vExpertCommunity Warriors

                    Thanks BenFB but that was not what we were looking for. Yes I know I can query the events database, we do so already. What I was aiming at here was querying the vRops database.

                    We have tried and install it again after all that time. To be honest the information that it spits out is much better than older version (we installed version 6.6.1) but yet again the entire thing just crashed after a few weeks. I am able to use ControlUp also, this works much better to be honest but unfortunately we cannot afford that as we already have vRops licenses.

                     

                    I could well be that this is due to my limited knowledge of vRops. We are trying to get someone to help us with it.. We'll see what happens in near future

                    • 7. Re: Session Count Per Pool Report
                      sjesse Master
                      vExpert

                      I'd like them to confirm it, but I don't think vrops stores anything more than user counts historically, nothing specific about the users. Its been awhile but I'm pretty sure I grilled them on this a few years ago and they said they didn't track all historical sessions because its too much to track for vrops,due to the data included for the sessions objects. The only way I've seen is scripts like csv files for user counts and the sql database before.

                      • 8. Re: Session Count Per Pool Report
                        sjesse Master
                        vExpert

                        This isn't a complete script but this can get detailed session data

                         

                        https://www.retouw.nl/powercli/pulling-horizon-session-information-using-powercli/

                         

                        and look at GitHub - vCheckReport/vCheck-HorizonView: vCheck for Horizon View

                         

                         

                        which does show this information(I hid the name of the pool.), plus more.

                         

                        • 9. Re: Session Count Per Pool Report
                          BenFB Expert

                          Oops, I missed that you were talking about vRops.

                          • 10. Re: Session Count Per Pool Report
                            mchadwick19 Enthusiast

                            I know you mention using vROps, which still seems to be not so great at some of the overtime metrics that seem to be requested by managers of VDI projects. Why not use the View API which seems to be great in these later releases?

                             

                            I did this previously with a couple PowerCLI scripts that I scheduled. First was one that would run for an hour every hour collecting the pool metric I think every 30 seconds - 1 minute and exporting them to a CSV. Then a second script would run daily and roll-up the files and spit out the max, min, and average per pool out of the days csv's. Of course doing some file management along with that as well.

                            • 11. Re: Session Count Per Pool Report
                              BenFB Expert

                              mchadwick19 Are you able to share these and any other Horizon scripts that you are using?

                              • 12. Re: Session Count Per Pool Report
                                sjesse Master
                                vExpert

                                I have one that I need to improve, if anyone wants to use if for inspiration.  The first one basically gets all the sessions, and outputs them to an csv. the second one gets the max count average count and emails it out. I have the first one running every 15 minutes and the other 1 running once a month. That lets me get a general idea on the average number non persistent desktops I need and what kinda spikes we can expect. The only thing is you need the hv.helper module PowerCLI-Example-Scripts/Modules/VMware.Hv.Helper at master · vmware/PowerCLI-Example-Scripts · GitHub . I pulled out my specifics and put place holders, if anyone has any questions on what goes where let me know.

                                 

                                #Get Sessions script

                                ===============================

                                 

                                Import-Module VMware.VimAutomation.HorizonView

                                Import-Module VMware.Hv.Helper

                                Connect-HVServer -Server <horizon server> -User  <horizon user> -Password <horizon password> -Domain <horizon domain>

                                 

                                 

                                 

                                $sessions=(get-hvlocalsession).namesdata

                                $sessionsList=@()

                                $desktopPools=@()

                                $rdsFarms=@()

                                foreach($session in $sessions)

                                {

                                    $desktopPools+=$session.DesktopName

                                    $rdsFarms+=$session.FarmName

                                }

                                 

                                 

                                $desktopPools = $desktopPools | select -Unique

                                $rdsFarms= $rdsFarms | select -Unique

                                 

                                 

                                foreach($desktopPool in $desktopPools)

                                {

                                    $count=0

                                    foreach ($session in $sessions)

                                    {

                                        if($session.DesktopName -eq $desktopPool)

                                        {

                                            $count++

                                        }

                                    }

                                    $obj = new-object PSObject

                                    $obj | Add-Member -MemberType NoteProperty -Name "Time" -Value $(get-date).ToString()

                                    $obj | add-member -membertype NoteProperty -name "Name" -value $desktopPool

                                    $obj | add-member -membertype NoteProperty -name "Concurrent Users" -value $count

                                    $sessionsList += $obj

                                 

                                 

                                }

                                 

                                 

                                foreach($rdsFarm in $rdsFarms)

                                {

                                    $count=0

                                    foreach ($session in $sessions)

                                    {

                                        if($session.FarmName -eq $rdsFarm)

                                        {

                                            $count++

                                        }

                                    }

                                    $obj = new-object PSObject

                                    $obj | Add-Member -MemberType NoteProperty -Name "Time" -Value $(get-date).ToString()

                                    $obj | add-member -membertype NoteProperty -name "Name" -value $rdsFarm

                                    $obj | add-member -membertype NoteProperty -name "Concurrent Users" -value $count

                                    $sessionsList += $obj

                                 

                                 

                                }

                                 

                                 

                                $sessionsList | Export-csv -NoTypeInformation -Append C:\Scripts\conncurentuserreport\sessions.csv

                                 

                                #count sessions

                                ==================================

                                $conncurrentUserCounts=Import-csv C:\Scripts\conncurentuserreport\sessions.csv

                                $vdiPools=@()

                                $connUserReport=@()

                                foreach ($userCount in $conncurrentUserCounts)

                                {

                                    $vdiPools+=$userCount.Name

                                }

                                 

                                 

                                $vdiPools=$vdiPools | select -Unique

                                 

                                 

                                foreach($vdiPool in $vdiPools)

                                {

                                    $max=0;

                                    $avg=0;

                                    $count=0

                                    $sum=0

                                    foreach($userCount in $conncurrentUserCounts)

                                    {

                                 

                                 

                                        if($userCount.Name -eq $vdiPool)

                                        {

                                            $num=[int]$userCount.'Concurrent Users'

                                            if($num -gt $max)

                                            {

                                                $max=$num

                                            }

                                            $sum+=$num

                                            $count++

                                        }

                                    }

                                    $avg=$sum/$count

                                    $obj = new-object PSObject

                                    $obj | Add-Member -MemberType NoteProperty -Name "Name" -Value $vdiPool

                                    $obj | Add-Member -MemberType NoteProperty -Name "Average Concurrent Users" -Value $avg

                                    $obj | Add-Member -MemberType NoteProperty -Name "Max Concurrent Users" -Value $max

                                    $connUserReport+=$obj

                                }

                                 

                                 

                                $connUserReport | Export-csv -NoTypeInformation C:\Scripts\conncurentuserreport\connCurrentUserReport.csv

                                 

                                 

                                Send-MailMessage -Attachments "C:\Scripts\conncurentuserreport\sessions.csv","C:\Scripts\conncurentuserreport\connCurrentUserReport.csv"  -SmtpServer $smtpserver -From $fromaddress -Subject "Concurrent User Report" -to $toaddress

                                 

                                Remove-Item C:\Scripts\conncurentuserreport\sessions.csv

                                Remove-Item C:\Scripts\conncurentuserreport\connCurrentUserReport.csv