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.
Hannes ZernerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John TsioumprisSoftware & Systems EngineerCommented:
I think some sample data with a mini guide would be most helpful...
0
Hannes ZernerAuthor Commented:
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.
0
John TsioumprisSoftware & Systems EngineerCommented:
Check the attached database...query1 & query5 should give you the desired results...
HZ.accdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Hannes ZernerAuthor Commented:
This is a good starting point. Thank you.
0
John TsioumprisSoftware & Systems EngineerCommented:
If you study the queries you will see that it can also do analysis per month...
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
auditing

From novice to tech pro — start learning today.