Views:

Applies To:

GroupID 10

Business Requirement:

We are using GroupID to manage a large amount of data in Active Directory. To keep track of the data and all the changes, we have to export all data displayed on the History Summary page in GroupID Management Console. This is also required for record-keeping. History can be directly exported from the History Summary page, but is there another way to export it?

More Information:

Other than GroupID Management Console, we can extract the history data displayed on the History Summary page from the SQL database directly. In SQL, the data (history) is present in the form of JASON. We need to create a view to extract the information.

Steps:

Follow these steps to extract the History Summary data from the GroupID database.

  1. Launch SQL Server Management Studio and go to the database that GroupID is joined to.

  2. Browse to the SVC.BackupHistory table and execute the following query:

    CREATE VIEW HistoryT AS
    Select id,
    JSON_Value(JsonData,'$.EventID') as EventID,
    JSON_Value(JsonData,'$.IdentityStoreID') as IdentityStoreID,
    JSON_Value(JsonData,'$.ObjectGUID') as ObjectGUID,
    JSON_Value(JsonData,'$.ObjectClass') as ObjectClass,
    JSON_Value(JsonData,'$.ObjectName') as ObjectName,
    JSON_Value(JsonData,'$.AttributeName') as AttributeName,
    JSON_Value(JsonData,'$.Action') as Action,
    ActionName,
    JSON_Value(JsonData,'$.DateTimeDetected') as DateTimeDetected,
    JSON_Value(JsonData,'$.DateTimeLogged') as DateTimeLogged,
    JSON_Value(JsonData,'$.UserAccount') as UserAccount,
    JSON_Value(JsonData,'$.MachineName') as MachineName,
    JSON_Value(JsonData,'$.ClientMachineName') as ClientMachineName,
    JSON_Value(JsonData,'$.UserDN') as UserDN,
    JSON_Value(JsonData,'$.UserGUID') as UserGUID,
    JSON_Value(JsonData,'$.NewValue') as NewValue,
    JSON_Value(JsonData,'$.NewValueResolved') as NewValueResolved,
    JSON_Value(JsonData,'$.OldValue') as OldValue,
    JSON_Value(JsonData,'$.OldValueResolved') as OldValueResolved,
    JSON_Value(JsonData,'$.NewMembers') as NewMembers,
    JSON_Value(JsonData,'$.OldMembers') as OldMembers,
    JSON_Value(JsonData,'$.Comments') as Comments,
    JSON_Value(JsonData,'$.ChangeMadeBy') as ChangeMadeBy,
    SVC.Client.Name,
    JSON_Value(JsonData,'$.RequestedAction') as RequestedAction,
    JSON_Value(JsonData,'$.WorkflowRequester') as WorkflowRequester,
    JSON_Value(JsonData,'$.WorkflowRequesterGUID') as WorkflowRequesterGUID,
    JSON_Query(JsonData,'$.HistoryDetails') as HistoryDetails,
    JSON_Value(JsonData,'$.DisplayName') as DisplayName,
    JSON_Value(JsonData,'$.HistoryObjectAttributes') as HistoryObjectAttributes,
    JSON_Value(JsonData,'$.DescriptiveData') as DescriptiveData,
    JSON_Value(JsonData,'$.HasDetails') as HasDetails,
    JSON_Value(JsonData,'$.listOfAttributes') as listOfAttributes,
    JSON_Value(JsonData,'$.UserDisplayName') as UserDisplayName,
    JSON_Value(JsonData,'$.TicDateTime') as TicDateTime,
    JSON_Value(JsonData,'$.ClientType') as ClientType,
    JSON_Value(JsonData,'$.NewImageValue') as NewImageValue,
    JSON_Value(JsonData,'$.OldImageValue') as OldImageValue from svc.BackUpHistory, Event.Action, SVC.Client
    where ActionID = JSON_Value(JsonData,'$.Action') AND Secret = JSON_Value(JsonData,'$.ChangeMadeBy')

    This command creates a view of all these attributes. We can also add/remove the attributes according to our need.

  3. Now click New Query and execute the following command:

    select * from HistoryT

    Here, 'HistoryT' is the name I gave to the view that I created. Replace it with the name of your view.
    After executing the command, the data in the table is displayed in readable form.

  4. To save the results to a CSV file, right-click in an empty cell on the Results tab.

  5. On the shortcut menu, select Save Results As to save them to a file by giving a path.


     

Keywords: JSON, SQL, history, view, export, extract, data