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.

  • Okay, good news it likes your queries now.  But on to the second part of the question.  Even with the $FromTime and $ToTime - How do I declare these variables?  I dont see the selector like I would with a normal query?

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

    Nope - this is being setup as defined in the linked support docs..  Very odd.  What version of the platform are you running?

    Regardless - each reporting resource (in blue below) would have their own timespan.

    Depending on what you were thinking about the report, you still may need multiple reports.

    For example, if I want "Last Month", "This Month", and "This Week" resources for summary by team and each node itself, we would need either

    • a report with a segment for each of them (3 × 2 = 6) resources.
    • three different reports with only the two segments each.
  • Should be the latest version: 

    Orion Platform HF4, IPAM, NCM HF1, NPM HF3, NTA HF2, SAM HF4: 2020.2.6

    When I create a new report and type in this query manually i see the selector and it says "past hour" but as as soon as I submit the selector goes away.

  • Orion Platform HF4, IPAM, NCM HF1, NPM HF3, NTA HF2, SAM HF4: 2020.2.6

    Most current is Orion Platform HF5 ... 2020.2.6, but I don't think HF5 would make a difference. (Do not hold me to this).

    If this were me, I'd open a support case to get someone to review it, because you are following the supported instructions.

  • I want both of these resources to use the same time frame.  I dont know why it was called team (probably repurposed by something else!) but the top report is supposed to be a summary (Average) of the bottom report nodes.

  • Roger that.  Will do!  They redirected me here because they did not know how to make it work with dates.