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.

     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

      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!

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

    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 might have a typo because I don't have that particular Custom Property in my system.  Give me a few minutes to add it and I'll test the queries.

  • Yep - one too many closing parenthesis in the custom query.  I've updated the above ones.

Reply Children