Use Excel to compile server usage

Posted on 2012-09-11
Last Modified: 2012-09-26
There are 6 citrix servers in my farm. A vbs script is used to store who is logged on to each citrix server.  Every hour at the 15 the poll is done.  Below is an example of the info captured. The date, time, login and server name is captured.

10-Sep      13:15      User10      SERVER4
10-Sep      13:15      User10      SERVER5
10-Sep      13:15      User1      SERVER4
10-Sep      13:15      User1      SERVER5
10-Sep      14:15      User9      SERVER3
10-Sep      14:15      User8      SERVER4
10-Sep      14:15      User7      SERVER5
10-Sep      14:15      User6      SERVER1
10-Sep      14:15      User6      SERVER4
10-Sep      14:15      User16      SERVER5
10-Sep      14:15      User6      SERVER2
10-Sep      14:15      User11      SERVER6
10-Sep      14:15      User12      SERVER6
10-Sep      14:15      User5      SERVER1

I would like to know how many users are connected to each server.  Using the sample above at 13:15 2 users are connected to SERVER4 and 2 users are connected to SERVER 5.
A user count is needed for each polling time.  An excel spreadsheet is attached with 24 hours worth of data.

The ultimate goal is to be able to create an excel chart showing hourly how many users are logged on to each server.  

I am open to easier ways of getting hourly user counts on each server.
Question by:epmmis
    LVL 11

    Assisted Solution

    I believe creating a pivot table solves what you need, see the example attached.
    I used the new tables of Excel 2007+ and a pivot table to get that data separated.
    LVL 11

    Expert Comment

    It was missing the attachment.
    LVL 10

    Accepted Solution

    Please see the attached workbook. I added a Pivot table in the report sheet. It is very easy to use. Hope this helps.
    LVL 25

    Expert Comment

    I am not very good with pivot tables so I was working from a different angle.  I did not complete it but just a start to give some idea.  Please see attached.

    Author Closing Comment

    Pivot table was the abosult right answer.

    thanks all

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
    My experience with Windows 10 over a one year period and suggestions for smooth operation
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now