A very common problem I see on the Orchestrator forums is people noticing that their Runbooks are not showing up properly in their web service. This usually manifests itself when they are looking either in Service Manager (Runbooks not all syncing) or on the web console (not all ‘showing’ up). So, what is going on and how do you fix it?
What is Going On?
When you hit the web service (either directly or through another interface like the web console) it looks at who you are and looks at its authorization cache (a SQL Table) to see if it has figured out what you have access already. If it has already figured out what you have access to it will just show you those things it granted you (or your group) access to you and will not re-calculate (this means you won’t see new things). This is done for efficiency, rather than calculating what you should have access to with every request you send it does it one time; the problem here is that you also need to see new Runbooks / folders. This is accomplished by a re-occurring task on the Orchestrator DB called ClearAuthorizationCache which, by default, runs every 10 minutes and runs the stored procedure [Microsoft.SystemCenter.Orchestrator.Internal].[ClearAuthorizationCache]. To check your environment to see if this is enabled and working you can run the following SQL command
select * from [Microsoft.SystemCenter.Orchestrator.Maintenance].MaintenanceTasks
What the stored procedure does is truncate the [Microsoft.SystemCenter.Orchestrator.Internal].AuthorizationCache table, which means the next time you hit the web service your permissions will not be found and they will be re-calculated. So, the common work-around posted online to 'fix' things not showing up is to tell administrators to simply run the following query on their Orchestrator DB by hand before doing something like syncing their Service Manager connector
TRUNCATE TABLE [Microsoft.SystemCenter.Orchestrator.Internal].AuthorizationCache
The problem is that there is another problem that you can run into, the web service has an internal timeout of 30 seconds for SQL queries. The web service actually ends up running [Microsoft.SystemCenter.Orchestrator].[ComputeAuthorizationCache] to figure out what your security token has permissions to and, in larger environments with many Runbooks and folders this can often take more than 30 seconds (it averages out to take about 2 minutes for our normal environments) and what you are left with is a 'partial' set of permissions meaning some things show up and some things do not.
First off, don't just use this workaround, this is a bug that is open with Microsoft, please open a support case so we can get the problem resolved in a service pack or CU. Once you have done that I'd suggest you look at the workaround we have implemented and think about using it (at your own risk of course). What we have done is disable the SQL job to automatically clear the authorization cache. This means that each time the web service is hit there should be a 'valid' authorization calculation sitting out there to use. The second thing we did is combine the truncate table command and the compute authorization cache stored procedure calls into a Runbook that runs on each of our environments every hour (this means that we calculate, without a timeout, a valid set of permissions for everything every hour). We also expose this Runbook for on-demand scheduling through a service manager request
Workaround SQL Query
Use Orchestrator Truncate table [Microsoft.SystemCenter.Orchestrator.Internal].AuthorizationCache DECLARE @secToken INT DECLARE tokenCursor CURSOR FOR SELECT Id FROM [Microsoft.SystemCenter.Orchestrator.Internal].SecurityTokens OPEN tokenCursor FETCH NEXT FROM tokenCursor INTO @secToken WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'Computing Authorization Cache for Security Token: ' + Convert(Nvarchar, @secToken) exec [Microsoft.SystemCenter.Orchestrator].ComputeAuthorizationCache @TokenId = @secToken FETCH NEXT FROM tokenCursor INTO @secToken END CLOSE tokenCursor DEALLOCATE tokenCursor
Self Service Request Runbook
Self Service Request Service Request
More Service Manager Specific Problems
The other constraint you may run into for Runbooks / folders not showing up has to do with the functionality of the service manager Runbook connector. By default the Runbook connector does not 'page'. This means that the connector will only return a max number of Runbooks or subfolders for a given folder, by default this number is 50. This means that if you have more than 50 subfolders they will not show up on a sync. Again, this is a bug, please submit it with Microsoft before implementing this workaround at your own risk. The fix is to update your 'page' size for whatever isn't syncing (if you have more than 50 Runbooks in a folder you need to increase that number, if you have more than 50 subfolders in a Folder you need to update the folder number). This setting is found in the Orchestrator Web Service Application Settings, update the number then recycle your application pool and viola, Runbooks / subfolders show up!