Cireson Analytics support more than just a select statement

Jason_Tayler

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  

Brett_Moffett

I got it to work on v 8.6.2.2016 with some minor changes:

select a.Name0 as 'Computername', comp.Status, ui.ArticleID as 'KBNumber',
CASE
	WHEN comp.status = '0' THEN 'Unknown'
	WHEN comp.status = '1' THEN 'Not required'
	WHEN comp.status = '2' THEN 'Install is Missing'
	WHEN comp.status = '3' THEN 'Installed'
END as 'Installed state'

from [CM_CM1].[dbo].[v_R_System] a
left join [CM_CM1].[dbo].[v_Update_ComplianceStatusAll] comp on a.[ResourceID] = comp.[ResourceID]
join [CM_CM1].[dbo].[v_UpdateInfo] ui on comp.[CI_ID] = ui.[CI_ID]
WHERE ui.ArticleID = '2920698'

You would have to add back in "and Name0 like 'Machine%' and LastStatusChangeTime is not Null" to filter out the results you don't want but that should work fine.

I spell out the DBO's and tables in full because i get too confused otherwise. I think the trick really is the values come back as strings rather than as values so the WHEN Status = '1' is the key.

Brett_Moffett

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?

Brett_Moffett

I got it to work on v 8.6.2.2016 with some minor changes:

select a.Name0 as 'Computername', comp.Status, ui.ArticleID as 'KBNumber',
CASE
	WHEN comp.status = '0' THEN 'Unknown'
	WHEN comp.status = '1' THEN 'Not required'
	WHEN comp.status = '2' THEN 'Install is Missing'
	WHEN comp.status = '3' THEN 'Installed'
END as 'Installed state'

from [CM_CM1].[dbo].[v_R_System] a
left join [CM_CM1].[dbo].[v_Update_ComplianceStatusAll] comp on a.[ResourceID] = comp.[ResourceID]
join [CM_CM1].[dbo].[v_UpdateInfo] ui on comp.[CI_ID] = ui.[CI_ID]
WHERE ui.ArticleID = '2920698'

You would have to add back in "and Name0 like 'Machine%' and LastStatusChangeTime is not Null" to filter out the results you don't want but that should work fine.

I spell out the DBO's and tables in full because i get too confused otherwise. I think the trick really is the values come back as strings rather than as values so the WHEN Status = '1' is the key.

Jason_Tayler

Maaate legend thanks for that works a treat

Jason_Tayler

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_Moffett

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_Workman

Awesome @Brett_Moffett!