asked on

Use Excel to compile server usage

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.
Server-Count.xls

SOLUTION

acseven

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

acseven

It was missing the attachment.
Server-Count.xlsx

ASKER CERTIFIED SOLUTION

SANTABABY

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Steven Carnahan

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.
Copy-of-Server-Count.xls

epmmis

ASKER

Pivot table was the abosult right answer.

thanks all