SWQL error when pulling data for 7 days

Trying to get average Availability for a KPI widget for the last 7 days and using below Query

SELECT AVG(E0.[AvgResponseTime]) AS Counter
FROM Orion.Nodes AS E0
WHERE ( ( E0.[CustomProperties].[x_x_x] = 'abc' )) AND Date.Time BETWEEN GETDATE() - 7 AND GETDATE()

But I am getting below error when validating the query:

Provided SWQL query is not valid. Details: RunQuery failed, check fault information. Entity Date not found in FROM clause for property Date.Time

Can anyone tell here what is wrong here ? If I remove the Time condition , SWQL runs just fine.

Find more posts tagged with

swql query

KPI Widget

Accepted answers

All comments

adam.beedell

Date.Time BETWEEN GETDATE() - 7 AND GETDATE()

Date.time is looking for a column called "time" in a table called "date" and neither of those exist, you want something like

Ex.Observationtimestamp BETWEEN addday(-7, GETDATE()) AND GETDATE()

or

Ex.Observationtimestamp BETWEEN addday(-7, GETUTCDATE()) AND GETUTCDATE()

or perhaps just

Ex.Observationtimestamp > addday(-7, GETUTCDATE())

Now i'm using "Ex" there because there's no history stuff in the orion.nodes table either, so you'd need to join one of the history ones

Also to get AVG to work you need to GROUP BY everything bar the thing being averaged (or other aggregates)

Also ( ( E0.[CustomProperties].[x_x_x] = 'abc' )) doesnt need the brackets

heldy

This below also worked for me too

SELECT
AVG (AvgResponseTime) AS AvgResponseTime
FROM
Orion.ResponseTime
WHERE
NodeID in (
SELECT
E0.NodeID
FROM
Orion.Nodes AS E0
WHERE
(
(
E0.[CustomProperties].[x_x_x] = 'abc'
)
)
)
AND
ObservationTimestamp > addday(
-7,
GETUTCDATE()
)