Use Excel to compile server usage

Posted on 2012-09-11
Medium Priority
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

acseven earned 1000 total points
ID: 38388878
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

ID: 38388881
It was missing the attachment.
LVL 10

Accepted Solution

SANTABABY earned 1000 total points
ID: 38388893
Please see the attached workbook. I added a Pivot table in the report sheet. It is very easy to use. Hope this helps.
LVL 26

Expert Comment

ID: 38388943
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

ID: 38437694
Pivot table was the abosult right answer.

thanks all

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

850 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