Home Analyst Portal

Workflow Status through Dashboarding

Adam_DzyackyAdam_Dzyacky Product Owner Contributor Monkey ✭✭✭✭✭
edited August 2017 in Analyst Portal
Curious if anyone has the classic "workflow subscription status" query working on Cireson Dashboards? I know SQL widgets are a bit iffy with certain SQL features, but there are a lot of things happening in this SQL query that the portal seems defeated by

Comments

  • Adam_DzyackyAdam_Dzyacky Product Owner Contributor Monkey ✭✭✭✭✭
    edited August 2017
    I've just answered my own question here, looks like "Order By" is the hangup here. Removed this and now can dashboard workflow subscription status on v8+ of the portal.

    Correction: I got a query working with Order By on accident. It actually looks like this is related to the "Reset State" button mentioned below.
  • Tom_HendricksTom_Hendricks Customer Super IT Monkey ✭✭✭✭✭
    That did not fix it for me.  I get nearly 200 completely empty rows.  The column headers are correct, at least.



    Exporting them to Excel proves that they are indeed empty.  I have never seen this before.

    If I run the query in SQL Server, it returns the same number of rows, but the columns actually have data in them.
  • Adam_DzyackyAdam_Dzyacky Product Owner Contributor Monkey ✭✭✭✭✭
    edited September 2017
    Tom, here's the exact workflow status query that I'm using (however slightly modified to make it work) within my v8+ environment. I will say that once I got it working I had to hit "Reset State" and close/reopen my browser before I got results. I also introduced a new row for "seconds behind" in the event that several workflows were tied in terms of Minutes Behind.

    DECLARE @MaxState INT, @MaxStateDate Datetime, @Delta INT, @Language nvarchar(3)
     SET @Delta = 0
     SET @Language = 'ENU'
     SET @MaxState = (
        SELECT MAX(EntityTransactionLogId)
        FROM EntityChangeLog WITH(NOLOCK)
     )
     SET @MaxStateDate = (
    	SELECT TimeAdded 
    	FROM EntityTransactionLog
    	WHERE EntityTransactionLogId = @MaxState
    )
    
    SELECT
        LT.LTValue AS 'Display Name',
    		S.State AS 'Current Workflow Watermark',
    	@MaxState AS 'Current Transaction Log Watermark',
    	DATEDIFF(mi,(SELECT TimeAdded 
    					FROM EntityTransactionLog WITH(NOLOCK)
    					WHERE EntityTransactionLogId = S.State), @MaxStateDate) AS 'Time Behind in Minutes',
    	DATEDIFF(ss,(SELECT TimeAdded 
    	FROM EntityTransactionLog WITH(NOLOCK)
    	WHERE EntityTransactionLogId = S.State), @MaxStateDate) AS 'Time Behind in Seconds',
    	S.EventCount,
    	S.LastNonZeroEventCount,
    	R.RuleName AS 'MP Rule Name',
        MT.TypeName AS 'Source Class Name',
        S.LastModified AS 'Rule Last Modified',
    	S.IsPeriodicQueryEvent AS 'Is Periodic Query Subscription',
        R.RuleEnabled AS 'Rule Enabled',
    	R.RuleID
    	
     FROM CmdbInstanceSubscriptionState AS S WITH(NOLOCK)
     LEFT OUTER JOIN Rules AS R
        ON S.RuleId = R.RuleId
     LEFT OUTER JOIN ManagedType AS MT
        ON S.TypeId = MT.ManagedTypeId
     LEFT OUTER JOIN LocalizedText AS LT
    	ON R.RuleId = LT.MPElementId
     WHERE
        S.State <= @MaxState - @Delta 
    	AND R.RuleEnabled <> 0 
    	AND LT.LTStringType = 1
    	AND LT.LanguageCode = @Language
    	AND S.IsPeriodicQueryEvent = 0


  • Leigh_KildayLeigh_Kilday Member Ninja IT Monkey ✭✭✭✭
    We just link off to a Power BI server (SSRS works just as well) for system health reports like this. The advantage is I can set parameters for filters and the auto-refresh interval, which (IIRC) you can't do in a portal dashboard.

    Other reports where I lean away from portal dashboards are our SMA dashboard (we don't use the WAP) and Cireson Cache Builder status.
  • Adam_DzyackyAdam_Dzyacky Product Owner Contributor Monkey ✭✭✭✭✭
    @Leigh_Kilday if we can't figure out how to JavaScript click the refresh button then there are always browser addons for page refreshes :)
  • Tom_HendricksTom_Hendricks Customer Super IT Monkey ✭✭✭✭✭
    @Adam_Dzyacky, I appreciate that you mentioned the reset button.  I never clicked it because why would I reset something that had never been set?

    This is yet another case of logic getting in the way, because clicking Reset State did the trick perfectly.

    Our queries are essentially identical, but I have shortened some of the column names so that they display better.
  • Adam_DzyackyAdam_Dzyacky Product Owner Contributor Monkey ✭✭✭✭✭
    edited September 2017
    I decided to properly setup a health dashboard and save myself from running SSMS. This however is now a page I wouldn't want to refresh on any regular intervals because I can't conjur up a good need to be sitting on this page 24/7 much less constantly pegging core SCSM, rather just a good troubleshooting page that I can access quickly through a web ui should I need to. But most importantly, something that others can access without needing access to the backend DB.

    I've typed it before and I'll type it again, this dashboard feature rocks.



    Also, if you're looking for the "loudest objects in SCSM" query here it is. For the unaware, this query will take longer than those other two to run entirely based on the size of your SCSM deployment.

    SELECT TOP 25 BME.FullName, COUNT(1) as 'ECL'
    FROM EntityChangeLog AS ECL WITH(NOLOCK)
    JOIN BaseManagedEntity AS BME WITH(NOLOCK)
        ON ECL.EntityId = BME.BaseManagedEntityId
    WHERE RelatedEntityId IS NULL
    GROUP BY BME.FullName
    ORDER BY COUNT(1) DESC<br>
  • Leigh_KildayLeigh_Kilday Member Ninja IT Monkey ✭✭✭✭
    @Adam_Dzyacky, send your query to your DBAs and have them index the tables for you. Never settle for slow DBs!
  • Adam_DzyackyAdam_Dzyacky Product Owner Contributor Monkey ✭✭✭✭✭
    I've actually performed said indexes, verified them with DBAs, and even obtained the "what to index" from a PSDiag. The best time I can get on the ECL/large objects query is about 10 seconds. If I've missed something here, I'd be eager to hear what you've done!
  • Brad_McKennaBrad_McKenna Customer Advanced IT Monkey ✭✭✭
    @Adam_Dzyacky - What's the secret sauce to get your beautiful workflow status query to run via 7.4?
    I continue to feel a bit behind the curve on the SQL Tables, as it is so easy to get what you want in management studio. Then it seems to get lost in translation when using the SQL Table widget. 

    Also, thank you for sharing. Greatly appreciate it, and timing is perfect as we experienced issues just last week.
  • Adam_DzyackyAdam_Dzyacky Product Owner Contributor Monkey ✭✭✭✭✭
    edited September 2017
    As far as I know you can't do it, I think the ability to query ServiceManager directly is introduced in v8+ of the portal. But I certainly invite corrections from Cireson/anyone.

    Also, this is not my query. All credit absolutely goes to Travis Wright for this - https://blogs.technet.microsoft.com/servicemanager/2013/01/14/troubleshooting-workflow-performance-and-delays/


    The following screenshot from a v8+ SQL Table Widget here allows you to pick the data source.



  • Brad_McKennaBrad_McKenna Customer Advanced IT Monkey ✭✭✭
    Oh I know you are using Travis's query and just changed it a bit :) I had just thought a change that you had made was the difference with it working in v8+.

    Querying ServiceManager db works in v7.4.2012.9 as I was able to create a SQL Table Widget for the 'loudest objects in Service Manager' which is another query from Travis Wright's queries for troubleshooting workflow performance. Granted it seems to take around 20-30 seconds for the query to return, however it does end up returning. Oddly enough when the page loads it states 'no results' until the results are finally displayed.

    For Workflow Status it simply states Empty with no data being presented:

  • Adam_DzyackyAdam_Dzyacky Product Owner Contributor Monkey ✭✭✭✭✭
    edited September 2017
    Ha, alright. Good. If this is going to be cemented on the internet it pays to be clear on these topics :)

    As I pointed out to Tom earlier in the thread, I also returned empty results. Hitting "Reset State" cleared this up for us.
  • Brad_McKennaBrad_McKenna Customer Advanced IT Monkey ✭✭✭
    Apologies if I came off offensive/antagonistic @Adam_Dzyacky , was not my intention at all!

    I identified that the issue was due to the portal I was on. The issue resided in my 'pre-prod' site, but not my 'prod' site.

    The good news is with v7.4.2012.9 you are able to query ServiceManager and even the SCSM DW (we use for dashboards that leverage custom fields) using the SQL Table Widget. My issue once on my 'prod' portal of v7.4.2012.9 resided in spaces within the alias/as statement. I thought I converted all spaces to underlines, however I missed one :(

    Thank you very much for all of your contributions Adam. It is greatly appreciated!!
  • Adam_DzyackyAdam_Dzyacky Product Owner Contributor Monkey ✭✭✭✭✭
    Oh no, wasn't taken that way at all @brad_mckenna ! You're totally fine! We're cool!

    Glad to help. Related to this, there are few threads open on workflow troubleshooting performance. I'd be interested to hear if you arrive at any root cause analysis. I think there is a lot data missing in this area that stands to benefit us all. While there is going to be variance between environments, part of me would like to believe there is perhaps something common.
  • Karen_Bruster1Karen_Bruster1 Member IT Monkey ✭
    @Adam_Dzyacky I was wondering if you could share your Cache Query too, I am not ashamed to say that I am just a dabbler. 
  • Adam_DzyackyAdam_Dzyacky Product Owner Contributor Monkey ✭✭✭✭✭
    Of course - 
    SELECT Name, CONVERT(datetime, 
                   SWITCHOFFSET(CONVERT(datetimeoffset, 
                                        Modified), 
                                DATENAME(TzOffset, SYSDATETIMEOFFSET()))) 
           AS 'Last Sync Time'
      FROM LastModified


  • Karen_Bruster1Karen_Bruster1 Member IT Monkey ✭
    Of course - 
    SELECT Name, CONVERT(datetime, 
                   SWITCHOFFSET(CONVERT(datetimeoffset, 
                                        Modified), 
                                DATENAME(TzOffset, SYSDATETIMEOFFSET()))) 
           AS 'Last Sync Time'
      FROM LastModified



    Are you using ServiceManger or Cireson to run the Query?
  • Adam_DzyackyAdam_Dzyacky Product Owner Contributor Monkey ✭✭✭✭✭
    Cireson. So point that query at your ServiceManagement db.
  • Karen_Bruster1Karen_Bruster1 Member IT Monkey ✭

    @Adam_Dzyacky That's what I thought.

    So far the only one that has been displaying any results is the Loudest Object query. But I will keep trying.
    Thank you for your helpful posts.

  • Brad_McKennaBrad_McKenna Customer Advanced IT Monkey ✭✭✭
    @Karen_Bruster1 try remove the spaces or replacing the spaces with _ in Last Sync Time.
    Within our environment, we still struggle with the spaces in the alias, therefore we have to use the character or no space.
    SELECT Name, CONVERT(datetime, 
                   SWITCHOFFSET(CONVERT(datetimeoffset, 
                                        Modified), 
                                DATENAME(TzOffset, SYSDATETIMEOFFSET()))) 
           AS 'LastSyncTime'
      FROM <span>LastModified
    
    or
    </span><br><div class="Quote"><pre><code>SELECT Name, CONVERT(datetime, 
                   SWITCHOFFSET(CONVERT(datetimeoffset, 
                                        Modified), 
                                DATENAME(TzOffset, SYSDATETIMEOFFSET()))) 
           AS 'Last_Sync_Time'
      FROM LastModified
  • Adam_DzyackyAdam_Dzyacky Product Owner Contributor Monkey ✭✭✭✭✭
    @Karen_Bruster1  and @Brad_McKenna  - can you try the queries as is, save them, then use the "Reset State" fix as mentioned above, then post back here? I'm personally interested to know if we're all discovering nuances across v7.x and v8.x or this can simply be traced back to the aforementioned "Reset State" fix.
  • Brad_McKennaBrad_McKenna Customer Advanced IT Monkey ✭✭✭
    @Adam_Dzyacky For my environment the Reset State does not fix anything. I know instantly after adding/editing a query if the data will display. Sometimes I am missing a whole column, and sometimes all the data is blank. A bit odd.
  • Karen_Bruster1Karen_Bruster1 Member IT Monkey ✭

    @Adam_Dzyacky and @Brad_McKenna - I tried the Reset State and it did not work, so I tried Brad's two sugestions and they did not work. I do have v 7.4.2012.3, though so maybe that is the issue.

Sign In or Register to comment.