0 Replies Latest reply on May 9, 2018 12:10 AM by nvt-lexd

    Custom report on MultiValueStatistics

    nvt-lexd

      We have configured Server and Application Monitoring (SAM) and in the component, we have set multiple statistics and messages.

       

      What we want to achieve is to be able to split each of the "key" out into its own column.

       

      This is what we have currently:

      What we are trying to achieve is the following:

      We manage to do this by using a very long select like the following:

      SELECT RIGHT(LEFT(APM_ComponentAlertVariable.MultiValueStatistics, CHARINDEX(',', APM_ComponentAlertVariable.MultiValueStatistics + ',') -1), Charindex(':', Reverse(LEFT(APM_ComponentAlertVariable.MultiValueStatistics, CHARINDEX(',', APM_ComponentAlertVariable.MultiValueStatistics + ',') -1))) - 1) AS CPU

                , RIGHT(RIGHT(APM_ComponentAlertVariable.MultiValueStatistics, CHARINDEX(',', APM_ComponentAlertVariable.MultiValueStatistics + ',') -1), Charindex(':', Reverse(LEFT(APM_ComponentAlertVariable.MultiValueStatistics, CHARINDEX(',', APM_ComponentAlertVariable.MultiValueStatistics + ',') -1))) - 1) AS UPDATES

       

      With this solution, this is only possible with a predefined number of keys, however we would like to have some sort of SQL function that can automatically split this single column into multiple columns for us automatically based on the "," and ":"

       

      Have any of you guys come across this before or any SQL experts willing to provide a function which we may use?

       

      Thanks!