• State Verified Answer
  • Locked Locked
  • Replies 9 replies
  • Subscribers 23 subscribers
  • Views 2283 views
  • Users 0 members are here
Options
Related
This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

SWQL String Manipulation in a Custom Resource

sturdyerde

I have the following query successfully working in a custom SWQL resource on a view:

SELECT MultiValueMessages FROM Orion.APM.ComponentAlert WHERE ComponentID = '90224'

The output is a string containing 4 custom message results fron a component monitor, and it looks like this:

ITSecurityOnCall:user1@mail.org, NetworkEngineersOnCall:user2@mail.org, SystemsEngineersOnCall:user3@mail.org, SystemsSpecialistsOnCall:user4@mail.org

This is progress, but we'd like to make it look more presentable. Something with line breaks would be very nice, although I don't see a way to do this with SWQL. I've tried the following syntax, and it does work in SWQL Studio, but does not work within my custom resource.

SELECT 
     ARRAYVALUEAT(SPLITSTRINGTOARRAY(REPLACE(MultiValueMessages,',','|§|§|')),0) AS ITSecurityOnCall,
     ARRAYVALUEAT(SPLITSTRINGTOARRAY(REPLACE(MultiValueMessages,',','|§|§|')),1) AS NetworkEngineerOnCall,
     ARRAYVALUEAT(SPLITSTRINGTOARRAY(REPLACE(MultiValueMessages,',','|§|§|')),2) AS SystemsEngineerOnCall,
     ARRAYVALUEAT(SPLITSTRINGTOARRAY(REPLACE(MultiValueMessages,',','|§|§|')),3) AS SystemsSpecialistOnCall
FROM Orion.APM.ComponentAlert
WHERE ComponentID = '90224'

A simple REPLACE statement that inserts CHAR(10) and/or CHAR(13) would be nice, but SWQL does not support the CHAR statement. Any thoughts or suggestions?

