• State Suggested Answer
  • Replies 6 replies
  • Answers 1 answer
  • Subscribers 95 subscribers
  • Views 435 views
  • Users 0 members are here
Options
Related

Creating a report / Maximal member count of a Group (ADSGroup, AADGroup) in last month

Paul
5 months ago

Hi there, is there an OOTB functionality to set a report of the historical group memberships?

We have customers who receive a monthly invoice. The quantities are calculated based on the memberships in AD or AAD groups, specifically the maximum number from the previous month. Is there a table where I can get such data to use it in the report? 

This is a possible solution: We could create new fields in ADSGroup and AADGroup like and "Max this month" and "Max last month" and count the memberships on every change in ADSAccountInADSGroup for this group. Negative point is, that we need more processes. We could have problems, when the processes terminate without a result.

But could we do it more sophisticated? 

Best regards, Paul

Parents
  • 0 5 months ago

    Hi Paul,

    You could enable timetrace on the ADSAccountInADSGroup and AADUserinGroup tables, these are the 'linking' tables where memberships / assignments to groups are stored. When you have timetrace turned on you could make a query to 'find' the historical number of people assigned to the group.

    However... unless there are SQL guru's far smarter than I the queries for this would still become quite complicated, although it's easy to see the removals from the group to find the highest number in a month you'd still have quite a few complicated 'queries' to make to find a day at which there was a 'maximum'.

    Personally I would lean towards an 'onsaving' script on the ADSAccountInADSGroup and AADUserInGroup tables so that whenever there are changes made to these tables a small script runs to update a field where you store the maximum memberships. This way you won't need a process but everytime the table gets updated you'd update the number.

Reply
  • 0 5 months ago

    Hi Paul,

    You could enable timetrace on the ADSAccountInADSGroup and AADUserinGroup tables, these are the 'linking' tables where memberships / assignments to groups are stored. When you have timetrace turned on you could make a query to 'find' the historical number of people assigned to the group.

    However... unless there are SQL guru's far smarter than I the queries for this would still become quite complicated, although it's easy to see the removals from the group to find the highest number in a month you'd still have quite a few complicated 'queries' to make to find a day at which there was a 'maximum'.

    Personally I would lean towards an 'onsaving' script on the ADSAccountInADSGroup and AADUserInGroup tables so that whenever there are changes made to these tables a small script runs to update a field where you store the maximum memberships. This way you won't need a process but everytime the table gets updated you'd update the number.

Children