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?

Reply Children
  • 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.

  • If you had a magic pencil, what would you want to result report to look like? (the more detail the better, including if you can put in a table with the fields or the like).

  • Hiya!  Sorry for delay.  Solarwinds support came back and told me they can not help me.  

    Hi Blake,
    
    Good Day!
    Unfortunately, we do not support SWQL troubleshooting. We can guide you on where to place them but if a script fails, we won't be able to assist.
    
    It is not that we do not wish to help, it is just that we are not trained for SWQL troubleshooting nor creating them.
    Please see Support Limitation under the article below:
    https://support.solarwinds.com/working-with-support
    
    All SWQL and Orion SDK troubleshooting are usually channeled through the Thwack forums.
    

    So that does not seem to be an option!

    Ill do my best to describe my wish list.

    1 Monthly Report (with ability to get historical data from when I implemented solarwinds if possible)

    Reporting on Groups (currently a custom field called AvailabilityReportingGroups)

    I also tried using actual groups but that caused issues with historical info.

    Under this custom field I have Dev Servers, Prod Servers, Network Devices, and Telecom.  I need a report monthly that can be easily typed into an excel document.

    The unmanaged time is a wildcard field that I believe i could get them to not track as i am not sure what can be done with this.  I know that Average availability excludes time a node is not managed, so would be good to see a percentage of time that node is in that status, but not sure if this exists.  

    Let me know if this helps.  At this point I don't care if I have to do multiple reports as long as I can get something working soon with the historical data!

    System Uptime (Average of Average availability) Unmanaged Time
    Dev Servers 97.5%
    Prod Servers 99.65%
    Network Devices 86.5%
    Telecom Devices 100%

     

    Node AvailabilityReportingGroup Uptime (average availability) Unmanaged Time
    Server1Dev DEV Servers 100%
    Server2Dev DEV Servers 95%
    Server1Prod PROD Servers 99.3%
    Server2Prod Prod Servers 100%
    Network Device1 Network Devices 100%
    Network Device2 Network Devices 82%
    Network Device3 Network Devices 79%
    Network Device4 Network Devices 85%
    Telecom1 Telecom 100%