Posts tagged: Cleanup

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

 

Updated: DB Cleanup Tool 2.0

By Max Ranzau

 

From the Tools-R-Us Dept. You may recall a while back in February, I reported on a cool utility to address the issue with clearing individual log files in RES Workspace Manager. There’s now a new version 2.0 out from one of our community heroes (=someone who contributes and shares stuff), Patrick van Grinsven in the Netherlands (For the record, Morgan Freeman did not develop it ;). The SQL Database Logging Cleanup Tool has seen a few GUI changes and some other improvements:

  • It is now possible to directly Analyze / Query / Clear the configured logging database if the supplied connection details and logs are valid.
  • It is now possible to Analyze / Query / Clear the logging between dates, or to completely clear the selected log (1)
  • Logging analysis is being sorted descending.
  • Displayed record count added (2)

If you are a RES engineer or admin, this utility should most definitely be in your Bat-utility belt.

For further information and downloads, see the updated article here: doc-icon2

 

Automated VDI Optimization

legobrick-croppedFrom the BuildingBlock Dept. Here’s a set of buildingblocks for Automation Manager I’d like to share with you. They have kindly been provided by my co-blogger Rob Aarts. Rob asked me to go through the buildingblock and we agreed to document the result in an article. This collection of modules is very useful when implementing best practices for a Windows 8.1 golden image.

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

 

 

Defeating a live virus/trojan infection with AM

By Max Ranzau

 

From the Crush, Kill & Destroy Dept. This is an aricle about using RES Automation Manager to defeat a live virus infection and cleaning up the colatteral damage afterwards, in case you’re dealing with many computers. With the help of others, I’ve put together a solution, as well as providing some valuable generic takeaways, like how to change special permissions in the registry and how to use the Windows PendingFileRenameOperations queue from within Automation Manager.

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

 

New Technote: Global Authorized Files Cleanup!

From the spring cleaning dept. Ever got frustrated with having a Global Authorized File list which is a mile long? Been wanting to break down your appguard and read-only blanketing security into more manageable chunks? Then this article is for you. It will show you a very slick way of organizing security authorizations using blank/empty applications as placeholders and how you can easily move security settings inbetween them. Note the moving is a PowerFuse 2010 feature.

The article contains a nice buildingblock for you to try out also.

Click here to view the RG026 article.

Cleaning out the Wisdom agent completely

Here’s a bit of info which may come in handy for those of you who spend a lot of time cloning machines and contemplating using Wisdom to manage the clones. As you may know, there are 3 methods in RES Wisdom for identifying the agent:

  1. Using the WUID option
  2. The MAC address of the first NIC and
  3. 3) a combo of the computername and domain name.

In an environment where cloning is performed, using option 1 is not recommended as it may lead you to agents disapearing from the Wisdom console. This is due to the fact that the WUID is written into the HKLM portion of the registry, hence it will be part of the image. This is why we usually recommend either using MAC address or domain+computername as the Agent identification method here

When you uninstall the Wisdom Agent, it’s a quite clean operation. However the WUID value will remain on the target machine when you uninstall it. Although this is per design, it may have some unforseen consequences if you are in the middle of building your clone template. Hence it would be nice to know what to clean out in order to forget the Wisdom agent has ever touched a machine.

The registry keys you are looking for are:

HKLM\SOFTWARE\RES\Wisdom\Preferences\WUID
HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\WUID\Default

If you need to clean out the Wisdom agent completely, make sure you delete both the WUID keys.

Update: August 24th 2010 – This topic has been integrated into Technote RG028.


Got Skeletons?

skeleton_in_the_closetAnimated, Gears, boxA technote was published in the technote library in late March. This one will help you clean up any embarrasing log entries, which you need to clean out for one reason or another. Suppose you are running PowerTrace with WebTrace enabled in your environment and you or somebody else manage to surf to a webpage which everybody just rather forget about, then you need to find a way to surgically remove the skeletons from the closet, as you may want to retain the remainer of your PowerTrace logs.

In order to do this, you need to have the proper credentials for the PowerFuse datastore.

The article available here, will show you how to deal with this problem. A nifty buildingblock for Wisdom has also been included in the article.

Reducing the size of the PowerFuse database

Animated, Gears, boxA brand new article has been posted to the Technote Library. This time we’re diving into the PowerTrace tables. Being new to PowerFuse, some will be inclined to switch on everything, including PowerTrace turned to the Maxx, resulting in a potentially very unwanted huge heap of logdata and perhaps even a slow performing DBMS too.

This article explains how to both cure that situation if things have gone megabad, but also how to prevent it from happening in the future. 

Click here to read the full article.

Clearing individual PowerFuse Logfiles

Animated, Gears, boxThis technote and associated Wisdom BuildingBlock is inspired by a public posting at the RES Forum, specifically on how to access certain elements of the PowerFuse datastore. Now, before we proceed let’s get one thing straight. If you start messing around with your datastore on your own, the key operating phrase here is: ON YOUR OWN, i.e. don’t go crying to RES Support if you screw up and haven’t backed up your database. Responsible admins only, capice? 

Right – with that being clear, what’s all this about then? Well, one of the things which would be very usefull would be having the ability to clear individual logfiles. Let’s say for example that you want to keep your AppGuard log, but want to flush the IP-security log, perhaps because you configured it to learning mode, and you’re now swamped with a bunch of useless log entries and you want to blow those out and start over. Unfortunatly it’s not currently possible through the GUI to delete individual logs. So, with the help of a nifty SQL statement this is possible.

The technote wil demonstrate how you can clear out specific logs, without having to clear all logs at once. A Wisdom Buildingblock has been created to help get it right. Resguru.com will assume absolutely NO responsibility nor liability from the result of utilizing anything published in this article, or anything else on this blog for that matter.

With all said and done,  click the brick to read the article:  legobrick_red