From the Hacking Dead dept. Service Store is a fine HR data processor and workflow engine, when you set it up to pull people and department data in from an authoritative data source. In a previous article I showed an example on how to do just that. However, when a person is marked as deleted in your datasource, IT Store doesn’t delete the user. They effectively are the living dead IT Store people, except in this case they won’t try to claim a license or your brains.
Update: This article was updated on May 8th 2016 with new and improved SQL.
Deleting a user in IT Store has always been a two-stage affair. Initially when IT Store marks a person for deletion it uses the opportunity to scan for any and all delivered services. One should not tinker with this. However, once mentioned services have been properly returned, the user is then marked as [Ready for deletion]. But that’s all she wrote. Nothing more happens.
Effectively this means over time an organization with thousands of annual onboarding/offboardings (think educational institutions for example) will have a pileup of
undead un-deleted people in IT Store. Sure, they’re obscurred from view until you check the “Include people marked for deletion”. Your only current option is to manually go Mischonne on them in the console yourself. (Yes, I know – old screenshot, but it’s the same deal)
Update: There is also a another problem with leaving people not deleted in the ServiceStore. If you need to re-use people identifiers, say when you delete someone, their email address can be re-registered. This is not the case if a person is not deleted manually from the store.
The design rationale is that since some HR systems don’t delete the employee when off-boarded, then neither should ITS. Here’s where I disagree. It makes sense for HR systems to keep a record of previous people for administrative reasons, but since ITS is the conduit into the rest of the IT infrastructure organization, there’s IMHO little point in keeping a record here once you’ve cleaned up everywhere else. After all, during off-boarding we’d probably be exporting the user’s mailbox and zip up his homedrive as we don’t want dead user remains floating around in the production environment.
At this stage there’s only one way to deal with this if you don’t want to manually flush users marked ready for deletion: Hack the IT Store database.
Like any other vendor, RES gets nervous ticks and reaches for their crossbow, when you start messing with the
brraaaiiins grey matter of the datastores, thus the usual warnings apply: If you do this, you’re on your own. See the MOAD for details. Also, may I recommend you make a backup of the datastore and KNOW how to restore it.
That said, let’s look at the updated
hack. It consists of 3 consecutive SQL delete queries. The first version of this database hack only deleted the person, but since people attributes and identifiers are stored in separate tables, they would be orphaned if you don’t clean them out before deleting the person. Presuming your datastore is running MSSQL, the new and improved update SQL looks like this:
-- delete all people identifiers associated with this person DELETE FROM [$[in.db.its.name]].[dbo].[OR_PeopleIdentifiers] FROM [$[in.db.its.name]].[dbo].[OR_PeopleIdentifiers] AS ppli INNER JOIN [$[in.db.its.name]].[dbo].[OR_Objects] AS pers ON ppli.PersonGuid = pers.Guid WHERE pers.Type = 1 and pers.RecordStatus = 2; -- delete all people attributes associated with this person DELETE FROM [$[in.db.its.name]].[dbo].[OR_PeopleAttributes] FROM [$[in.db.its.name]].[dbo].[OR_PeopleAttributes] AS ppla INNER JOIN [$[in.db.its.name]].[dbo].[OR_Objects] AS pers ON ppla.PersonGuid = pers.Guid WHERE pers.Type = 1 and pers.RecordStatus = 2; -- delete the person DELETE FROM [$[in.db.its.name]].[dbo].[OR_Objects] WHERE [$[in.db.its.name]].[dbo].[OR_Objects].Type = 1 and [$[in.db.its.name]].[dbo].[OR_Objects].RecordStatus = 2;
The $[in.db.its.name] above is an Automation Manager module parameter, containing the name of the ITS database. Running this update query will be the same as manually marking all the users marked [Ready for deletion]. One SNAFU back from IT Store 2014 was the people will not be removed from the ITS console before you exit and re-launch it. My guess is that the records are cached in RAM and are only updated when the old IT Store was doing it’s own operations. This is however not the case with ServiceStore 2015, as the affected people are removed immediately.
Putting this into Automation Manager, I came across a minor problem with the SQL statement execute task in Automation Manager. It looks like as of SR3 (22.214.171.124) the password field can’t be properly parameterized. Sure, you can rightclick on the password field and insert a parameter, but next time you go back and edit the module, the password stops working. Until RES fixes this and puts in a proper set of credential-type accepting field, you’re better off hardcoding the password.
Note1: Buildingblock has NOT been updated with the new SQL statement above, you’ll need to paste that in yourself.
Note2: If you suspect you might already have orphaned people attributes or people identifiers in your datastore you can check with these two statements:
-- test if we have any orphaned people attributes select * from your_storedb.dbo.OR_PeopleAttributes WHERE NOT EXISTS(SELECT NULL FROM your_storedb.dbo.OR_Objects obj WHERE obj.Guid = PersonGuid ) -- test if we have any orphaned people identifiers select * from your_storedb.dbo.OR_PeopleIdentifiers WHERE NOT EXISTS(SELECT NULL FROM your_storedb.dbo.OR_Objects obj WHERE obj.Guid = PersonGuid )
If both queries above come back with zero rows, you’re fine. Otherwise, you’ve got orphans. You can wipe them out like another Scrooge by running these two deletes:
-- delete orphaned people attributes delete from your_storedb.dbo.OR_PeopleAttributes where not exists ( select NULL from resss.dbo.OR_Objects obj where obj.Guid = PersonGuid ); -- delete orphaned people identifiers delete from your_storedb.dbo.OR_PeopleIdentifiers where not exists ( select NULL from resss.dbo.OR_Objects obj where obj.Guid = PersonGuid );