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.

Help Modifying a custom report to give me the ability to enter specific dates (pull historical) and then default to the last "Month"

HI Guys,

I have had NCM installed and monitoring for a few months now but due to issues never got my "Uptime" report working.  I worked with presales before purchasing and they helped get a demo report working off a custom field using SWQL queries.

The problem with these queries is that the WHERE statement is hardcoded to last 30 days and I lost the ability to change the table to use "Last Month" or any of the other prebuilt options in report builder.  

How can I change this SWQL statement to be able to hard code specific dates so I can get my missing historical data?  

Then I want to schedule the report automatically to report on the last month at the first of the new month.  How do I get it to properly do 30/31 days?

I have two custom queries as follows right now that both need modified.

SELECT 
     Orin.CustomProperties.AvailabilityReportingGroups as [Team]
,    OriN.Caption as [Node Name]
,    ToString(Round(AVG(OriN.ResponseTimeHistory.Availability),2))+'%' as Avail
     FROM Orion.Nodes OriN
WHERE OriN.ResponseTimeHistory.DateTime>ADDDAY(-30, GETDATE())
GROUP BY Orin.CustomProperties.AvailabilityReportingGroups, OriN.Caption
ORDER BY Orin.CustomProperties.AvailabilityReportingGroups, OriN.Caption DESC

SELECT
      OriC.AvailabilityReportingGroups as [Team]
,     ToString(Round(AVG(OriC.Node.ResponseTimeHistory.Availability),2))+'%' as [Availabilty]
FROM Orion.NodesCustomProperties OriC
WHERE OriC.AvailabilityReportingGroups IS NOT NULL and OriC.node.ResponseTimeHistory.DateTime>ADDDAY(-30, GETDATE())
GROUP BY OriC.AvailabilityReportingGroups

Any help is appreciated!

