Cachebuilder Status & Timeline Display

Tony_Collett

As we know the Cachebuilder is an integral part of the Cireson Portal. 

It would be really awesome if the Cachebuilder had a status & timeline built into the Admin Settings on the Portal

Could be a new tab titled 'Cachebuilder Status' which would be in the Admin Setting display. 

It would display the following information: 
 - Current status: Running, Stopped, Starting, Processing. 
       - Running - means it's performing normal operation
       - Stopped - could mean something went wrong and it was stopped, or it's been stopped manually
       - Starting - means the CB was restarted recently and is still performing the first run operation
       - Processing - means it is currently actively performing an operation (could be detailed with the timeline)
 - Timeline of events for the next 24 hours, for example: 
       - Work Items -> next in 25 seconds
       - Configuration Items -> next in 45 minutes, next in 1 hour 45 minutes
       - Users and Groups -> next in 45 minutes, next in 2 hours 45 minutes
       - Enumerations, Scoped Access, Service Catalog -> next in 12 hours 45 minutes, next in 36 hours 45 minutes
       (Information from https://support.cireson.com/KnowledgeBase/View/1176#/) 

It could enable decent troubleshooting to determine where something might be off, or to determine what process is coming up if the system is slowing down at a certain point. 
It would also be useful to determine when users access might be next available, using the timeline. 

Leigh_Kilday

Good suggestion, Tony.

We have a tabular report showing the cache area (ConfigItems, EnumLookup etc.), last cache run DTG, minutes ago, the refresh interval, next cache run, thread name and table name that it affects. We have a report parameter for the refresh interval too.
We also added some information on what is cached and what requires an app pool / SCSM Health Service reset, so admins and stakeholders know what to expect.

At times the cache doesn't start one of the areas and the Minutes Ago value is blank. It would be nice to drill into a log (separated by cache area) for further information. I'd like to see the current status of each cache area as well as the overall status of the cache builder service.

Mike_Storms

Leigh_Kilday is this something you could share?

Tom_Hendricks

This would be a great time saver.  I would love to have a button to force one of the sync categories to run on-demand, as well.

Perhaps that is a separate feature request, but it made sense as part of the same FR when I first envisioned this looking similar to the SMA dashboard.

Leigh_Kilday

Mike_Storms said:

Leigh_Kilday is this something you could share?

Sure thing. It'll take some time to export from our network.

Ben_Tey

Leigh_Kilday said:

Mike_Storms said:

Leigh_Kilday is this something you could share?

Sure thing. It'll take some time to export from our network.

Great ! that really help for administrator

Konstantin_Slavin-Bo

@Leigh_Kilday
That sound very nice, we would also greatly appreciate if you share it!

Leigh_Kilday

The important data is just coming from ServiceManagement.dbo.LastModified. Here's our stored proc:

CREATE PROCEDURE [dbo].[usp_CiresonCacheBuilderInfo]
AS
WITH cte_LastModified (ThreadName, TableName, Modified, MinutesAgo)
as (
SELECT
CASE Name
WHEN 'CI$User' THEN 'USER'
WHEN 'ConfigurationItem' THEN 'CONF'
WHEN 'Enumeration' THEN 'ENUM'
WHEN 'ServiceOffering' THEN 'CATA'
WHEN'Access_CI$User_WorkItem' THEN 'SCOP'
WHEN 'WorkItem' THEN 'WORK'
ELSE Name
END as ThreadName,
Name as TableName
DATEADD(MINUTE,(DATEDIFF(MINUTE,GETUTCDATE(),GETDATE())), Modified) as Modified,
DATEDIFF(MINUTE,Modified,GETUTCDATE()) as MinutesAgo
FROM ServiceManagement.dbo.LastModified
)
SELECT
p.Name as CacheType
,p.RefreshInterval as RefreshInterval
,l.Modified as Last_Cache_Run
,l.MinutesAgo as Minutes_Ago
,l.ThreadName as Thread_Name
,l.TableName as Table_Name
,p.LastUpdate as Last_Param_load
,p.ServerName as CachingServer
,DATEADD(MINUTE, CONVERT(int, p.RefreshInterval), l.Modified) as Next_Cache_Run
FROM CiresonMaintenance.dbo.CiresonCacheBuilderParam p
LEFT OUTER JOIN cte_LastModified l ON l.ThreadName = p.ThreadName
;
GO

Leigh_Kilday

CiresonMaintenance.dbo.CiresonCacheBuilderParam is automatically generated but I can't find the stored proc at this time. Fields required by the above stored proc could be manually entered.

It contains the following columns:
Name
ThreadName
RefreshInterval
BatchSize
LastUpdate
ServerName

Tom_Hendricks

I do not see CiresonMaintenance.dbo.CiresonCacheBuilderParam anywhere.  This search also came up empty:

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
    FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_DEFINITION LIKE '%CiresonCacheBuilderParam%'
    AND ROUTINE_TYPE='PROCEDURE'
SELECT OBJECT_NAME(id)
    FROM SYSCOMMENTS
    WHERE [text] LIKE '%CiresonCacheBuilderParam%'
    AND OBJECTPROPERTY(id, 'IsProcedure') = 1
    GROUP BY OBJECT_NAME(id)
SELECT OBJECT_NAME(object_id)
    FROM sys.sql_modules
    WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1
    AND definition LIKE '%CiresonCacheBuilderParam%'

So that suggests that the SP you are looking for is not OOB.  Something custom, perhaps?

I am not sure if you were implying that from the start, so I wanted to clarify.

In the meantime, the part of your query that targets dbo.LastModified is still quite useful, so thank you for sharing!

Leigh_Kilday

Yep, that's right, it's a custom table. I'm my last post I included the sort of data we store in there, but you could easily enter it into the query or rdl definition.

Brad_McKenna

@Leigh_Kilday Looks like you all are grabbing the CacheCommands (configuration) in the CacheBuilder configuration folder. Then enumerating this into a table...I like this! Easy way to know what the configuration is on your servers. 
Also out of curiosity, any other cool things you can share that you all do with the CiresonMaintenance dbo that you are using?

Leigh_Kilday

@Brad_McKenna That's right, but I didn't design that part so I'm not sure how it's occurring. The only other things we store in that DB are:

1. Info on the core folders to backup by server group (e.g. Cireson portal boxes need CustomSpace backed up), file types to look for (e.g. *.dll) and some flags.
2. Audit info, like last audit DTG and some stats on process DTG, rows and duration.

Konstantin_Slavin-Bo

@Leigh_Kilday
Thanks for sharing, it looks great!

But - could someone please explain in more detail how to implement this? I'm not really a SQL ops guy; I know the syntax and the concepts, but I'm not really sure on how to implement this.

I need a new database, CiresonMaintenance, which should contain a table CiresonCacheBuilderParam, which has the columns Name, ThreadName, RefreshInterval, BatchSize, LastUpdate, ServerName (all of type nvarchar? Or should LastUpdate be a datetime field?)

The cache builder service account should have read/write access to the new DB / table?

Then I need to create a new stored procedure, right? Under which DB; CiresonMaintenance or ServiceManagement (I'm guessing the former)? In SSMS, it's just right-clicking on Stored Procedures > New Stored Procedure, and replaceing everything in the query editor with what Leigh posted, correct? I'm getting a number of errors here, e.g. 'cte_LastModified has fewer columns than specified in the column list', 'Invalid column name "Name"' in the CASE statement,  and  'The multi-part identifier "l.Modified" could not be bound'.

We're running SQL 11.

Thanks to anyone who has time for this

Adam_Dzyacky

I don't think is as ideal as we want it (lacking some prettier animation and possibly some tighter integration). but using Cireson's Dashboarding tools we can acheive some GUI like integration of this. That shows the Last Modified times from the LastModified table from the ServiceManagement database. However I noticed that the portal seems to be rendering time differently despite how I choose to render it on SQL. Need some more investigation with this, but I hope it moves the needle forward a bit. I suspect if you take @Leigh_Kilday
or @Tom_Hendricks suggestions its possible we may get closer from a GUI perspective.

If you remove my attempt at time conversion in the query, you'll get the datetime in UTC format.

Tom_Hendricks

One thing I have noticed since v8.1.1 (could have been happening earlier) is that sometimes the CacheBuilder service will freeze and stop working.  It also cannot be restarted--it will be stuck in "StartPending" status if you attempt it.

I mention this here because it has been useful to have a few extra columns to give my 24-hr support folks an easy gauge on whether this is happening or not, without having to remote into the server (or call me in the middle of the night!).

I added "NextRun" to show when the next sync time should be, as well as "MinutesUntil".  That last column will show negative numbers if the issue I described above is occurring.  It is easier to see at a glance than looking at the times and guessing whether the time is for today or tomorrow, etc.  The sync intervals I put in the CASE statements are from the Cireson KB.

Here is the query (mostly stolen from @Leigh_Kilday and slightly modified ):

SELECT
    CASE Name
        WHEN 'CI$User' THEN 'USER'
        WHEN 'ConfigurationItem' THEN 'CONF'
        WHEN 'Enumeration' THEN 'ENUM'
        WHEN 'ServiceOffering' THEN 'CATA'
        WHEN'Access_CI$User_WorkItem' THEN 'SCOP'
        WHEN 'WorkItem' THEN 'WORK'
        ELSE Name
    END as ThreadName,
    Name as TableName,
    Modified as LastRun,
    DATEDIFF(MINUTE,Modified,GETUTCDATE()) as MinutesAgo,
    DATEADD(MINUTE,(
        CASE [Name]
            WHEN 'CI$User' THEN 120
            WHEN 'ConfigurationItem' THEN 60
            WHEN 'Enumeration' THEN 1440
            WHEN 'ServiceOffering' THEN 1440
            WHEN 'Access_CI$User_WorkItem' THEN 1440
            WHEN 'WorkItem' THEN 1
        END
        ),Modified) as NextRun,
    DATEDIFF(Minute,GETUTCDATE(),DATEADD(MINUTE,(
        CASE [Name]
            WHEN 'CI$User' THEN 120
            WHEN 'ConfigurationItem' THEN 60
            WHEN 'Enumeration' THEN 1440
            WHEN 'ServiceOffering' THEN 1440
            WHEN 'Access_CI$User_WorkItem' THEN 1440
            WHEN 'WorkItem' THEN 1
        END
        ),Modified)) AS MinutesUntil
    FROM
        ServiceManagement.dbo.LastModified