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

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.

Hi Paul,

alternately you may create a new field at table ADSGroup (i.g. <fieldOfYourOwn>). Create process having a single step of type "Execute SQL" at object ADSDomain.

Set as "SQLStmt" parameter value something like this (replace <fieldOfYourOwn> with the fieldname of new field) :

Dim f As ISqlFormatter = Connection.SqlFormatter

Value = "update ADSGroup set <fieldOfYourOwn> = cntsum  from " &
    "( select distinct(id), Sum(cnt) as cntsum from " &
    "        ( select distinct(uiD_Adsgroup) as id, count(*) as cnt from ADSAccountINADSGroup group by UID_ADSGroup " &
    "            union " &
    "            select distinct(uiD_Adsgroupparent) as id, count(*) as cnt from ADSgroupINADSGroup group by UID_ADSGroupParent  " &
    "        ) as x group by id " &
    ") as y " &
    " where UID_ADSGroup = y.id and " & f.UIDComparison("UID_ADSDomain", $UID_ADSDomain$)

Create a monthly schedule to fire an event connected with this process.

So the process will run once per month and update the field counting the memberships with a single operation. You may extend the "set" list of the statement with "<fieldOfYourOwnOld> = <fieldOfYourOwn>" to move the value of last month to the "...old" field.

regards,

  Tino

Agreed, nice solution Tino. Although I do believe this would just calculate the number at the moment the schedule runs (not necessarily the maximum for the month, assuming people also get removed from the group).

So here it depends on your exact requierments Paul :).

If you need the measurement at the end of the month, definetly go with a suggestion like Tino suggested.

If you need the field updated every time there is a mutation in group memberships an on-saving script for ADSAccountInADSGroup is what you can use to 'trigger' when there are changes there.

Alternatively you can also assign a proces to the assign / deassign events for that table obviously.

Thank you tlindner . As jos groenewegen  mentioned, the measurement at the end of the month is not the requirement. We need a report of the max count for the whole last month. That's why the solution with the onSaving/onSaved-Script is better for us.

Thank you jos groenewegen 

It is a good idea, but we think, onSaved-Script is better. While running the onSaving script, the assignment ist not set yet.

To review the amounts we activated "Log changes" for the field(s)

Sharp, yes OnSaved is probably better than OnSaving. Glad to hear you were able to get to a good solution.