We help IT Professionals succeed at work.

Data Macro to analyze Audit trail information

Last Modified: 2017-03-27

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.
Watch Question

John TsioumprisIT Supervisor
Distinguished Expert 2019

I think some sample data with a mini guide would be most helpful...


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.
IT Supervisor
Distinguished Expert 2019
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)


This is a good starting point. Thank you.
John TsioumprisIT Supervisor
Distinguished Expert 2019

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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions