Home› Service Manager Portal Feature Requests
We appreciate you taking the time to vote and add your suggestions to make our products awesome! Your request will be submitted to the community for review and inclusion into the backlog.

We recommend reviewing what is submitted before posting, in case your idea has already been submitted by another community member. If it has been submitted, vote for that existing feature request (by clicking the up arrow) to increase its opportunity of being added to Cireson solutions.

For more information around feature requests in the Cireson Community click here.
Options

Cachebuilder Status & Timeline Display

Tony_CollettTony_Collett Cireson Support Super IT Monkey ✭✭✭✭✭
in Service Manager Portal Feature Requests
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. 
30
Up Down
30 votes

Submitted · Last Updated

Comments

  • Options
    Leigh_KildayLeigh_Kilday Member Ninja IT Monkey ✭✭✭✭
    edited February 2017
    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.
  • Options
    Mike_StormsMike_Storms Customer Adept IT Monkey ✭✭

    Leigh_Kilday is this something you could share?

  • Options
    Tom_HendricksTom_Hendricks Customer Super IT Monkey ✭✭✭✭✭
    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.
  • Options
    Leigh_KildayLeigh_Kilday Member Ninja IT Monkey ✭✭✭✭
    Mike_Storms said:

    Leigh_Kilday is this something you could share?

    Sure thing. It'll take some time to export from our network.
  • Options
    Ben_TeyBen_Tey Partner Adept IT Monkey ✭✭
    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
  • Options
    Konstantin_Slavin-BoKonstantin_Slavin-Bo Customer Ninja IT Monkey ✭✭✭✭
    @Leigh_Kilday
    That sound very nice, we would also greatly appreciate if you share it!

  • Options
    Leigh_KildayLeigh_Kilday Member Ninja IT Monkey ✭✭✭✭
    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
  • Options
    Leigh_KildayLeigh_Kilday Member Ninja IT Monkey ✭✭✭✭
    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
  • Options
    Tom_HendricksTom_Hendricks Customer Super IT Monkey ✭✭✭✭✭
    edited May 2017
    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!
  • Options
    Leigh_KildayLeigh_Kilday Member Ninja IT Monkey ✭✭✭✭
    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.
  • Options
    Brad_McKennaBrad_McKenna Customer Advanced IT Monkey ✭✭✭
    @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?

  • Options
    Leigh_KildayLeigh_Kilday Member Ninja IT Monkey ✭✭✭✭
    @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.
  • Options
    Konstantin_Slavin-BoKonstantin_Slavin-Bo Customer Ninja IT Monkey ✭✭✭✭
    @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 :smile:
  • Options
    Adam_DzyackyAdam_Dzyacky Product Owner Contributor Monkey ✭✭✭✭✭
    edited August 2017
    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.




  • Options
    Tom_HendricksTom_Hendricks Customer Super IT Monkey ✭✭✭✭✭
    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










Sign In or Register to comment.