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

 

Bug alert: The Zombification Attribute

By Max Ranzau
Will code C# for brrraaaaaaiiiins!

 

From The Brrrraaaains Dept. Although the title might sound like a weird crossover episode between Big Bang Theory and The Walking Dead, I had a super scary experience with Service Store this week. All of a sudden people attributes had disappeared from a client development environment and everyone was biting their nails the problem would propagate into production. Even the built-in People Attributes; Security Questions and Answers, had disappeared from all users when you went to their Attributes tab. What was even worse; services were failing left and right – specifically those which used any reference to #Subscriber(personattribute) or #Requester(personattribute). Looking directly into the OR_PeopleAttributes table, via SQL Studio I could see my attributes were still intact, alas something was making the ServiceStore act all gnarly and puke dayglo, while everything else seemed to work normal.

At this time of writing, I have only experienced this problem with the latest ServiceStore 2015 FR2, Update 2 AKA (8.2.2.0). I do not know if earlier versions of Service/IT store are affected. And yes, this has been reported to the Merry Men of RES Support. You’ll probably notice a new KB article over the next few days until engineering devises a fix for this.

errattr

I’ll spare you the long trials and tribulations I went through to nail this bug down over the course of a night, with only a pot of coffee and Radio Paradise for company. Let’s cut to the chase:

The problem is specifically with certain People Attributes you may define: It would appear if you define a Person Attribute of the TABLE type, with more than 6 columns defined, the problem will manifest itself at some point and your people attributes will be zombified. What specifically triggers it, is not exactly clear, however I suspect it would be when a WFA (WorkFlow Action) references the attribute. I was able to manually trigger it in a new clean database by importing a buildingblock containing the offending attribute and a dummy service.

The good news is that until the software engineers fix the problem, it is relatively easy to get rid of: Go and either delete the table Person Attribute from your Data Model, or edit it down to 6 columns or less. The moment column #7 is deleted and the table definition saved, all the hidden people attributes would re-appear.

So, now we know what we’re dealing with, allow me a moment to spin my thoughts on this: The table objects were originally created to cater for MDM, like registering something a user might have more than one of, such as mobile devices, tablets etc. Typically 4-5 fields were used for Device type, Model, Carrier, Phone#, etc, thus I can only muse that more than 6 columns might never have been attempted duing test – that is until your friendly neighborhood blogging-bull came charging through the china store and created a table attribute with 14 columns.

This concludes the alert/early warning. As mentioned, RES have already been notified, so hopefully this article will be obsolete soon.

 

Setting up a Lab HR system for IT Store

xor-logoFrom the Lab Essentials Dept. This article is to show you how you can stand up your very own open-source HR system and hook it up with RES IT Store. One of the things you may often hear about in regards to RES IT Store, is the ability to do employee on/offboarding. If you want to test this out for real, you probably won’t get access to a live HR system in production, thus I wrote this article.

doc-icon2<<< Click here to read the article

 

Managing VMware AppVolumes with RES

appvolumesFrom the Skunkworks Dept. This is an article I’ve been looking forward to writing for a while. I had the opportunity to look at AppVolumes when it was still pre-VMware Cloudvolumes, but as you know – timing is everything, so I decided to hang back until VMware released AppVolumes today! In this article I will share some thoughts on how the AppVolumes product can be augmented by RES technology to offer some very interesting options for integrators.

doc-icon2<<< Click here to read the article