Adding "Last Month" or "This Month" to a SWQL query

Question

Hello, 
 Here is my query so far: 
 SELECT
[data].[DisplayName] AS [Site],
[data].[ContainerStatus].[PercentAvailability] AS [PercentAvailability],
[data].[InstanceSiteId] AS [InstanceSiteId]
FROM orion.groups AS data
WHERE
((([data].[CustomProperties].[Business_Unit_Groups]) = ('X')) AND ((([data].[ContainerStatus].[PercentAvailability]) != (100)))) 
 
 I am looking to have this query now track the overall availability of the current month and the previous month. They do not have to be tracked in the same query but if someone could provide the additional SWQL line(s) that would pull those date ranges, that would be great!

jm_sysadmin · Accepted Answer

I can't test it, but it would be something like: SELECT
 [data].[DisplayName] AS [Site]
, [data].[ContainerStatus].[PercentAvailability] AS [PercentAvailability]
, [data].[InstanceSiteId] AS [InstanceSiteId]
, Case 
 When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 1 Then 'Jan'
 When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 2 Then 'Feb'
 When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 3 Then 'Mar'
 When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 4 Then 'Apr'
 When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 5 Then 'May'
 When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 6 Then 'Jun'
 When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 7 Then 'Jul'
 When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 8 Then 'Aug'
 When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 9 Then 'Sep'
 When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 10 Then 'Oct'
 When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 11 Then 'Nov'
 When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 12 Then 'DEC'
 Else ''
End as [Month]
, Avg([data].[ContainerStatus].PercentAvailability) as [Average Percent Availability]

FROM orion.groups AS data
WHERE
 ([data].[CustomProperties].[Business_Unit_Groups]) = ('X')
 And MONTHDIFF( DateTrunc('Month', [data].[ContainerStatus].DateTime), Getdate() ) < 2
Group By ContainerID, DateTrunc('Month', [data].[ContainerStatus].DateTime)
Order By ContainerID, DateTrunc('Month', [data].[ContainerStatus].DateTime) 
 I went with the ''orion groups' for the from since we already knew how it was related (by SWQL's built in relationships) to containerstatus. Its easiest to see these connections in SWQL studio, part of the SDK . Also I didn't use the not equal 100% available filter. If you want to add that back to the where clause, it might be worth changing it to : "AND Avg([data].[ContainerStatus].PercentAvailability) != (100)" To filter out months that less that 100% available, but normally if you have a bad month, you want to see it get better, so I dropped it. 
 As for being a SWQL expert, you don't have to be, but if you think you might continue to want less generic and more specific information from Orion, a little practice goes a long way. SWQL is obviously patterned after SQL, but its scope is smaller, and can save you some time (like the built in relationships between tables). 
 KMSigma.SWI put together: https://www.youtube.com/watch?v=8pTQo8Oqk8c&t=4s and its a good place to get started.

jm_sysadmin · Answer

This should get you closer, just merge the two and I bet you get it done. 
 
 SELECT 
ContainerID
, Case 
 When MONTH( DateTrunc('Month', DateTime) ) = 1 Then 'Jan'
 When MONTH( DateTrunc('Month', DateTime) ) = 2 Then 'Feb'
 When MONTH( DateTrunc('Month', DateTime) ) = 3 Then 'Mar'
 When MONTH( DateTrunc('Month', DateTime) ) = 4 Then 'Apr'
 When MONTH( DateTrunc('Month', DateTime) ) = 5 Then 'May'
 When MONTH( DateTrunc('Month', DateTime) ) = 6 Then 'Jun'
 When MONTH( DateTrunc('Month', DateTime) ) = 7 Then 'Jul'
 When MONTH( DateTrunc('Month', DateTime) ) = 8 Then 'Aug'
 When MONTH( DateTrunc('Month', DateTime) ) = 9 Then 'Sep'
 When MONTH( DateTrunc('Month', DateTime) ) = 10 Then 'Oct'
 When MONTH( DateTrunc('Month', DateTime) ) = 11 Then 'Nov'
 When MONTH( DateTrunc('Month', DateTime) ) = 12 Then 'DEC'
 Else ''
End as [Month]
, MONTHDIFF( DateTrunc('Month', DateTime), Getdate() ) as [Months Ago]
, Avg(PercentAvailability) as [Average Percent Available], AVG(PercentMembersAvailability) as [Average Percent Member Available]
FROM Orion.ContainerStatus

Where MONTHDIFF( DateTrunc('Month', DateTime), Getdate() ) < 2
Group By ContainerID, DateTrunc('Month', DateTime)
Order By ContainerID, DateTrunc('Month', DateTime)

Adding "Last Month" or "This Month" to a SWQL query

Top Replies