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

  • Dear 

    Thanks , 

    CAn we get the data in milliseconds format please ??

    Appreciate your help in this regard.

Reply Children