Data export from IdM to LogRhythm

[former_member1295755]

Dear Experts

We are trying to use LogRhythm tool for IdM reporting. The idea is to export all the audit data together with the corresponding user mskeyvalues to this tool and then create report based on various scenarios like 'All the users disabled this month by IdM in AD' etc.

So I wrote the below query join which gives the data but run for many hours to get even 100 rows of data. I am not sure whether the join I made is a optimized one. Can you please suggest how I can improve this query or any other approach please

SELECT  A.AuditID, A.TaskName ,A.Provision_status ,A.posteddate,A.Userid ,A.MSG,B.MSKEY,B.SearchValue,c.Aud_task,c.Aud_StartedBy,c.Aud_OnEntry,D.SearchValue FROM mxv_audit A WITH (NOLOCK) INNER JOIN idmv_value_basic_all B WITH (NOLOCK) ON  A.Userid like B.MSKEY   LEFT OUTER JOIN MXPV_Ext_Audit C WITH (NOLOCK) ON A.AuditID = C.Aud_ref  LEFT OUTER JOIN idmv_value_basic_all D WITH (NOLOCK) ON C.Aud_OnEntry = D.MSKEY where B.AttrName = 'MSKEYVALUE' and D.AttrName = 'MSKEYVALUE'

Kind Regards

Shiju Krishnan

[former_member201064]

Hello Shiju,

I have this one as one of my "base queries". I tried joining and sub-selecting the ext-audit to it, yet, this is sheer overkill.

I don't use this query for reporting, but monitoring, so I have the "and s.mskey = 123456" and some more, which makes the query very fast.

select

s.auditid,

(select taskname from mxp_tasks with (nolock) where taskid = s.TaskId) taskName,

(select taskname from mxp_tasks with (nolock) where taskid = s.LastAction) lastActionName,

(select name from MXP_ProvStatus with (nolock) where ProvStatus = s.ProvStatus) provStatusName,

PostedDate, userid, MSG, mcMSKEY, mcMSKEYVALUE, TaskId, s.StartedBy

from mxp_audit s with (nolock)

join idmv_entry_simple e with (nolock) on s.mskey = e.mcmskey

where e.mcEntryType in ('MX_PERSON')

--and s.mskey = 123456

order by PostedDate desc

This query loads 250k rows in about 90s. Should be somewhat OK.

Maybe you can reduce the load times with the posteddate column to a reasonable amount or joining on only changed persons or similar.

Best regards

Dominik

[former_member1295755]

Hi Dominik,

Yes this helps me to build another query. Many thanks

BR

Shiju