TLDR: How can I manipulate strings from a component monitor's MultiValueMessages data in a custom SWQL resource/widget?

  • 0

    When you say it doesn't work in the custom SWQL resource what do you mean? Do you get an error or not quite the expected output?

  • 0 in reply to mrxinu

    Unfortunately the page simply returned an "error processing the request". I have decided to rewrite my API query that populates the component messages, and updated the SWQL query to pull the new data. Going to let this one hibernate until it becomes an issue elsewhere or until updates for Orion enhance custom query modules.

  • 0

    I have the same issue. The API returns correctly, but the custom query resource throws error  "There was an error processing the request."

  • 0 in reply to mdettenmeier

    What does your SWQL query look like?  When you run the query, do you find any error messages at the end of the log file at C:\ProgramData\SolarWinds\Logs\Orion\OrionWeb.log on the Orion server?

  • 0 in reply to dan_jagnow

    The query is:

    SELECT

        Replace(UpdatedNodes.Vendor, 'Cisco', 'Router') AS Devices, ROUND(UpdatedNodes.IOS_Versions * 100.0 / TotalNodes.Total_Versions * 1.0, 2) AS Percent_Version

    FROM (

    SELECT n.Vendor, COUNT(n.IOSVersion) AS Total_Versions

    FROM Orion.Nodes n

    WHERE

            (n.MachineType LIKE 'Cisco 29%' ) OR

            (n.MachineType LIKE 'Cisco 39%') OR

            (n.MachineType LIKE 'Cisco 4%') OR

            (n.MachineType LIKE 'Cisco ISR4%')

    GROUP BY  n.Vendor

      ) TotalNodes INNER JOIN (

         SELECT n.Vendor, COUNT(n.IOSVersion) as IOS_Versions

            FROM Orion.Nodes n

            WHERE (

             (n.MachineType LIKE 'Cisco 29%' AND n.IOSVersion LIKE '15.6%') OR

             (n.MachineType LIKE 'Cisco 39%' AND n.IOSVersion LIKE '15.6%' ) OR

             (n.MachineType LIKE 'Cisco 4%' AND n.IOSVersion LIKE '16.3%' ) OR

             (n.MachineType LIKE 'Cisco ISR4%' AND n.IOSVersion LIKE '16.3%' )

           )

            GROUP BY  n.Vendor

    ) UpdatedNodes ON TotalNodes.Vendor = UpdatedNodes.Vendor




    These appear to be the relevant errors:

    2019-08-20 08:51:18,734 [277] (3) ERROR SolarWinds.Orion.Web.InformationService.InformationServiceProxy - (null)  mismatched input 'WITH' expecting ')' in Order By clause

    System.ServiceModel.FaultException`1[SolarWinds.InformationService.Contract2.InfoServiceFaultContract]: RunQuery failed, check fault information.

    mismatched input 'WITH' expecting ')' in Order By clause (Fault Detail is equal to InfoServiceFaultContract [ SolarWinds.Data.SWQL.SWQLSyntaxException: mismatched input 'WITH' expecting ')' in Order By clause ---> Antlr.Runtime.MismatchedTokenException: A recognition error occurred.

       at SolarWinds.Data.SWQL.Parser.SWQLParser.MismatchIsUnwantedToken(IIntStream input, Int32 ttype)

       at Antlr.Runtime.BaseRecognizer.RecoverFromMismatchedToken(IIntStream input, Int32 ttype, BitSet follow)

       at Antlr.Runtime.BaseRecognizer.Match(IIntStream input, Int32 ttype, BitSet follow)

       at SolarWinds.Data.SWQL.Parser.SWQLParser.identPrimary()

       --- End of inner exception stack trace ---

       at SolarWinds.Data.SWQL.Parser.SWQLParser.identPrimary()

       at SolarWinds.Data.SWQL.Parser.SWQLParser.primaryExpression()

       at SolarWinds.Data.SWQL.Parser.SWQLParser.unaryExpression()

       at SolarWinds.Data.SWQL.Parser.SWQLParser.multiplyExpression()

       at SolarWinds.Data.SWQL.Parser.SWQLParser.additiveExpression()

       at SolarWinds.Data.SWQL.Parser.SWQLParser.relationalExpression()

       at SolarWinds.Da...).

  • 0 in reply to mdettenmeier

    Thanks.  I was able to reproduce this error (valid SWQL failing when used in a Custom Query resource).  This being tracked with internal issue CORE-13219.

  • 0 in reply to dan_jagnow

    https://thwack.solarwinds.com/people/dan%20jagnow  wrote:

    Thanks.  I was able to reproduce this error (valid SWQL failing when used in a Custom Query resource).  This being tracked with internal issue CORE-13219.

    Unfortunately there is nothing logged in OrionWeb.log on my Orion server. Thank you, @mdettenmeierfor sharing your logs, and thank you, dan jagnow for confirming the issue!

  • 0 in reply to sturdyerde

    Hi,

    Is there any solution. I got same error. 

    Custom SWQL Query:

    SELECT
    o.AlertConfigurations.Name AS [ALERT NAME]
    ,'/Orion/NetPerfMon/ActiveAlertDetails.aspx?NetObject=AAT:' + ToString(o.AlertObjectID) AS [_LinkFor_ALERT NAME]
    ,CASE
    WHEN o.AlertConfigurations.Severity = 2 THEN '/Orion/images/ActiveAlerts/Critical.png'
    WHEN o.AlertConfigurations.Severity = 3 THEN '/Orion/images/ActiveAlerts/Serious.png'
    WHEN o.AlertConfigurations.Severity = 1 THEN '/Orion/images/ActiveAlerts/Warning.png'
    WHEN o.AlertConfigurations.Severity = 0 THEN '/Orion/images/ActiveAlerts/InformationalAlert.png'
    WHEN o.AlertConfigurations.Severity = 4 THEN '/Orion/images/ActiveAlerts/Notice.png'
    END AS [_iconfor_ALERT NAME]
    ,o.EntityCaption AS [ALERT OBJECT]
    ,p.CustomProperties.CI_OWNER_TEAM
    ,o.EntityDetailsURL AS [_LinkFor_ALERT OBJECT]
    ,case WHEN o.RelatedNodeCaption=EntityCaption THEN 'Self'
    When o.RelatedNodeCaption!=EntityCaption THEN RelatedNodeCaption
    End as [RELATED NODE]
    ,o.RelatedNodeDetailsURL AS [_LinkFor_RELATED NODE]
    ,ToLocal(o.AlertActive.TriggeredDateTime) AS [ALERT TRIGGER TIME]
    ,'/Orion/images/StatusIcons/Small-' + p.StatusIcon AS [_IconFor_RELATED NODE]
    ,CASE
    when minutediff(o.AlertActive.TriggeredDateTime,GETUTCDATE())>1440 then (tostring(round(minutediff(o.AlertActive.TriggeredDateTime,GETUTCDATE())/1440.0,1)) + ' Days')
    when minutediff(o.AlertActive.TriggeredDateTime,GETUTCDATE())>60 then (tostring(round(minutediff(o.AlertActive.TriggeredDateTime,GETUTCDATE())/60.0,1)) + ' Hours')
    else (tostring(minutediff(o.AlertActive.TriggeredDateTime,GETUTCDATE())) + ' Minutes')
    end as [Time Active]
    ,aa.AcknowledgedBy
    ,ah.Message as [Note]
    From Orion.AlertActive aa
    join Orion.AlertObjects o on aa.alertobjectid=o.alertobjectid
    LEFT join Orion.Nodes p on p.nodeid=relatednodeid
    left join orion.alerthistory ah on ah.AlertActiveID=aa.AlertActiveID and ah.EventType in (2,3)
    --where (o.AlertConfigurations.Name like '%${SEARCH_STRING}%' or o.RelatedNodeCaption like '%${SEARCH_STRING}%' or o.EntityCaption like '%${SEARCH_STRING}%' or ah.Message like '%${SEARCH_STRING}%')
    ORDER by o.AlertActive.TriggeredDateTime DESC

    Search SWQL Query:

    %${SEARCH_STRING}%
    SELECT
    o.AlertConfigurations.Name AS [ALERT NAME]
    ,'/Orion/NetPerfMon/ActiveAlertDetails.aspx?NetObject=AAT:' + ToString(o.AlertObjectID) AS [_LinkFor_ALERT NAME]
    ,CASE
    WHEN o.AlertConfigurations.Severity = 2 THEN '/Orion/images/ActiveAlerts/Critical.png'
    WHEN o.AlertConfigurations.Severity = 3 THEN '/Orion/images/ActiveAlerts/Serious.png'
    WHEN o.AlertConfigurations.Severity = 1 THEN '/Orion/images/ActiveAlerts/Warning.png'
    WHEN o.AlertConfigurations.Severity = 0 THEN '/Orion/images/ActiveAlerts/InformationalAlert.png'
    WHEN o.AlertConfigurations.Severity = 4 THEN '/Orion/images/ActiveAlerts/Notice.png'
    END AS [_iconfor_ALERT NAME]
    ,o.EntityCaption AS [ALERT OBJECT]
    ,p.CustomProperties.CI_OWNER_TEAM
    ,o.EntityDetailsURL AS [_LinkFor_ALERT OBJECT]
    ,case WHEN o.RelatedNodeCaption=EntityCaption THEN 'Self'
    When o.RelatedNodeCaption!=EntityCaption THEN RelatedNodeCaption
    End as [RELATED NODE]
    ,o.RelatedNodeDetailsURL AS [_LinkFor_RELATED NODE]
    ,ToLocal(o.AlertActive.TriggeredDateTime) AS [ALERT TRIGGER TIME]
    ,'/Orion/images/StatusIcons/Small-' + p.StatusIcon AS [_IconFor_RELATED NODE]
    ,CASE
    when minutediff(o.AlertActive.TriggeredDateTime,GETUTCDATE())>1440 then (tostring(round(minutediff(o.AlertActive.TriggeredDateTime,GETUTCDATE())/1440.0,1)) + ' Days')
    when minutediff(o.AlertActive.TriggeredDateTime,GETUTCDATE())>60 then (tostring(round(minutediff(o.AlertActive.TriggeredDateTime,GETUTCDATE())/60.0,1)) + ' Hours')
    else (tostring(minutediff(o.AlertActive.TriggeredDateTime,GETUTCDATE())) + ' Minutes')
    end as [Time Active]
    ,aa.AcknowledgedBy
    ,ah.Message as [Note]
    From Orion.AlertActive aa
    join Orion.AlertObjects o on aa.alertobjectid=o.alertobjectid
    LEFT join Orion.Nodes p on p.nodeid=relatednodeid
    left join orion.alerthisSELECT
    o.AlertConfigurations.Name AS [ALERT NAME]
    ,'/Orion/NetPerfMon/ActiveAlertDetails.aspx?NetObject=AAT:' + ToString(o.AlertObjectID) AS [_LinkFor_ALERT NAME]
    ,CASE
    WHEN o.AlertConfigurations.Severity = 2 THEN '/Orion/images/ActiveAlerts/Critical.png'
    WHEN o.AlertConfigurations.Severity = 3 THEN '/Orion/images/ActiveAlerts/Serious.png'
    WHEN o.AlertConfigurations.Severity = 1 THEN '/Orion/images/ActiveAlerts/Warning.png'
    WHEN o.AlertConfigurations.Severity = 0 THEN '/Orion/images/ActiveAlerts/InformationalAlert.png'
    WHEN o.AlertConfigurations.Severity = 4 THEN '/Orion/images/ActiveAlerts/Notice.png'
    END AS [_iconfor_ALERT NAME]
    ,o.EntityCaption AS [ALERT OBJECT]
    ,p.CustomProperties.CI_OWNER_TEAM
    ,o.EntityDetailsURL AS [_LinkFor_ALERT OBJECT]
    ,case WHEN o.RelatedNodeCaption=EntityCaption THEN 'Self'
    When o.RelatedNodeCaption!=EntityCaption THEN RelatedNodeCaption
    End as [RELATED NODE]
    ,o.RelatedNodeDetailsURL AS [_LinkFor_RELATED NODE]
    ,ToLocal(o.AlertActive.TriggeredDateTime) AS [ALERT TRIGGER TIME]
    ,'/Orion/images/StatusIcons/Small-' + p.StatusIcon AS [_IconFor_RELATED NODE]
    ,CASE
    when minutediff(o.AlertActive.TriggeredDateTime,GETUTCDATE())>1440 then (tostring(round(minutediff(o.AlertActive.TriggeredDateTime,GETUTCDATE())/1440.0,1)) + ' Days')
    when minutediff(o.AlertActive.TriggeredDateTime,GETUTCDATE())>60 then (tostring(round(minutediff(o.AlertActive.TriggeredDateTime,GETUTCDATE())/60.0,1)) + ' Hours')
    else (tostring(minutediff(o.AlertActive.TriggeredDateTime,GETUTCDATE())) + ' Minutes')
    end as [Time Active]
    ,aa.AcknowledgedBy
    ,ah.Message as [Note]
    From Orion.AlertActive aa
    join Orion.AlertObjects o on aa.alertobjectid=o.alertobjectid
    LEFT join Orion.Nodes p on p.nodeid=relatednodeid
    left join orion.alerthistory ah on ah.AlertActiveID=aa.AlertActiveID and ah.EventType in (2,3)
    where (o.AlertConfigurations.Name like '%${SEARCH_STRING}%' or o.RelatedNodeCaption like '%${SEARCH_STRING}%' or o.EntityCaption like '%${SEARCH_STRING}%' or ah.Message like '%${SEARCH_STRING}%' or p.CustomProperties.CI_OWNER_TEAM like %${SEARCH_STRING}%')
    ORDER by o.AlertActive.TriggeredDateTime DESCtory ah on ah.AlertActiveID=aa.AlertActiveID and ah.EventType in (2,3)
    where (o.AlertConfigurations.Name like '%${SEARCH_STRING}%' or o.RelatedNodeCaption like '%${SEARCH_STRING}%' or o.EntityCaption like '%${SEARCH_STRING}%' or ah.Message like '%${SEARCH_STRING}%')
    ORDER by o.AlertActive.TriggeredDateTime DESC

    OrionWeb.log :

    ERROR SolarWinds.Orion.Web.InformationService.InformationServiceProxy - (null) mismatched input '%' expecting 'SELECT'
    System.ServiceModel.FaultException`1[SolarWinds.InformationService.Contract2.InfoServiceFaultContract]: RunQuery failed, check fault information.
    mismatched input '%' expecting 'SELECT' (Fault Detail is equal to InfoServiceFaultContract [ SolarWinds.Data.SWQL.SWQLSyntaxException: mismatched input '%' expecting 'SELECT' ---> Antlr.Runtime.MismatchedTokenException: A recognition error occurred.
    at SolarWinds.Data.SWQL.Parser.SWQLParser.MismatchIsUnwantedToken(IIntStream input, Int32 ttype)
    at Antlr.Runtime.BaseRecognizer.RecoverFromMismatchedToken(IIntStream input, Int32 ttype, BitSet follow)
    at Antlr.Runtime.BaseRecognizer.Match(IIntStream input, Int32 ttype, BitSet follow)
    at SolarWinds.Data.SWQL.Parser.SWQLParser.selectClause()
    --- End of inner exception stack trace ---
    at SolarWinds.Data.SWQL.Parser.SWQLParser.selectClause()
    at SolarWinds.Data.SWQL.Parser.SWQLParser.selectFrom()
    at SolarWinds.Data.SWQL.Parser.SWQLParser.queryRule()
    at SolarWinds.Data.SWQL.Parser.SWQLParser.selectStatement()
    at SolarWinds.Data.SWQL.Parser.SWQLParser.statement()
    at SolarWinds.Data.SWQL.SWQLParser.Parse(String query)
    at SolarWinds.Data.Query.Engine.QueryProcessor.CompileQueryExecution...).

  • 0 in reply to mmozaydin

    Do you have ${SEARCH_STRING} hanging out above your SELECT? If so, that needs to be removed.

    mrxinu_0-1602158786618.png

SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 195,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.

SolarWinds Customer Success Center Certification Media Vault Link Accounts
About THWACK Blogs For Government Edit Settings Free Tools & Trials
Legal Documents Terms of Use Privacy California Privacy Rights Security Information
©2024 SolarWinds Worldwide, LLC. All Rights Reserved.