Home Cireson Uploads

Open Asset On SQL Dashboard View Grid Click

joivan_hedrickjoivan_hedrick Cireson Consultant Advanced IT Monkey ✭✭✭

SQL dashboards allow you to create grids of data from any SQL data source. However, when you click on that item to open it, it will open up the default search page for that asset instead of actually opening up the asset. This now allows you to open up any asset in a new tab.


Download is available on github.

https://github.com/Cireson/Community_OpenAssetOnSQLGridClick

Comments

  • Tom_HendricksTom_Hendricks Customer Super IT Monkey ✭✭✭✭✭

    Add config items and knowledge base articles and I can retire my own solution to this. (https://community.cireson.com/discussion/3582/dashboard-tables-open-items-other-than-work-items#latest)

    Mine could use an update and some optimization, so I wouldn't mind switching.

  • joivan_hedrickjoivan_hedrick Cireson Consultant Advanced IT Monkey ✭✭✭

    Good call. I've added functionality for KBs, Dynamic Data, multiple grids, and a few bug fixes. New version is up on github.

  • Cory_BoweCory_Bowe Customer Adept IT Monkey ✭✭
    edited July 2019

    We've been running a modified version of @Tom_Hendricks solution for a while(thanks Tom!), but this new one is smooth like a rich, fragrant, mahogany. Great work @joivan_hedrick. This is the sauce.

  • Tom_HendricksTom_Hendricks Customer Super IT Monkey ✭✭✭✭✭

    Yes, it's time to move to the version from @joivan_hedrick. I am glad mine could hold some of us over for a while, but I think the true replacement has arrived.

    And thanks for the excellent visual on this, @Cory_Bowe. I unfortunately now find myself wanting new office furniture that I didn't budget for, though!

  • Cory_BoweCory_Bowe Customer Adept IT Monkey ✭✭

    @Tom_Hendricks Imagine a big mahogany executive style desk. Who's in that leather high-backed reclining chair? It's you Tom, you. Smooth R&B rolls out of your speakers in the background. Someone walks through the door into your office. You turn your chair from the window to look at them, fingers tented as you swivel. When they see you, you lock eyes. Theirs get wide. They turn around and walk out of your office without ever asking for anything.

  • Sean_TerrySean_Terry Customer Advanced IT Monkey ✭✭✭

    I’ve managed to replicate the hardware asset example and I have also created a Windows Computer version. However, these have only been via the SCSM databases. I haven’t been able to replicate the solution when focusing on the portal databases. The github notes say:

    v9.3.4.v2

         

          

            // Contributors: Joivan

         

         

            // Description: If a dashboard grid view has a column that contains the name "BaseManagedEntityId" or "ArticleId", and that column has the Entity ID (or Article ID),

         

         

            //                         then this script makes the row clickable, going to the asset object (hardware, location, organization, Dynamic Data/Business service, custom class, KB Article, etc) in a new tab.

         

         

     

      

    It doesn’t look like the column name BaseManagedEntityId comes across to the portal, at least not in a way the code will recognise. As far as I can tell, it appears to be called GUID in the win computer table?

    /****** Script for SelectTopNRows command from SSMS ******/

    SELECT TOP 1000 [Guid]

         ,[DisplayName]

         ,[DNSName]

         ,[IPAddress]

         ,[IsVirtualMachine]

         ,[Notes]

         ,[OUAlertCategory]

         ,[OUAlertCategoryId]

         ,[OUDescription]

         ,[OULocation]

     FROM [SCSM_Portal_Dev].[cachert].[csnCached_MT_Computer]


    The solution looks like a good one if I stuck to the SCSM databases. It’s just a shame that the BaseManagedEntityID columns are not matched in the Cireson Portal databases. If it was in the Computer and Unix tables then I would be onto a winner. Is there a simple tweak or another column I can focus on? Or is there another community solution I’m missing that covers the Portal.

    I wanted to use this particular query in a dashboard and find a way to open the CIs:


    SELECT DisplayName AS "Display Name", OULocation AS "ESX_Host", OUAlertCategory AS Alert_Category, OUHostFunction AS "Host_Function", OUDescription AS "Description"

     FROM [SCSM_Portal_Acct].[cachert].[csnCached_MT_Computer]

     WHERE OULocation IS NOT NULL AND OUAlertCategory IS NOT NULL

    UNION SELECT

    DisplayName AS "Display Name", OULocation AS "ESX Host", OUAlertCategory AS "Alert_Category", OUHostFunction AS "Host_Function", OUDescription AS "Description"

    FROM [SCSM_Portal_Acct].[cachert].[csnCached_MT_Microsoft_Unix_Computer]

    WHERE OULocation IS NOT NULL AND OUAlertCategory IS NOT NULL

  • Justin_WorkmanJustin_Workman Cireson Support Super IT Monkey ✭✭✭✭✭

    The "Guid" field in the cachert tables is the BaseId. I would just alias it as BaseManagedEntityId to make sure the code picks it up.

  • Sean_TerrySean_Terry Customer Advanced IT Monkey ✭✭✭

    Thanks. It was a query from a colleague so thanks for the tip. I've managed to workout alias' and got it working.

  • Sean_TerrySean_Terry Customer Advanced IT Monkey ✭✭✭

    Me again ☺️.

    Our Acct site is working ok after the tweaking the SQL query above. Our page needed a little time to settle as it sometimes opened a search if we clicked on a CI too soon. That wasn't a huge issue for us though.

    We moved the solution into live. It has 4 portal servers instead of 2 in Acct. In Dev tools everything looks ok:-


    Unfortunately, when I click on CIs it reverts to the search. There are no errors in dev tools, web console or cachebuilder. I tried looking in the Platform cache log and I think this error appears when we click on a CI:-

    Main Exception 2020-09-10T09:56:56.0227047+01:00: HResult: -2146232060, Source: .Net SqlClient Data Provider, Message: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding., InnerException: System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out, StackTrace  at PlatformCache.Data.Services.Implementations.Database.<ReadFromParameterizedSql>d__5`1.MoveNext() in D:\a\1\s\Cireson.WebConsole\PlatformCache.Data\Services\Implementations\Database.cs:line 66

     at System.Linq.Enumerable.WhereEnumerableIterator`1.MoveNext()

     at PlatformCache.Data.Models.RuntimeCache.RuntimeCacheUtil`1.<ProcessUpdates>d__11.MoveNext() in D:\a\1\s\Cireson.WebConsole\PlatformCache.Data\Models\RuntimeCache\RuntimeCacheUtil.cs:line 166, TargetSite: Boolean MoveNext()


    Live is particularly large. Are there any recommendations for getting more information or any settings that we can maybe tweak? Implications for changing them? Is this something we can raise as a support ticket?

  • joivan_hedrickjoivan_hedrick Cireson Consultant Advanced IT Monkey ✭✭✭

    I've seen this before in a couple of other large environments. In both scenarios, the SDK call from within the JS was causing SQL to throw an exception similar to ~not enough resources to execute the query plan, or something like that. In your dev environment, the delay was caused by the script making the SDK call and it taking seconds to return the data, as compared to the expected few milliseconds.


    The JS uses an OOB type projection, System.ConfigItem.Projection, which contains 6 components. It uses this to grab the first item to determine what class of item it is. The JS only grabs a single object (plus 6 components) which the SDK doesn't seem to like for some reason. the previous fixes were to create a different empty ConfigItem type projection (attached), and use that instead. Or for even better and significantly faster performance, change the JS to use a a SQL dashboard query and dashboard API call instead of the OOB GetProjectionByCriteria API call. 


    If you go with the type projection route, after you import the sealed MP with the type projection, the only change within the JS that you will have to make is to change the type projection GUID on line 171 (currently has a comment of //System.ConfigItem.Projection). 


Sign In or Register to comment.