.csv export

[Former Member]

Hello,

As a beginner in IDM solution, can someone please help me to export 2 differents sql request results in .csv file ?

The goal is to compare the output (columns) ?

Thanks,

Nina

[Former Member · Accepted solution]

As you can see I am geting the same result but as I am requesting this information from the same view: idmv_link_ext so different mcUniqueID in the both request.

Another point is to use UNION I have to remove all my headers I want to get the delta and keep only: mcOtherMSKEYVALUE

Here is my SQL:

--SQL1

SELECT

UR.mcUniqueID MSKEYVALUE, UR.mcThisMSKEY MskeyUser, UR.mcThisMSKEYVALUE Users, UR.mcOtherMSKEYVALUE UserRoles, PR.mcThisMSKEYVALUE UserPrivileges,

UR.mcAssignedDirect isDirect

FROM idmv_link_ext2 UR inner join idmv_link_ext2 PR on UR.mcOtherMSKEY = PR.mcOtherMSKEY

WHERE UR.mcThisOcName='MX_PERSON'

AND UR.mcOtherOcName='MX_ROLE'

AND PR.mcThisMSKEYVALUE like 'PRIV:%'

AND UR.mcThisMSKEYVALUE='3003'

-- SQL2

SELECT

mcUniqueID, mcThisMSKEYVALUE, mcOtherMSKEYVALUE

FROM idmv_link_ext2 with (nolock)

WHERE mcThisOcName='MX_PERSON'

AND mcOtherOcName='MX_PRIVILEGE'

AND mcLinkState=0

AND mcOtherMSKEYVALUE not like 'PRIV:SYSTEM%'

AND mcThisMSKEYVALUE='3003'

Any other method to get the delta with the other columns?

Nina

[Former Member · Accepted solution]

1st, your first select uses "UR.mcUniqueID MSKEYVALUE", this will never be correct. The mxi_link.mcuniqueId is a counter with no match to the contents of mskey or mskeyvalue columns of mxi_entry or mxi_values.

2nd, I'm having problems understanding what your query is attempting to do, and the screenshots have headers that don't match your SQL statements. I think it would help us all if you describe what you're trying to report.

To use union (all) you need identical header datatypes from both queries, not to remove everything except one column.

To find differences between two queries you should be able to use standard sql such as

select mcmskeyvalue, mcothermskeyvalue, mcAssignedDirect

from idmv_link_ext where

mcmskeyvalue = '3003' and mcOtherMskeyValue = 'PRIV:%' and mcOtherMskeyValue not in(

  select mcOtherMskeyValeu from idmv_link_ext where ....

)

Since your usecase is not providerd it's not possible to give a working example.

Br,

Chris

[Former Member · Accepted solution]

Hi Chris,

I need to be sure that all assigned BR/Privileges correspond to the reality at any time for a UserID, so I choose to export this content in .csv after developing the sql request and puting this in To Ascii pass:

Example:

BR1 : Priv10 , Priv11

BR2 : Priv20

At T= 0 :

UserID	DisplayName	BusRole	Privs	Privs exists in BusRole?	Privs assigned to the UserID?	AssignedDirectly?
XY	X Y	BR1	Priv10	Yes	Yes	Non
XY	X Y	BR1	Priv11	Yes	Yes	Non
XY	X Y	BR2	Priv20	Yes	Yes	Non

At T=1:

For some reasons the assigned Privileges are not aligned yet (Dirty job).

For example: Priv30 is added to BR1.

UserID	DisplayName	BusRole	Privs	Privs exists in BusRole?	Privs assigned to the UserID?	AssignedDirectly?
XY	X Y	BR1	Priv10	Yes	Yes
XY	X Y	BR1	Priv11	Yes	Yes
XY	X Y	BR2	Priv20	Yes	Yes
XY	X Y	BR1	Priv30	Yes	Non

So how to get the final report?

Nina

[Former Member · Accepted solution]

Hi Chris,

Any comment on what I am trying to report?

Thanks,

Nina

[Former Member · Accepted solution]

If I understand correctly you want to report any privilege assignment that is inherited from a role that is not completed.

This will list all inherited privileges and the role they're inherited from and their state, but is only valid for a single level hierarchy with user <- role <- privilege links and does not look at valid-from/valid-to or context which also affects this. Which btw. is why the UIs can be a bit slow to list users with thousands of assignments. There's a lot to check to determine the state of an assignment...

select

  userRoles.mcThisMskeyValue userName,

  rolePrivs.mcThisMSKEYVALUE inheritedPrivilegeName,

  userRoles.mcOtherMskeyValue inheritedFromRoleName,

  case

    when userPrivs.mcLinkState = 0 then 'Assigned OK'

    when userPrivs.mcLinkState = 1 then 'Inactive or pending'

    when userPrivs.mcLinkState = 2 then 'Deleted'

    else 'How did you manage this?'

  end inheritedPrivilegeAssignmentState

