Posts tagged: it store

Removing zombies from Service Store

By Max Ranzau

 

From Rick Grimes has no patience for the undeleted dead.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.

3zombiesEffectively 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.

warning, yellowLike 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.

sql 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 (7.0.3.0) 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.

If you’re still up for it, try out this buildingblock in your lab:  legobrick-cropped

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
);