Link to home
Start Free TrialLog in
Avatar of Hannes Zerner
Hannes Zerner

asked on

Data Macro to analyze Audit trail information

Hey,

I am looking for a Macro / DB Structure, where I can import a predefined raw data format which includes start and stop dates for individual User entries being enabled for various services (including Hierarchies of Services).

This Data base is supposed to provide me Usage Statistics per month and if applicable applying the hierarchies.

Can somebody point me to the right direction / area where I could find something for this?

Thank you.
Avatar of John Tsioumpris
John Tsioumpris
Flag of Greece image

I think some sample data with a mini guide would be most helpful...
Avatar of Hannes Zerner
Hannes Zerner

ASKER

Columns would be something like:

User ID ; Service subscribed ; Start Date of Service ; End Date of Service
1234; Service 1 ; 01/01/2010 ; 12/31/2011
1234; Service 2 ; 01/01/2010 ; 12/31/2011
4321; Service 1 ; 01/01/2010 ; 12/31/2011
4321; Service 2 ; 01/01/2010 ; 12/31/2010

In this instance, service 1 is included in Service 1 and if for the same period both services are assigned per user, only the higher one is supposed to be counted.

For this example the following is the expected output
For each of the months between 1/1/2010-12/31/2010 I am expecting Output 2 Users for Service 2
For each of the months between 1/1/2011-12/31/2011 I am expecting Output 1 User for Service 1 and 1 User for Service 2.

In general it should be a relatively simple counting mechanism, but I am uncertain about the hierarchies.
ASKER CERTIFIED SOLUTION
Avatar of John Tsioumpris
John Tsioumpris
Flag of Greece image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
This is a good starting point. Thank you.
If you study the queries you will see that it can also do analysis per month...