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.
* auditingMicrosoft AccessMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
John Tsioumpris

8/22/2022 - Mon
John Tsioumpris

I think some sample data with a mini guide would be most helpful...
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
John Tsioumpris

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Hannes Zerner

ASKER
This is a good starting point. Thank you.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
John Tsioumpris

If you study the queries you will see that it can also do analysis per month...