In our identity feed, we have a flag indicating if userid is enabled or disabled. I want changes to this flag's state to affect the user table's dateenabled and datedisabled fields. I could do it via a scheduled SQL function on the server side, such as check flag status and if current state's time stamp <=opposite state's time stamp, update it. (Or maintain prior state in another field or table, compare, and if changed, update secondary table as well as appropriate time stamp.)

Wondering if better way to do it within Securonix, possibly within the import itself.

Happy holidays, Jason

asked 18 Dec '14, 09:47

JasonBlue's gravatar image

JasonBlue
17129
accept rate: 0%

edited 24 Dec '14, 11:28


You can use the post process sql query to do this. This is part of the import job itself. On Step 2 of the import job, scroll down to the Pre and Post Actions section.

Name: give it any name you want Type: post processor Enabled: true Class: DOnt enter any thing here SQL: Provide the sql you want. make sure not to enter semicolon after the sql Example: update users set enabledate=CURRENT_DATE() where someflag=1

link

answered 18 Dec '14, 20:09

tgulati's gravatar image

tgulati ♦♦
2061411
accept rate: 14%

As we don't want the date updated on each import, the best I've come up with so far are two post-processors, first along the lines of UPDATE users SET enabledate = now() WHERE disableflag = FALSE AND (enabledate is null OR datediff(enabledate, disabledate)<0) the second would be for the reverse condition, when the disable flag is TRUE.

Seems a bit cumbersome, so unless I really need those date field populated (e.g., to report on violations on length of time between termination and disablement), I'm just going to enable change tracking on the flag. This will at least give visibility into when the flag changed via the UI.

Your answer came in very handily for another need; our identity feed only includes the manager's employeeid, so using a post processor I am now populating the manager's name:

    UPDATE users ut1
    JOIN users ut2 ON ut1.manageremployeeid = ut2.employeeid
    SET ut1.managerfirstname = ut2.firstname, ut1.managerlastname = ut2.lastname, ut1.managermiddlename = ut2.middlename
    WHERE datediff(ut1.updatedate, NOW())=0

Thanks Tanuj for insight! Jason

link

answered 24 Dec '14, 11:24

JasonBlue's gravatar image

JasonBlue
17129
accept rate: 0%

I tested and verified that new users' updateddate is blank when first added; so above script should be modified as follows:

UPDATE users ut1
JOIN users ut2 ON ut1.manageremployeeid = ut2.employeeid
SET ut1.managerfirstname = ut2.firstname, ut1.managerlastname = ut2.lastname, ut1.managermiddlename = ut2.middlename
WHERE datediff(ut1.updatedate, NOW())=0 OR ut1.updatedate IS NULL
link

answered 18 Mar '15, 15:43

JasonBlue's gravatar image

JasonBlue
17129
accept rate: 0%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "Title")
  • image?![alt text](/path/img.jpg "Title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×11
×2
×1
×1
×1

Asked: 18 Dec '14, 09:47

Seen: 32,007 times

Last updated: 18 Mar '15, 15:43