SWQL Modern Dashboard Error

I am trying to create a modern dashboard table widget with the following query from SWQL Studio.

SELECT TOP 1

       [Node].Caption 
     , [Node].DetailsUrl     
     , [Node].CustomPollerAssignmentOnNode.ID 
     , [Node].CustomPollerAssignmentOnNode.CustomPollerName 
     , [Node].CustomPollerAssignmentOnNode.CurrentValue 
     , [Assign].CustomPollerStatistics.DateTime 
     , [Assign].CustomPollerStatistics.RawStatus 

FROM Orion.Nodes AS [Node]

JOIN Orion.NPM.CustomPollerAssignment AS [Assign] on [Node].CustomPollerAssignmentOnNode.ID = [Assign].ID

WHERE [Node].CustomPollerAssignmentOnNode.ID = 1627 AND [Node].CustomPollerAssignmentOnNode.CustomPollerName LIKE 'InternalTemp%' AND ADDDATE('hour', -24, GETDATE()) <= [Assign].CustomPollerStatistics.DateTime</pre>The query works in SWQL Studio and displaysWhen I paste into a modern dashboard table widget and click validate query I get the green "Provided SWQL query is valid."But when I click show records I get "Provided SWQL query is not valid. Details: RunQuery failed, check fault information. Cannot resolve property DateTime."However, I have been abled to create a classics dashboard custom query table using the same query.What can I do to modify the query for the modern dashboard table widget?

Find more posts tagged with

Accepted answers

All comments

stuartd

I concur, as I get this error:

and I have no idea what that might mean:)

So following as interested in the answer myself.

s-bolyard

Looks to be having issues with:

, [Assign].CustomPollerStatistics.DateTime
, [Assign].CustomPollerStatistics.RawStatus

I haven't figured out why yet, but if you take those two columns out of the query then the output is provided.

jmpenney01

Yes, I commented out these two lines and now it works.

SELECT TOP 1

       [Node].Caption 
     , [Node].DetailsUrl     
     , [Node].CustomPollerAssignmentOnNode.ID 
     , [Node].CustomPollerAssignmentOnNode.CustomPollerName 
     , [Node].CustomPollerAssignmentOnNode.CurrentValue 
     --, [Assign].CustomPollerStatistics.DateTime 
    -- , [Assign].CustomPollerStatistics.RawStatus 

FROM Orion.Nodes AS [Node]

JOIN Orion.NPM.CustomPollerAssignment AS [Assign] on [Node].CustomPollerAssignmentOnNode.ID = [Assign].ID

WHERE [Node].CustomPollerAssignmentOnNode.ID = 1627 AND [Node].CustomPollerAssignmentOnNode.CustomPollerName LIKE 'InternalTemp%' AND ADDDATE('hour', -24, GETDATE()) <= [Assign].CustomPollerStatistics.DateTime</pre>What needs to change to get the original query working?

jmpenney01

I fixed it with this change to the original query.

SELECT TOP 1

       [OnNode].Node.Caption 
     , [OnNode].Node.DetailsUrl     
     , [OnNode].ID 
     , [OnNode].CustomPollerName 
     , [OnNode].CurrentValue 
     , [OnNode].CustomPollerStatistics.DateTime 
     , [OnNode].CustomPollerStatistics.RawStatus 

FROM Orion.NPM.CustomPollerAssignmentOnNode AS [OnNode]

WHERE [OnNode].ID = 1627 AND [OnNode].CustomPollerName LIKE 'InternalTemp%' AND ADDDATE('hour', -24, GETDATE()) <= [OnNode].CustomPollerStatistics.DateTime</pre>

s-bolyard

Ah, ok. Cool!

Great job!

KMSigma.SWI

If you want the most recent (guaranteed), then you should do add an "ORDER BY" clause.

SELECT TOP 1

       [OnNode].Node.Caption 
     , [OnNode].Node.DetailsUrl     
     , [OnNode].ID 
     , [OnNode].CustomPollerName 
     , [OnNode].CurrentValue 
     , [OnNode].CustomPollerStatistics.DateTime 
     , [OnNode].CustomPollerStatistics.RawStatus 

FROM Orion.NPM.CustomPollerAssignmentOnNode AS [OnNode]

WHERE [OnNode].ID = 1627
  AND [OnNode].CustomPollerName LIKE 'InternalTemp%'
  AND ADDDATE('hour', -24, GETDATE()) <= [OnNode].CustomPollerStatistics.DateTime

ORDER BY [OnNode].CustomPollerStatistics.DateTime DESC</pre>