Open Asset On SQL Dashboard View Grid Click

joivan_hedrick

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

Tom_Hendricks

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_hedrick

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

Cory_Bowe

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_Hendricks

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_Bowe

@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_Terry

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_Workman

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_Terry

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

Sean_Terry

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_hedrick

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

Dust.ConfigItem.TypeProjections.xml.txt

Brett_Etzel

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_Etzel

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_hedrick

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_Etzel

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_hedrick

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_Etzel

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

Chris_Jordan

Hi Joivan,

Looks like the changes to SQL widget in the latest version of the portal no longer has any clickable elements to it.

What's unfortunate for me is every single one of our views is done in SQL using that method to open all CIs.

Hoping there is some sort of workaround, thanks.

Tony_Collett

It appears that due to a change in version 10.4.2 (or 10.2.6 BASELINE) of the Portal, that this script will no longer work correctly.

I do not have any additional information on this at this time.

joivan_hedrick

Good find on this one. I have a hunch on how to address this one, but it may be a short bit before I can modify the script. Stay tuned! 

Chris_Jordan

Hey Joivan, thanks for the update.

I assume the PR resolution is final in terms of the official product? It's a shame this functionality didn't make it into the product, it gives you the user views with relationships you can't get with any other option as far as I'm aware.

PR104574 SMP: Dashboards CI or custom CI SQL table / SQL chart widget forwarding to CI form is not working

joivan_hedrick

This is now fixed in the CustomSpace script. Kudos to @Shane_White for examining the code changes in the recent release and adapting the JavaScript in this customization to work with version 10.5.1. Newest script version is available in the github repo.

Brett_Etzel

Hi all,

This customization is working great for Asset and Knowledge Article grid views (when using SQL a table widget), but is there a simple way to exclude a list of URLs where you don't want the code to apply?

Thanks!