from

  idmv_link_ext userRoles with(nolock)

  inner join idmv_link_ext rolePrivs with(nolock) on rolePrivs.mcOtherMSKEY = userRoles.mcOtherMSKEY and rolePrivs.mcThisOcName = 'MX_PRIVILEGE'

  left outer join idmv_link_ext userPrivs with(nolock) on userPrivs.mcOtherMskey = rolePrivs.mcThisMskey and userPrivs.mcThisMskey = userRoles.mcThisMSKEY and  userPrivs.mcOtherOcName = 'MX_PRIVILEGE' -- and userPrivs.mcLinkState <> 0

where

  userRoles.mcThisMskey in(select mcmskey from idmv_entry_simple where mcmskeyvalue like 'USER.TEST.ROLEPRIVSTATE.4') and

  userRoles.mcOtherOcName = 'MX_ROLE' and userPrivs.mcLinkState is not null

order by

  userRoles.mcThisMSKEY,userPrivs.mcOtherMSKEY

If you uncomment the -- and userPrivs.mcLinkState <> 0 part it will only list inherited privileges that are not fully assigned. If you remove the  and userPrivs.mcLinkState is not null part you will also get the role and its state listed, as well as privileges not yet expanded by eval-link with the "How did you manage this?" state.  Rename and reorder columns as needed, link in privilege/role/user displaynames from idmv_entry_simple if you need them, change the userPrivs.mcLinkState to list only.

I believe this should be fairly correct, but this is as much as I can do for now in between the development work I need to do. Perhaps someone else can build and expand on it as well.

Results with all linkstates:

With only inactive/deleted:

After I enable my Validate task which is blocking the privilege assignment (all states):

Br,

Chris

[Former Member · Accepted solution]

Your are the best Chris!

Your sql is working perfect for one person fro example '3004', my last question is how to run it for all users?

I replaced 'USER.TEST.ROLEPRIVSTATE.4' by '%' but I get all entries I need only 'Persons'

Nina

[Former Member · Accepted solution]

Hi Chris,

I just added mcEntryType='MX_PERSON' and removed the 'mcmskeyvalue like 'USER.TEST.ROLEPRIVSTATE.4' and I got all the assignement for all users.

Thank you very much for your help,

Nina

[Former Member · Accepted solution]

Edit: I see you added your own solution almost at the same time as I added this 🙂

In the where clause, replace the

  userRoles.mcThisMskey in(select mcmskey from idmv_entry_simple where mcmskeyvalue like 'USER.TEST.ROLEPRIVSTATE.4') and

statement with

  userRoles.mcThisOcName = 'MX_PERSON' and

I think that should do it.

Perhaps the original statement should have been

  userRoles.mcThisOcName='MX_PERSON' and userRoles.mcThisMskey in(select mcmskey from idmv_entry_simple where mcmskeyvalue like 'USER.TEST.ROLEPRIVSTATE.4') and

For effiency.

Message was edited by: Per Krabsetsve

[Former Member · Accepted solution]

Thank you very much Chris for your expert help, you made my day

Nina

[Former Member]

Hello,

Many thanks for you all.

My problem is that I have 2 sql requets.

Select x,y,z from idmv_link_ext where ...  => Returns 50 priviliges records

A different request Select a,b,c from idmv_link_ext where... => Returns 45 priviliges records

How to get this delta(5) between the 2 results in .csv fro example? This job is to run weekly.

Thanks,

Nina

[Former Member]

Hi Nina,

in this case, I'd work with a UNION (not UNION ALL) Operator in the Source tab. This gives you only the distinct values as described here: SQL UNION Operator

In the destination tab you can just write the entries to a csv file

Maybe you can also use the INTERSECT operator in MSSQL, but I've never worked with this: EXCEPT und INTERSECT (Transact-SQL)

best regards

Matthias

[Former Member]

Hi Matthias,

The problem here is that I got my 2 list of privileges by 2 different ways and I don't have the same number of columns as you can see here to use "UNION" and I need these columns.

Any help?,

Nina

[Former Member]

Hi Nina,

I think you should use a join to have the two queries side by side and then put this in a TO ASCII pass as Matt mentioned.

I don't know what's you use-case but if you only want to run this query from time to time or only once, I would run this in SQL Management Studio (or whatever SQL tool you use) and export the CSV from there.

best regards

Matthias

[terovirta]

Also, depending on the use case, if you want to compare entries (or more than just one column), it's easier to use the toAscii passes in IdM.

Make sure that your SQL returns MSKEYs and by seleting the "use identity store" you can dump all the attributes the entries have to the CSV with column headings etc much easier than you would be able to do in the query tool.

regards, Tero

[former_member2987]

Nina,

You can do this by using a TO ASCII pass type.  Place your SQL query in the SOURCE Tab and then use the Insert Template Button in the DESTINATION Tab to read the values.  Apply what you've done and then run the job.

Matt