Parents
  • In the web reporting you can use the ${FromTime} and ${ToTime} values. [Explained in more details on Using SWQL/SQL with web-based reports (solarwinds.com)]

    Your first report query would change to this: [UPDATED]

    SELECT [Nodes].CustomProperties.AvailabilityReportingGroups as [Team]
         , [Nodes].Caption as [Node Name]
         , CONCAT(ROUND(AVG( [Nodes].ResponseTimeHistory.Availability ), 2), '%') as Avail
    FROM Orion.Nodes AS [Nodes]
    -- Old WHERE clause
    --WHERE [Nodes].ResponseTimeHistory.DateTime>ADDDAY(-30, GETDATE())
    -- New WHERE clause (my preferred way)
    WHERE [Nodes].ResponseTimeHistory.DateTime BETWEEN ${FromTime} AND ${ToTime}
    -- Other way
    -- WHERE [Nodes].ResponseTimeHistory.DateTime >= ${FromTime}
    --   AND [Nodes].ResponseTimeHistory.DateTime < ${ToTime}
    GROUP BY [Nodes].CustomProperties.AvailabilityReportingGroups
           , [Nodes].Caption
    ORDER BY [Nodes].CustomProperties.AvailabilityReportingGroups
           , [Nodes].Caption DESC

    Note: I changed a few things to get in line with the way that I prefer my queries, but functionally, it's the same.

    Same note applies to your second query. [UPDATED]

    SELECT [NodeCPs].AvailabilityReportingGroups as [Team]
         , CONCAT(ROUND(AVG( [NodeCPs].Node.ResponseTimeHistory.Availability ), 2 ), '%') as [Availabilty]
    FROM Orion.NodesCustomProperties [NodeCPs]
    --Old WHERE clause
    --WHERE [NodeCPs].AvailabilityReportingGroups IS NOT NULL 
    --  AND [NodeCPs].Node.ResponseTimeHistory.DateTime>ADDDAY(-30, GETDATE())
    -- New WHERE clause (my preferred way)
    WHERE ISNULL([NodeCPs].AvailabilityReportingGroups, '') <> ''
      AND [NodeCPs].Node.ResponseTimeHistory.DateTime BETWEEN ${FromTime} AND ${ToTime}
    -- Other way
    -- WHERE ISNULL([NodeCPs].AvailabilityReportingGroups, '') <> ''
    --   AND [NodeCPs].Node.ResponseTimeHistory.DateTime >= ${FromTime}
    --   AND [NodeCPs].Node.ResponseTimeHistory.DateTime < ${ToTime}
    GROUP BY [NodeCPs].AvailabilityReportingGroups

  • When pasting into my report i simply get "Query is not valid".  

    Also to note the option to select a time frame is gone, so I assume i have to hard code it somehow?

  • I downloaded the text and saved it as a XML and imported it into my reports and I can confirm that yours does have the selector.

    Can you provide an XML of my report above that I could attempt the same thing for?  That is if you are getting the selector when trying on our side?

  • Actually I really like your report.  I wonder if it can be modified to group like the one I am attempting.  

    If i change yours to last month it no longer pulls back data though and its way more complicated SWQL!

  • Pretty easily actually.  The SWQL behind the scenes is fairly intense (because of all the math it does).  I try to remember to make notes of how to incorporate custom properties (even if it's in comments)

    SELECT area of the query, I even left notes on how to "extend" this to include custom properties (line 9-10).

    SELECT [Nodes].Caption
         , [Nodes].DetailsUrl
         , [Nodes].VendorInfo.Icon
         , [ResponseTime].Day
         , [ResponseTime].Availability
         , [Downtime].MinutesDown
         , [Downtime].CalcAvailability
         , [Events].RebootCount
    -- To add Node Custom Properties use:
    -- , [Nodes].CustomProperties.<Custom Property Name>
    FROM Orion.Nodes AS [Nodes]
    -- truncated for readability --
    --   query continues below   --

    You should be just able to add your specific custom property and keep the same data set.

    SELECT [Nodes].Caption
         , [Nodes].DetailsUrl
         , [Nodes].VendorInfo.Icon
         , [ResponseTime].Day
         , [ResponseTime].Availability
         , [Downtime].MinutesDown
         , [Downtime].CalcAvailability
         , [Events].RebootCount
    -- To add Node Custom Properties use:
         , [Nodes].CustomProperties.AvailabilityReportingGroup
    FROM Orion.Nodes AS [Nodes]
    -- truncated for readability --
    --   query continues below   --

    Then you could edit the table to add the new field (AvailabilityReportingGroup) to the report and at the bottom change the "Day" grouping to that custom property.

  • First: Support responded and said no, you are wrong and they still will not help.  So seems like that is a no go.  

    Second:

    Some odd results with this.

    SELECT [Nodes].Caption
         , [Nodes].DetailsUrl
         , [Nodes].VendorInfo.Icon
         , [ResponseTime].Day
         , [ResponseTime].Availability
         , [Downtime].MinutesDown
         , [Downtime].CalcAvailability
         , [Events].RebootCount
    -- To add Node Custom Properties use:
          ,[Nodes].CustomProperties.AvailabilityReportingGroups
    FROM Orion.Nodes AS [Nodes]
    INNER JOIN (
         SELECT [RT].NodeID
              , DateTrunc('day', [RT].DateTime) AS [Day]
              , AVG([RT].Availability * 1.0) AS Availability
         FROM Orion.ResponseTime AS [RT]
         GROUP BY DateTrunc('day', [RT].DateTime)
              , [RT].NodeID
         ) AS [ResponseTime]
         ON [Nodes].NodeID = [ResponseTime].NodeID
    INNER JOIN (
         SELECT [DT].NodeId
              , DateTrunc('day', [DT].DateTimeUntil) AS [Day]
              , SUM([DT].TotalDurationMin) AS [MinutesDown]
              , ( ( 1.0 - ( SUM([DT].TotalDurationMin) / 1440.0 ) ) * 100.0 ) AS [CalcAvailability]
         FROM Orion.NetObjectDowntime AS [DT]
         INNER JOIN Orion.StatusInfo AS [SI]
              ON [DT].State = [SI].StatusId
                   AND [SI].StatusName = 'Down' -- Where the status is 'Down' (not 'Critical', 'Warning', etc.)
         WHERE [DT].EntityType = 'Orion.Nodes' -- Only show me nodes
              AND [DT].DateTimeUntil IS NOT NULL --Indicates that it's no longer down
         GROUP BY DateTrunc('day', [DT].DateTimeUntil)
              , [DT].NodeId
         ) AS [Downtime]
         ON [ResponseTime].NodeId = [Downtime].NodeID
              AND [ResponseTime].Day = [Downtime].Day
    RIGHT JOIN (
         SELECT [E].Nodes.NodeID
              , COUNT([E].EventID) AS [RebootCount]
              , DATETRUNC('day', [E].EventTime) AS [Day]
         FROM Orion.Events AS [E]
         WHERE [E].EventTypeProperties.Name = 'Node rebooted'
         --   AND [E].Nodes.CustomProperties.<Custom Property Name> = <Custom Property Value>
         GROUP BY DATETRUNC('day', [E].EventTime)
              , [E].Nodes.NodeID
         ) AS [Events]
         ON [ResponseTime].NodeID = [Events].NodeID
              AND [ResponseTime].Day = [Events].Day
    WHERE [Nodes].ObjectSubType <> 'ICMP'
    -- Required for Orion Reports
       AND [ResponseTime].Day BETWEEN ${FromTime} AND ${ToTime}
    -- To filter on Node Custom Properties add
    --  AND [Node].CustomProperties.<Custom Property Name> = <Custom Property Value>
    

    Preview results gives nothing back in the SWQL view. If i keep the results set to "Last Week" and go the "Edit Table" I have added the AvailabilityReportingGroups field.

    If i run the report I only get 10 results but it will not display the field and I do see it grouping by my custom field.

    Then it gets weirder and it only has sporadic days being displayed for these:

    Now if i change it to "Last Week" it shows even less.  If i change it to last month - it does not load anything at all.

    My only theory is that you are using ResponseTime and my report was using responsetimehistory?  Maybe it clears out values?

  • I mean, this is a downtime report, so if there's no downtime, it shouldn't exist on the report.  That said it does seem like unusual data responses.

    How many nodes do you have in your environment?  Because there's so much behind the scenes in this report, we may be running into a timeout issue.

  • Well true.  I guess its probably simpler to just focus on my report and try to figure out why it wont work with the fields you added.  When I imported your report it does have the field.

    Any chance you can export an xml of my data with it working on your side?

  • Any chance you can export an xml of my data with it working on your side?

    Do you mean, me work with your report?

    If so, you can export it and attach it to this thread. Exporting and Importing Shared Content - THWACK Documentation - THWACK Command Center - THWACK - SolarWinds Community

  • <Report xmlns="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models" xmlns:i="http://www.w3.org/2001/XMLSchema-instance"><Category>Daily Node Availability</Category><Configs xmlns:a="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Data"><a:ConfigurationData i:type="b:TableConfiguration" xmlns:b="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Tables"><a:DisplaySubTitle/><a:DisplayTitle>Node Availability - This Month</a:DisplayTitle><a:RefId>de05db73-fa01-48f5-bc16-6582a4fee4c9</a:RefId><b:Columns><b:TableColumn><b:CellStyle xmlns:c="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Styles"><c:BackgroundColor i:nil="true"/><c:Borders i:nil="true"/><c:DisplayName i:nil="true"/><c:Font i:nil="true"/><c:Padding i:nil="true"/><c:RefId>00000000-0000-0000-0000-000000000000</c:RefId><c:TextAlign>Left</c:TextAlign><c:ZebraBackgroundColor i:nil="true"/></b:CellStyle><b:DataColumnName i:nil="true"/><b:DisplayName>Team</b:DisplayName><b:Field xmlns:c="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Selection"><c:DataTypeInfo><a:ApplicationType i:nil="true"/><a:DataType><a:Data>System.String</a:Data></a:DataType><a:DeclType>Enumerated</a:DeclType><a:DefaultAggregation>NotSpecified</a:DefaultAggregation><a:DefaultDataUnitId i:nil="true"/><a:DefaultTransformId i:nil="true"/><a:Description>Group for Availability Reporting</a:Description><a:IsFavorite>true</a:IsFavorite><a:IsFilterBy>true</a:IsFilterBy><a:IsGroupBy>true</a:IsGroupBy><a:IsInherited>false</a:IsInherited><a:IsManaged>false</a:IsManaged><a:IsStatistic>false</a:IsStatistic><a:PreviewValue>Dev Servers</a:PreviewValue><a:Units/><a:UtcTimeIsInLocalTime>true</a:UtcTimeIsInLocalTime></c:DataTypeInfo><c:DisplayName>Team</c:DisplayName><c:NavigationPath i:nil="true"/><c:OwnerDisplayName i:nil="true"/><c:RefID><c:Data>Orion.NodesCustomProperties|AvailabilityReportingGroups|Team</c:Data></c:RefID></b:Field><b:FixedWidth i:nil="true"/><b:HeaderStyle xmlns:c="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Styles"><c:BackgroundColor i:nil="true"/><c:Borders i:nil="true"/><c:DisplayName i:nil="true"/><c:Font i:nil="true"/><c:Padding i:nil="true"/><c:RefId>00000000-0000-0000-0000-000000000000</c:RefId><c:TextAlign>Left</c:TextAlign><c:ZebraBackgroundColor i:nil="true"/></b:HeaderStyle><b:IsHTMLTagsAllowed>false</b:IsHTMLTagsAllowed><b:IsHidden>false</b:IsHidden><b:PercentWidth i:nil="true"/><b:Presenters i:nil="true" xmlns:c="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Presentation"/><b:PropertyName>Team</b:PropertyName><b:RefId>671d2a94-db1f-0f37-df15-05e6fbfaa881</b:RefId><b:Summary><b:Calculation>NotSpecified</b:Calculation><b:CellStyle i:nil="true" xmlns:c="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Styles"/></b:Summary><b:TransformId/><b:ValidRange>NotSpecified</b:ValidRange></b:TableColumn><b:TableColumn><b:CellStyle xmlns:c="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Styles"><c:BackgroundColor i:nil="true"/><c:Borders i:nil="true"/><c:DisplayName i:nil="true"/><c:Font i:nil="true"/><c:Padding i:nil="true"/><c:RefId>00000000-0000-0000-0000-000000000000</c:RefId><c:TextAlign>Left</c:TextAlign><c:ZebraBackgroundColor i:nil="true"/></b:CellStyle><b:DataColumnName i:nil="true"/><b:DisplayName>Node Name</b:DisplayName><b:Field xmlns:c="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Selection"><c:DataTypeInfo><a:ApplicationType i:nil="true"/><a:DataType><a:Data>System.String</a:Data></a:DataType><a:DeclType>Text</a:DeclType><a:DefaultAggregation>NotSpecified</a:DefaultAggregation><a:DefaultDataUnitId i:nil="true"/><a:DefaultTransformId i:nil="true"/><a:Description/><a:IsFavorite>false</a:IsFavorite><a:IsFilterBy>true</a:IsFilterBy><a:IsGroupBy>true</a:IsGroupBy><a:IsInherited>false</a:IsInherited><a:IsManaged>true</a:IsManaged><a:IsStatistic>false</a:IsStatistic><a:PreviewValue>LS1-CR-9300.kleintools.com</a:PreviewValue><a:Units/><a:UtcTimeIsInLocalTime>false</a:UtcTimeIsInLocalTime></c:DataTypeInfo><c:DisplayName>Node Name</c:DisplayName><c:NavigationPath i:nil="true"/><c:OwnerDisplayName i:nil="true"/><c:RefID><c:Data>Orion.Nodes|Caption|Node Name</c:Data></c:RefID></b:Field><b:FixedWidth i:nil="true"/><b:HeaderStyle xmlns:c="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Styles"><c:BackgroundColor i:nil="true"/><c:Borders i:nil="true"/><c:DisplayName i:nil="true"/><c:Font i:nil="true"/><c:Padding i:nil="true"/><c:RefId>00000000-0000-0000-0000-000000000000</c:RefId><c:TextAlign>Left</c:TextAlign><c:ZebraBackgroundColor i:nil="true"/></b:HeaderStyle><b:IsHTMLTagsAllowed>false</b:IsHTMLTagsAllowed><b:IsHidden>false</b:IsHidden><b:PercentWidth i:nil="true"/><b:Presenters i:nil="true" xmlns:c="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Presentation"/><b:PropertyName>Node Name</b:PropertyName><b:RefId>226e1460-911e-9064-a0fd-70288ce6a1ac</b:RefId><b:Summary><b:Calculation>NotSpecified</b:Calculation><b:CellStyle i:nil="true" xmlns:c="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Styles"/></b:Summary><b:TransformId/><b:ValidRange>NotSpecified</b:ValidRange></b:TableColumn><b:TableColumn><b:CellStyle xmlns:c="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Styles"><c:BackgroundColor i:nil="true"/><c:Borders i:nil="true"/><c:DisplayName i:nil="true"/><c:Font i:nil="true"/><c:Padding i:nil="true"/><c:RefId>00000000-0000-0000-0000-000000000000</c:RefId><c:TextAlign>Left</c:TextAlign><c:ZebraBackgroundColor i:nil="true"/></b:CellStyle><b:DataColumnName i:nil="true"/><b:DisplayName>Avail</b:DisplayName><b:Field xmlns:c="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Selection"><c:DataTypeInfo><a:ApplicationType i:nil="true"/><a:DataType><a:Data>System.String</a:Data></a:DataType><a:DeclType>Text</a:DeclType><a:DefaultAggregation>NotSpecified</a:DefaultAggregation><a:DefaultDataUnitId i:nil="true"/><a:DefaultTransformId i:nil="true"/><a:Description i:nil="true"/><a:IsFavorite i:nil="true"/><a:IsFilterBy i:nil="true"/><a:IsGroupBy i:nil="true"/><a:IsInherited i:nil="true"/><a:IsManaged i:nil="true"/><a:IsStatistic i:nil="true"/><a:PreviewValue i:nil="true"/><a:Units i:nil="true"/><a:UtcTimeIsInLocalTime i:nil="true"/></c:DataTypeInfo><c:DisplayName>Avail</c:DisplayName><c:NavigationPath i:nil="true"/><c:OwnerDisplayName i:nil="true"/><c:RefID><c:Data>db|Avail|computed</c:Data></c:RefID></b:Field><b:FixedWidth i:nil="true"/><b:HeaderStyle xmlns:c="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Styles"><c:BackgroundColor i:nil="true"/><c:Borders i:nil="true"/><c:DisplayName i:nil="true"/><c:Font i:nil="true"/><c:Padding i:nil="true"/><c:RefId>00000000-0000-0000-0000-000000000000</c:RefId><c:TextAlign>Left</c:TextAlign><c:ZebraBackgroundColor i:nil="true"/></b:HeaderStyle><b:IsHTMLTagsAllowed>false</b:IsHTMLTagsAllowed><b:IsHidden>false</b:IsHidden><b:PercentWidth i:nil="true"/><b:Presenters i:nil="true" xmlns:c="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Presentation"/><b:PropertyName>Avail</b:PropertyName><b:RefId>535509cd-7e35-61aa-13e7-11ef297d4a23</b:RefId><b:Summary><b:Calculation>NotSpecified</b:Calculation><b:CellStyle i:nil="true" xmlns:c="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Styles"/></b:Summary><b:TransformId/><b:ValidRange>NotSpecified</b:ValidRange></b:TableColumn></b:Columns><b:DefaultStyle i:nil="true" xmlns:c="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Styles"/><b:Filter xmlns:c="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Selection"><c:Expression i:nil="true"/><c:Limit><c:Count i:nil="true"/><c:Mode>ShowAll</c:Mode><c:OrionServerIDsToIgnore i:nil="true"/><c:Percentage i:nil="true"/></c:Limit></b:Filter><b:Indents/><b:Sorts xmlns:c="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Selection"/><b:SummarizeMode>NoDataSummarization</b:SummarizeMode><b:TimeField i:nil="true" xmlns:c="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Selection"/></a:ConfigurationData><a:ConfigurationData i:type="b:TableConfiguration" xmlns:b="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Tables"><a:DisplaySubTitle/><a:DisplayTitle>Custom Table</a:DisplayTitle><a:RefId>c91201da-62ac-43b6-be28-4f2c34effdae</a:RefId><b:Columns><b:TableColumn><b:CellStyle xmlns:c="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Styles"><c:BackgroundColor i:nil="true"/><c:Borders i:nil="true"/><c:DisplayName i:nil="true"/><c:Font i:nil="true"/><c:Padding i:nil="true"/><c:RefId>00000000-0000-0000-0000-000000000000</c:RefId><c:TextAlign>Left</c:TextAlign><c:ZebraBackgroundColor i:nil="true"/></b:CellStyle><b:DataColumnName i:nil="true"/><b:DisplayName>Team</b:DisplayName><b:Field xmlns:c="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Selection"><c:DataTypeInfo><a:ApplicationType i:nil="true"/><a:DataType><a:Data>System.String</a:Data></a:DataType><a:DeclType>Enumerated</a:DeclType><a:DefaultAggregation>NotSpecified</a:DefaultAggregation><a:DefaultDataUnitId i:nil="true"/><a:DefaultTransformId i:nil="true"/><a:Description>Group for Availability Reporting</a:Description><a:IsFavorite>true</a:IsFavorite><a:IsFilterBy>true</a:IsFilterBy><a:IsGroupBy>true</a:IsGroupBy><a:IsInherited>false</a:IsInherited><a:IsManaged>false</a:IsManaged><a:IsStatistic>false</a:IsStatistic><a:PreviewValue>Dev Servers</a:PreviewValue><a:Units/><a:UtcTimeIsInLocalTime>true</a:UtcTimeIsInLocalTime></c:DataTypeInfo><c:DisplayName>Team</c:DisplayName><c:NavigationPath i:nil="true"/><c:OwnerDisplayName i:nil="true"/><c:RefID><c:Data>Orion.NodesCustomProperties|AvailabilityReportingGroups|Team</c:Data></c:RefID></b:Field><b:FixedWidth i:nil="true"/><b:HeaderStyle xmlns:c="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Styles"><c:BackgroundColor i:nil="true"/><c:Borders i:nil="true"/><c:DisplayName i:nil="true"/><c:Font i:nil="true"/><c:Padding i:nil="true"/><c:RefId>00000000-0000-0000-0000-000000000000</c:RefId><c:TextAlign>Left</c:TextAlign><c:ZebraBackgroundColor i:nil="true"/></b:HeaderStyle><b:IsHTMLTagsAllowed>false</b:IsHTMLTagsAllowed><b:IsHidden>false</b:IsHidden><b:PercentWidth i:nil="true"/><b:Presenters i:nil="true" xmlns:c="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Presentation"/><b:PropertyName>Team</b:PropertyName><b:RefId>7f84e83c-34f3-eb10-1ce1-d4e3ae115684</b:RefId><b:Summary><b:Calculation>NotSpecified</b:Calculation><b:CellStyle i:nil="true" xmlns:c="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Styles"/></b:Summary><b:TransformId/><b:ValidRange>NotSpecified</b:ValidRange></b:TableColumn><b:TableColumn><b:CellStyle xmlns:c="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Styles"><c:BackgroundColor i:nil="true"/><c:Borders i:nil="true"/><c:DisplayName i:nil="true"/><c:Font i:nil="true"/><c:Padding i:nil="true"/><c:RefId>00000000-0000-0000-0000-000000000000</c:RefId><c:TextAlign>Left</c:TextAlign><c:ZebraBackgroundColor i:nil="true"/></b:CellStyle><b:DataColumnName i:nil="true"/><b:DisplayName>Availabilty</b:DisplayName><b:Field xmlns:c="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Selection"><c:DataTypeInfo><a:ApplicationType i:nil="true"/><a:DataType><a:Data>System.Double</a:Data></a:DataType><a:DeclType>Text</a:DeclType><a:DefaultAggregation>NotSpecified</a:DefaultAggregation><a:DefaultDataUnitId i:nil="true"/><a:DefaultTransformId i:nil="true"/><a:Description i:nil="true"/><a:IsFavorite i:nil="true"/><a:IsFilterBy i:nil="true"/><a:IsGroupBy i:nil="true"/><a:IsInherited i:nil="true"/><a:IsManaged i:nil="true"/><a:IsStatistic i:nil="true"/><a:PreviewValue i:nil="true"/><a:Units i:nil="true"/><a:UtcTimeIsInLocalTime i:nil="true"/></c:DataTypeInfo><c:DisplayName>Availabilty</c:DisplayName><c:NavigationPath i:nil="true"/><c:OwnerDisplayName i:nil="true"/><c:RefID><c:Data>db|Availabilty|computed</c:Data></c:RefID></b:Field><b:FixedWidth i:nil="true"/><b:HeaderStyle xmlns:c="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Styles"><c:BackgroundColor i:nil="true"/><c:Borders i:nil="true"/><c:DisplayName i:nil="true"/><c:Font i:nil="true"/><c:Padding i:nil="true"/><c:RefId>00000000-0000-0000-0000-000000000000</c:RefId><c:TextAlign>Left</c:TextAlign><c:ZebraBackgroundColor i:nil="true"/></b:HeaderStyle><b:IsHTMLTagsAllowed>false</b:IsHTMLTagsAllowed><b:IsHidden>false</b:IsHidden><b:PercentWidth i:nil="true"/><b:Presenters i:nil="true" xmlns:c="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Presentation"/><b:PropertyName>Availabilty</b:PropertyName><b:RefId>35b57fa2-b1f2-3996-79c7-60540744cda7</b:RefId><b:Summary><b:Calculation>NotSpecified</b:Calculation><b:CellStyle i:nil="true" xmlns:c="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Styles"/></b:Summary><b:TransformId/><b:ValidRange>NotSpecified</b:ValidRange></b:TableColumn></b:Columns><b:DefaultStyle i:nil="true" xmlns:c="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Styles"/><b:Filter xmlns:c="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Selection"><c:Expression i:nil="true"/><c:Limit><c:Count i:nil="true"/><c:Mode>ShowAll</c:Mode><c:OrionServerIDsToIgnore i:nil="true"/><c:Percentage i:nil="true"/></c:Limit></b:Filter><b:Indents/><b:Sorts xmlns:c="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Selection"/><b:SummarizeMode>NoDataSummarization</b:SummarizeMode><b:TimeField i:nil="true" xmlns:c="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Selection"/></a:ConfigurationData></Configs><DataSources xmlns:a="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Selection"><a:DataSource><a:CommandText>SELECT [Nodes].CustomProperties.AvailabilityReportingGroups as [Team]
         , [Nodes].Caption as [Node Name]
         , CONCAT(ROUND(AVG( [Nodes].ResponseTimeHistory.Availability ), 2), '%') as Avail
    FROM Orion.Nodes AS [Nodes]
    -- Old WHERE clause
    --WHERE [Nodes].ResponseTimeHistory.DateTime&gt;ADDDAY(-30, GETDATE())
    -- New WHERE clause (my preferred way)
    WHERE [Nodes].ResponseTimeHistory.DateTime BETWEEN ${FromTime} AND ${ToTime}
    -- Other way
    -- WHERE [Nodes].ResponseTimeHistory.DateTime &gt;= ${FromTime}
    --   AND [Nodes].ResponseTimeHistory.DateTime &lt; ${ToTime}
    GROUP BY [Nodes].CustomProperties.AvailabilityReportingGroups
           , [Nodes].Caption
    ORDER BY [Nodes].CustomProperties.AvailabilityReportingGroups
           , [Nodes].Caption DESC</a:CommandText><a:DynamicSelectionType>Undefined</a:DynamicSelectionType><a:EntityUri xmlns:b="http://schemas.microsoft.com/2003/10/Serialization/Arrays"/><a:Filter i:nil="true"/><a:MasterEntity>orion.nodes</a:MasterEntity><a:Name>Individual Nodes</a:Name><a:NetObjectId/><a:RefId>2c39dc54-9b5a-4824-a282-c487c78a93e2</a:RefId><a:Type>CustomSWQL</a:Type></a:DataSource><a:DataSource><a:CommandText>SELECT [NodeCPs].AvailabilityReportingGroups as [Team]
         , CONCAT(ROUND(AVG( [NodeCPs].Node.ResponseTimeHistory.Availability ), 2 ), '%') as [Availabilty]
    FROM Orion.NodesCustomProperties [NodeCPs]
    --Old WHERE clause
    --WHERE [NodeCPs].AvailabilityReportingGroups IS NOT NULL 
    --  AND [NodeCPs].Node.ResponseTimeHistory.DateTime&gt;ADDDAY(-30, GETDATE())
    -- New WHERE clause (my preferred way)
    WHERE ISNULL([NodeCPs].AvailabilityReportingGroups, '') &lt;&gt; ''
      AND [NodeCPs].Node.ResponseTimeHistory.DateTime BETWEEN ${FromTime} AND ${ToTime}
    -- Other way
    -- WHERE ISNULL([NodeCPs].AvailabilityReportingGroups, '') &lt;&gt; ''
    --   AND [NodeCPs].Node.ResponseTimeHistory.DateTime &gt;= ${FromTime}
    --   AND [NodeCPs].Node.ResponseTimeHistory.DateTime &lt; ${ToTime}
    GROUP BY [NodeCPs].AvailabilityReportingGroups</a:CommandText><a:DynamicSelectionType>Undefined</a:DynamicSelectionType><a:EntityUri xmlns:b="http://schemas.microsoft.com/2003/10/Serialization/Arrays"/><a:Filter i:nil="true"/><a:MasterEntity>Orion.Nodes</a:MasterEntity><a:Name>Group Availability</a:Name><a:NetObjectId/><a:RefId>0a627900-13d1-4592-b273-68c270287656</a:RefId><a:Type>CustomSWQL</a:Type></a:DataSource></DataSources><Description>Displays the Average Availability for each node for this month</Description><Footer xmlns:a="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Layout"><a:CustomText>© SolarWinds Worldwide, LLC. All Rights Reserved.</a:CustomText><a:ShowCustomText>true</a:ShowCustomText><a:ShowPageNumber>true</a:ShowPageNumber><a:ShowTimestamp>true</a:ShowTimestamp><a:Visible>true</a:Visible></Footer><Header xmlns:a="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Layout"><a:Logo>standard</a:Logo><a:SubTitle/><a:Title>KT Node and Group Availability - Last 30 days</a:Title><a:Visible>true</a:Visible></Header><LimitationCategory>Default Folder</LimitationCategory><ModuleTitle i:nil="true"/><Name>KT Node and Group Availability - Last 30 days</Name><OrionFeatureName i:nil="true"/><PageLayout xmlns:a="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Layout"><a:Height>0</a:Height><a:Orientation i:nil="true"/><a:PublishingType>web</a:PublishingType><a:Size i:nil="true"/><a:Units i:nil="true"/><a:Width>960</a:Width></PageLayout><ReportGuid>765121c3-d364-45fa-b6d5-9b4943d1f431</ReportGuid><Sections xmlns:a="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Layout"><a:Section><a:BorderStyle xmlns:b="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Styles"><b:Bottom i:nil="true"/><b:Color i:nil="true"/><b:Left i:nil="true"/><b:Right i:nil="true"/><b:Top i:nil="true"/></a:BorderStyle><a:Columns><a:SectionColumn><a:BorderStyle xmlns:b="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Styles"><b:Bottom i:nil="true"/><b:Color i:nil="true"/><b:Left i:nil="true"/><b:Right i:nil="true"/><b:Top i:nil="true"/></a:BorderStyle><a:Cells><a:SectionCell><a:Config i:nil="true" xmlns:b="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Data"/><a:ConfigId>c91201da-62ac-43b6-be28-4f2c34effdae</a:ConfigId><a:DataSelectionRefId>0a627900-13d1-4592-b273-68c270287656</a:DataSelectionRefId><a:DisplayName>Custom Table</a:DisplayName><a:RefId>36f5cf0c-e765-46b0-b604-a0e51885c6e1</a:RefId><a:RenderProvider>SolarWinds.Reporting,Table</a:RenderProvider><a:TimeframeRefId>ffffffff-ffff-ffff-ffff-ffffffffffff</a:TimeframeRefId></a:SectionCell><a:SectionCell><a:Config i:nil="true" xmlns:b="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Data"/><a:ConfigId>de05db73-fa01-48f5-bc16-6582a4fee4c9</a:ConfigId><a:DataSelectionRefId>2c39dc54-9b5a-4824-a282-c487c78a93e2</a:DataSelectionRefId><a:DisplayName>Custom Table</a:DisplayName><a:RefId>fb5b97a9-c0ec-4b56-96c1-3d9b797373ca</a:RefId><a:RenderProvider>SolarWinds.Reporting,Table</a:RenderProvider><a:TimeframeRefId>ffffffff-ffff-ffff-ffff-ffffffffffff</a:TimeframeRefId></a:SectionCell></a:Cells><a:ColumnGutter i:nil="true"/><a:PercentWidth>100</a:PercentWidth><a:PixelWidth i:nil="true"/><a:RefId>92cdd64a-3959-488a-b38f-2a0ce3d842fa</a:RefId><a:Subtitle/><a:Title/></a:SectionColumn></a:Columns><a:RefId>7efe6654-8645-4936-98dd-6bc1b9617e89</a:RefId></a:Section></Sections><TimeFrames xmlns:a="http://schemas.datacontract.org/2004/07/SolarWinds.Reporting.Models.Timing"><a:TimeFrame><a:DisplayName>This Month</a:DisplayName><a:IsStatic>false</a:IsStatic><a:RefId>c1abbcf6-41b8-46c1-aaaf-5d7aeed64bf3</a:RefId><a:Relative><a:NamedTimeFrame>ThisMonth</a:NamedTimeFrame><a:Unit>Month</a:Unit><a:UnitCount>1</a:UnitCount></a:Relative><a:Static i:nil="true"/></a:TimeFrame><a:TimeFrame><a:DisplayName>Last Month</a:DisplayName><a:IsStatic>false</a:IsStatic><a:RefId>a42a8524-7884-489d-95aa-8b5c38a80b46</a:RefId><a:Relative><a:NamedTimeFrame>LastMonth</a:NamedTimeFrame><a:Unit>Month</a:Unit><a:UnitCount>1</a:UnitCount></a:Relative><a:Static i:nil="true"/></a:TimeFrame><a:TimeFrame><a:DisplayName>Past Hour</a:DisplayName><a:IsStatic>false</a:IsStatic><a:RefId>3d6c60fc-bb11-4fa1-80a8-be1fee216320</a:RefId><a:Relative><a:NamedTimeFrame>PastHour</a:NamedTimeFrame><a:Unit>Hour</a:Unit><a:UnitCount>1</a:UnitCount></a:Relative><a:Static i:nil="true"/></a:TimeFrame></TimeFrames></Report>
    Attached my current report.  Would love to know if the dates work for you.

  • This is how mine looks (I had to edit a few things because I didn't have the exact same CP name - mine was singular and yours was plural)

    Still not doing the ${FromTime} and ${ToTime} correctly.  That annoys me in so many ways.

  • Okay so definitely a bug of some kind. I guess I will have to try and figure something else out on this.  Thanks

Reply Children
No Data