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.

Need help in Changing the Query to fetch last 7 days data

Dear

Thanks for the great community 

i have the below query which fetches the data like memory used device name and cpu load etc , 

---------------------------------------------------------------------------------------------------------------------------------

SELECT TOP 50 n.Caption,n.PercentMemoryUsed,n.LastBoot,n.Status,n.CPULoad,n.DetailsUrl
FROM Orion.Nodes n

WHERE (Caption LIKE '%FYHA-WLEF%' OR Caption LIKE '%FYHA-WAPC%' OR Caption LIKE '%FYHA-WSER%' OR Caption LIKE '%FYHA-WSPN%' OR Caption LIKE '%FYHA-NMSR%' OR Caption LIKE '%FYHA-NVRR%')

---------------------------------------------------------------------------------------------------------------------------------

Can we change the query to get last 7 days data , kindly help me to change the query to fetch the last 7 days data 

appreciate your help.

Parents
  • The Orion.Nodes entity only holds the Point in Time (most recent poll) information in the fields.  Do you want the PercentMemoryUsed and CPULoad over the last 7 days?  Do you want the Min/Avg/Max over those 7 days? What do you want the output to look like?

  • Or Same Data which happend last 7 days time , thatsalso fine.

  • This is the query for the full dump of data.  It will most likely not be small in your environment.

    SELECT [Nodes].Caption
         , [Nodes].PercentMemoryUsed AS [PercentMemoryUsed_MostRecent]
         , [Nodes].CPULoad AS [CPULoad_MostRecent]
         , [Nodes].CPULoadHistory.ObservationTimestamp
         , [Nodes].CPULoadHistory.AvgLoad
         , [Nodes].CPULoadHistory.AvgPercentMemoryUsed
    --                   ^                 ^
    --                   |                 |
    --                   +- CPULoadHistory is a Navigation Property connected to Orion.CpuLoad
    --                                     |
    --                                     +- AvgPercentMemoryUsed is a value within Orion.CpuLoad
         , [Nodes].Status
         , [Nodes].LastBoot
         , [Nodes].DetailsUrl
    FROM Orion.Nodes AS [Nodes]
    WHERE (
              -- Your caption filters
              Caption LIKE '%FYHA-WLEF%'
              OR Caption LIKE '%FYHA-WAPC%'
              OR Caption LIKE '%FYHA-WSER%'
              OR Caption LIKE '%FYHA-WSPN%'
              OR Caption LIKE '%FYHA-NMSR%'
              OR Caption LIKE '%FYHA-NVRR%'
              )
         -- the Date filter
         AND [Nodes].CPULoadHistory.ObservationTimestamp >= GETDATE() - 7
    -- I added my own sort so I could validate the data coming back
    ORDER BY [Nodes].Caption
           , [Nodes].CPULoadHistory.ObservationTimestamp DESC

Reply
  • This is the query for the full dump of data.  It will most likely not be small in your environment.

    SELECT [Nodes].Caption
         , [Nodes].PercentMemoryUsed AS [PercentMemoryUsed_MostRecent]
         , [Nodes].CPULoad AS [CPULoad_MostRecent]
         , [Nodes].CPULoadHistory.ObservationTimestamp
         , [Nodes].CPULoadHistory.AvgLoad
         , [Nodes].CPULoadHistory.AvgPercentMemoryUsed
    --                   ^                 ^
    --                   |                 |
    --                   +- CPULoadHistory is a Navigation Property connected to Orion.CpuLoad
    --                                     |
    --                                     +- AvgPercentMemoryUsed is a value within Orion.CpuLoad
         , [Nodes].Status
         , [Nodes].LastBoot
         , [Nodes].DetailsUrl
    FROM Orion.Nodes AS [Nodes]
    WHERE (
              -- Your caption filters
              Caption LIKE '%FYHA-WLEF%'
              OR Caption LIKE '%FYHA-WAPC%'
              OR Caption LIKE '%FYHA-WSER%'
              OR Caption LIKE '%FYHA-WSPN%'
              OR Caption LIKE '%FYHA-NMSR%'
              OR Caption LIKE '%FYHA-NVRR%'
              )
         -- the Date filter
         AND [Nodes].CPULoadHistory.ObservationTimestamp >= GETDATE() - 7
    -- I added my own sort so I could validate the data coming back
    ORDER BY [Nodes].Caption
           , [Nodes].CPULoadHistory.ObservationTimestamp DESC

Children