Feature Request | History export to SQL Server

Follow

Ksallis

• June 13, 2019 16:46

Thank you for the new history/audit log feature built into GroupID v9.  We would like to extend the feature of the audit log to our PowerBI infrastructure as a quick visual representation of changes made to the distribution group.  PowerBI is capable of pulling in the CSV file however we would like a method to directly query the GroupID SQL database to retrieve the same content shown in the history/audit log or expand the existing retention export options to include a SQL database.   

1

• Official comment

  

  Hatif Tahir

  • November 26, 2019 18:03

  Hi Kevin & Paul,

   

  We worked on above feature request and published a following KB article that may help you to achieve your requirement using PowerBI tool.
  
  https://support.imanami.com/hc/en-us/articles/360038328454-How-To-View-GroupID-History-Data-in-PowerBI

  After working with @Paul, we figured out an alternative with the help of SQL database as well. Here are steps to access history data using SQL database.

  • Create a view and provide following SQL query.
    

    Select id,
    IdentityStoreID,
    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')

  • After creating view, you'll be able to access history data with the help of a simple SQL query "Select * From "ViewName"".

  Feel free to us know if you have further questions or you need assistance regarding this.

  Comment actions Permalink
• 

  Paul Vu

  • November 12, 2019 17:33

  Any update on this request? This is something we are also interested in for reporting.

  Also can these feature requests include the date when submitted to gauge if this is an old/new request.

   

  1

  Comment actions Permalink

Please sign in to leave a comment.