The idea is in theory too.. I was intending to create a csv file based on an SQL output, by parsing the contents of the C104 field..
For eg if a C104 field contained ;1;1000000006;1000000007; for login name XYZ, I’d want 3 rows “XYZ”,”1” “XYZ”,”1000000006” “XYZ”,”1000000007” This just an example .. If I could even have this much basic information, I could feed it into Access and massage the rest of the data as required for licenses, PPL ID’s etc.. With some good SQL code, it is possible to have the PPL ID up there too with those 2 columns.. Joe From: Ben Chernys Sent: Monday, May 07, 2012 2:43 PM Newsgroups: public.remedy.arsystem.general To: arslist@ARSLIST.ORG Subject: Re: People Permission Groups form related question.. ** Hi Joe, Modify works fine – as long as the DO field is set. So, IF the DO field was set and the push fields set the wrong user name, then that user’s group list will probably also be bad. Not with standing that, how do you intend to generate the missing data? It’s a rhetorical question. Just want you thinking about some of the challenges about what you are trying to do and you seem to be doing so J Cheers Ben From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Joe Martin D'Souza Sent: May-07-12 20:27 To: arslist@ARSLIST.ORG Subject: Re: People Permission Groups form related question.. ** Hey Ben, Thanks for your response.. I know what you are trying to caution me on and I appreciate that.. I know I’ll need to consider license info etc. while doing this.. The PPG has rows that correspond to data present in the C104 (Group List) field from the User form.. For *almost every* permission group ID present in C104 separated by a semi colon, there is a record in the PPG form. I want to use this fact to regenerate the PPG data since its gone bad.. Having said that, lets backtrack to the cause of the problem as I may have not conveyed that clearly by not being more precise on how trouble in paradise all began.. Many months ago (many DB backups ago), one of the previous developers attempted an integration to bring in new users added to LDAP into Remedy. While attempting this integration the requirement was to pre-assign the ‘Unrestricted Access’ permission to the new users on creation from LDAP.. For this, the developer created a custom filter that looked into the PPG form to see if the Remedy login name with unrestricted access group existed in the PPG form, and if it didn’t find it to create one. That condition however was bad. I do not recall what that bad condition was, but it always returned true instead of false, and they had modify first matching request if any request matches.. So what this did was modified someone else's record and updated the new user login name to that someone else’s record, and updated the group to Unrestricted Access thus ‘removing’ the other persons PPG record.. So gradually every other users records started to get modified every time there was a new LDAP record added, in an alphabetical order of the Remedy Login Name, people started loosing their PPG records.. When they caught this, I was asked to look into why its happening and for nearly a week I was on the wrong track trying to see what is causing these records to be *deleted* as I initially thought that is what is happening. Every trap I tried to lay for a delete operation, including a DB trigger on delete yielded nothing.. Until Doug Muller, suggested it may not be a delete but a modify that is causing these records to disappear and he was right.. So now we have a bunch of damaged PPG records. Users upto the alphabetical order of M or something have 0 PPG records although they do have their permissions intact on the Group form.. There is no workflow that fires on modify on PPG form to modify contents of User form, so the User form remains unaffected and preserved their group memberships.. With over 50% of damaged records its not worth trying to repair only the damaged ones by infusing the PPG form with the information it needs.. It would be faster and quicker to clean it and redo its contents.. An intact User form with intact Group List information is the reason why I wanted to go down the path of generating the PPG info based on the contents of the Group List.. Sort of ‘reverse engineer’ the data by cross referencing.... We would have lived on with the problem, but unfortunately a lot of application code checks for the PPG form for group memberships for certain things.. I believe even the approval server depends on this form. So we need to fix this data. Sorry for the long post... but just wanted to be more clear.. Joe From: Ben Chernys Sent: Monday, May 07, 2012 1:53 PM Newsgroups: public.remedy.arsystem.general To: arslist@ARSLIST.ORG Subject: Re: People Permission Groups form related question.. ** Joe, People Permission Groups contains data NOT held in the User table (or anywhere else). Remember that you can have many equal permissions and people based on the other data in the record. See the structure of the table and see some of the records you do have. A push to People Permission Groups requires a value be pushed to a DO field to initiate the workflow to synchronise the User permissions groups value. This is more likely your situation. The fix is simply to add that to the push fields action. z1D Action = START Are you sure that you are missing the rows in this table and not the reverse? Generating missing rows in this table is non-trivial because of the missing data. Correcting the User Group Permissions based on the data in People Permission Groups is more realistic through still non-trivial without a Meta-Update script (available). Cheers Ben Chernys Senior Software Architect Canada / Deutschland Mobile: +49 171 380 2329 GMT + 1 + [ DST ] Email: Ben.Chernys_AT_softwaretoolhouse.com Web: www.softwaretoolhouse.com Check out Software Tool House's free Diary Editor and out Freebies Section for an ITSM 7.6.04 Forms and Fields spreadsheet. Meta-Update, our premium ARS Data tool, lets you automate your imports, migrations, in no time at all, without programming, without staging forms, without merge workflow. http://www.softwaretoolhouse.com/ www.softwaretoolhouse.com From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Joe Martin D'Souza Sent: May-07-12 19:21 To: arslist@ARSLIST.ORG Subject: CTM:People Permission Groups form related question.. ** Incorrect code written by another developer, to create People Permission Group records for new users that were added from an external data source (LDAP) got the information in the People Permission Groups form out of sync with the Group List information on the User form. The bad code modified existing PPG records instead of adding a row when a new user was created because of a bad push field if condition.. I have fixed the workflow that caused that, but now have to repair the damage – I now have to fix the damage done to the data that went bad as a result of this (which will be the more fun part – I’m hoping).. Restoring the DB might be your intuitive suggestion, but this unfortunately isn’t an option. This bug in the custom workflow went unnoticed for several months after the bad code was implemented, and the oldest available backup, is very recent – in comparison to when the bad code went live.. I was toying around with the idea to develop a SQL code whose results would create a CSV file required for the PPG form, based on the contents of group_list in the user_x view.. I want to be able to write a good SQL that would generate this data, based on the correct conditions for records that need to exist on the PPG form.. If I am able to do this, I can truncate the data existing in the PPG form (and its H table), and import the CSV file to fix the data. I wanted to know *ALL* the conditions & rules under which a PPG record is created. I think I do know the answer, but because I am attempting to repair foundation data gone bad, I do not want to be missing any possibilities.. Cheers Joe _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are"
<<image001.jpg>>
