• 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,

    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

Reply
  • 0 5 months ago

    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

Children
  • 0 5 months ago in reply to tlindner

    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.

  • 0 5 months ago in reply to jos groenewegen

    Thank you  . As   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.