SWQL Help with Date

KITGuy

Hi SWQL boffins,

I have a swql query that gets the availability of databases for the previous month. I want to add a coloum that gives the time period like you can with the Dynamic Query Builder like:

My swql is:

SELECT
SA.Node.Caption AS [Server]
,SA.Node.DetailsUrl as [_linkfor_Server]
,SA.InstanceName AS [SQL Instance]
,SA.Databases.DisplayName AS [Database]
,SA.Databases.StatusDescription AS [Status]
,B.Availability AS [AVG Availability last Month]
,SA.Node.CustomProperties.ServerClass As [Server Class]
FROM Orion.APM.SqlServerApplication AS SA
INNER JOIN (
SELECT
A.DatabaseID
,AVG(A.Av*100) AS Availability
-- ,DateTrunc('day',A.TimeStamp) as day --Enable to get availability per day
FROM(
SELECT
DS.DatabaseID AS [DatabaseID]
,DS.TimeStamp
,CASE
WHEN DS.Status>5 THEN 0
ELSE 1
END AS Av

FROM Orion.APM.SqlDatabaseStatus AS DS
WHERE
DS.TimeStamp>ADDMONTH(-1,GETUTCDATE()) --Set days back to calculate avg availability
) AS A
GROUP BY A.DatabaseID --, DateTrunc('day',A.TimeStamp) --Enable to get availability per day
) AS B ON SA.databases.DatabaseID=B.databaseid
Where SA.Node.CustomProperties.ServerClass = '1'

Lofstrand

This is as far as I got on this one. Don't give you the month name but the number of the month, and year.

SELECT
    SA.Node.Caption AS [Server]
    ,SA.Node.DetailsUrl as [_linkfor_Server]
    ,SA.InstanceName AS [SQL Instance]
    ,SA.Databases.DisplayName AS [Database]
    ,SA.Databases.StatusDescription AS [Status]
    ,B.Availability AS [AVG Availability last Month]
    --,SA.Node.CustomProperties.ServerClass As [Server Class]
    ,CONCAT(B.[Year],'-',B.[month]) AS TheMonth
FROM Orion.APM.SqlServerApplication AS SA
INNER JOIN (
    SELECT
    A.DatabaseID
    ,AVG(A.Av*100) AS Availability
    ,Month(A.TimeStamp) as [month] 
    ,YEAR(A.Timestamp) AS [Year]
    FROM(
        SELECT
        DS.DatabaseID AS [DatabaseID]
        ,DS.TimeStamp
        ,CASE
            WHEN DS.Status>5 THEN 0
            ELSE 1
        END AS Av
        FROM Orion.APM.SqlDatabaseStatus AS DS
        WHERE
            DS.TimeStamp>ADDMONTH(-1,GETUTCDATE()) --Set days back to calculate avg availability
) AS A
GROUP BY A.DatabaseID ,YEAR(A.Timestamp), MONTH(A.TimeStamp) 
) AS B ON SA.databases.DatabaseID=B.databaseid
--Where SA.Node.CustomProperties.ServerClass = '1'