Home Cireson Uploads
image


IT Monkey will place code here as examples of what Cireson's consulting team has to offer as well as examples for public consumption to benefit the Microsoft System Center community as a whole.

DISCLAIMER

All files and projects located here come as is and without any warranty or support. We will attempt to improve the projects as time goes on based on customer and community demand. Comments and improvements are welcome as well as customization requests. Your use of these Cireson Uploads is subject to our Terms of Use.


Cireson's support team has no information on these projects outside of what you have available and will not provide support for these enhancements, extensions, and scripts.

Dont forget to checkout solutions uploaded by our customers, partners and community members here.

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). 


  • Brett_EtzelBrett_Etzel Customer IT Monkey ✭

    Hi Joivan,

    I'm trying this out for a custom view of our Knowledge Articles (SQL Table Widget).

    When I click on the row in the grid, I land on a blank page.

    Any ideas?

    Thanks, Brett

  • Brett_EtzelBrett_Etzel Customer IT Monkey ✭

    Joivan, It appears that my SQL query is the problem.

    When I use the query in your example (select ArticleId, Title, Abstract, ViewCount from KnowledgeArticle) it works great, but when I use the query below, I get the blank page (or a page error on variations). Maybe I need to somehow account for how I'm changing some field names?

    Great customization!


    SELECT TOP (100) PERCENT dbo.KnowledgeArticle.ArticleID, dbo.KnowledgeArticle.Title, cat.DisplayString AS Category, dbo.KnowledgeArticle.VendorArticleID AS Vendor, dbo.KnowledgeArticle.CreatedDate AS Date_Created, owner.DisplayName AS Article_Owner, dbo.KnowledgeArticle.LastModifiedDate AS Date_Modified, lstmodr.DisplayName AS Modified_By, stat.DisplayString AS Status, dbo.KnowledgeArticle.Keywords, dbo.KnowledgeArticle.Popularity AS Rating FROM dbo.KnowledgeArticle INNER JOIN dbo.DisplayString AS cat ON cat.ElementID = dbo.KnowledgeArticle.Category AND cat.LocaleID = 'enu' INNER JOIN dbo.DisplayString AS stat ON stat.ElementID = dbo.KnowledgeArticle.Status AND stat.LocaleID = 'enu' INNER JOIN dbo.CI$User AS lstmodr ON lstmodr.Id = dbo.KnowledgeArticle.LastModifiedBy INNER JOIN dbo.CI$User AS owner ON owner.Id = dbo.KnowledgeArticle.Owner ORDER BY Rating DESC, dbo.KnowledgeArticle.ArticleID

  • joivan_hedrickjoivan_hedrick Cireson Consultant Advanced IT Monkey ✭✭✭

    Hi Brett,

    The JavaScript is looking for the exact case sensitive text "ArticleId" instead of "ArticleID" when determining the article page to redirect to. Though it does properly hide the column regardless, which is kind of misleading on my part. In the meantime, change that first column to ArticleId and it should start working for you.

  • Brett_EtzelBrett_Etzel Customer IT Monkey ✭

    Joivan,

    Thank you that fixed it. I hope you have time for one more question.

    I'm trying to have the JavaScript rebuild the URL to point to "View" instead of "Edit". I've tried updating all the URL parts to use "/KnowledgeBase/View/" instead of "/KnowledgeBase/Edit/", but it it still goes to "/KnowledgeBase/Edit/" (restarted CacheBuilder, IIS, etc. services). I'm obviously missing something that's key for changing the URL to go to the "View" form (change the GUID?, etc.).

    Thanks again for your help with the ArticleId.

    Brett

  • joivan_hedrickjoivan_hedrick Cireson Consultant Advanced IT Monkey ✭✭✭

    At first glance, this one seems like a JavaScript + browser caching issue. After saving the updated .JS file, can you open the page via InPrivate or Incognito mode and see if it persists?

  • Brett_EtzelBrett_Etzel Customer IT Monkey ✭

    Joivan,

    My bad. Apologies for wasting your time.

    I thought I had flushed cache in Chrome. Obviously not (or not at the right time) because when I combined a CacheBuilder restart with an increment of Versions.json, it started to use "view" instead of "edit".

    Thank you for your help getting this customization squared away. I like it a lot. I believe this will be helpful for my team. I'm not the biggest fan of the native KA landing page. Landing on a page that lists all Articles seems better (I've enabled a grid search field at the top of our grids). Going forward, this could make it possible to create KA views based on IT team, end-user, etc.

    Good stuff. I wish Cireson would incorporate this functionality into the production Portal. Ideally, they'd extend it way beyond to essentially turn the KA functionality into a full-fledged Knowledge Management platform, like what https://www.knowledge-architecture.com/ does.

    Thanks again,

    Brett

Sign In or Register to comment.