Home Analytics

Cireson Analytics support more than just a select statement

Jason_TaylerJason_Tayler Customer IT Monkey ✭
Hi all 

Recently i started to create a dashboard to cater for our Hyper V Cluster patching progress, a lot of orchestrator run books and SCCM task sequences later we were able to automate the system health pre checks node drains, software update installations and Post health checks across 100's of clusters overnight but monitoring the overall job execution was a nightmare, so i created a dashboard that tracked the Orchestrator jobs that write custom information to a custom SQL Table to show the progress, which assisted greatly, however we then wanted to add a chart to show the overall success/still pending software update installation progress for that single plane of glass progress dashboard.

the SQL Query was used

select Name0 as Computername, Status, ui.ArticleID as KBNumber,
CASE 
  WHEN Status = 0 THEN 'Unknown'
  WHEN Status = 1 THEN 'Not required'
  WHEN Status = 2 THEN 'Update is Missing'
  WHEN Status = 3 THEN 'Installed'
  END as 'Installed State'
from v_R_System a
left join v_Update_ComplianceStatusAll comp on a.ResourceID=comp.ResourceID
join v_UpdateInfo ui on comp.CI_ID=ui.CI_ID
where ui.ArticleID = '4103725' and Name0 like 'Machine%' and LastStatusChangeTime is not Null


this resulted in the error 
ERROR [ 308]:  Dashboard: Invalid query. Query must contain select statements only.

i needed to remove the 
Case Statement which resulted in the dashboard showing only the Status Code of 0,1,2,3 


whilst i can add the break down of the meaning of each status in the description it would be great if the analytics portal supported the CASE function

it would be great if we can add these types of SQL Query's to the analytics SQL widgets.

if anyone needs any more information let me know or have managed to work around this issue let me know :) 

Best Answer

Answers

  • Brett_MoffettBrett_Moffett Cireson PACE Super IT Monkey ✭✭✭✭✭
    Hi @Jason_Tayler
    You can use CASE statements in Analytics dashboards. I use them often.
    There is something else going on here that is causing this issue.

    What version of the portal are you currently running?

  • Jason_TaylerJason_Tayler Customer IT Monkey ✭
    Maaate legend thanks for that works a treat
  • Jason_TaylerJason_Tayler Customer IT Monkey ✭

    i am going to assume the ability for a user to say enter a KB number as a input  isn't an available feature just yet


  • Brett_MoffettBrett_Moffett Cireson PACE Super IT Monkey ✭✭✭✭✭
    That is a good assumption.
    However, there is a feature request in play to get such functionality.
    Vote the feature up if it's something you are wanting to see in the product.
    https://community.cireson.com/discussion/2168/other-options-for-filtering-data-on-sql-widgets

  • Justin_WorkmanJustin_Workman Cireson Support Super IT Monkey ✭✭✭✭✭
    Awesome @Brett_Moffett!

Sign In or Register to comment.