Pulling out Historical database for response time from Database manager

Hi , I want to get a csv file from MSSQL studio or Solarwinds Database manager on Response Time for 30 days for Node with some Custom Property value like "Switch-Garages" assigned.

Currently I am getting this data from reports feature on Solarwinds but would rather want to pull this directly from Database.

I did see the Response Time( Daily , hourly and statistics) table but can how will I gather the data for 30 days for specific nodes with custom tags is chalenging to me as I am not familiar with SQL or SQWL except for using where like and join features , I know there should a way of doing it . I am preferring to get this on MMSQL.

Custom property is just an example i have used.

I did check out the SQWL query but could not get much out from it as there are columns in actual report.

Find more posts tagged with

SWQL

npm orion

Accepted answers

All comments

KMSigma.SWI

Question: Is the custom property assigned to the Node or the Interface?

heldy

The kind of data that I am trying to see can also work for Nodes. Nodes are assigned custom property/tags , so lets say if I want to see the combine Response Time for the last one hour on nodes grouped Custom Property "Layer2_Switches" how is it done ?

KMSigma.SWI

-- This is just the last hour for devices
SELECT [Nodes].Caption
-- the multiply by 1.0 turns this into a decimal instead of an integer
     , AVG([Nodes].ResponseTimeHistory.AvgResponseTime * 1.0) AS [AverageResponseTime]
FROM Orion.Nodes AS [Nodes]
WHERE
-- This says to filter down for only the last 1 hour
  [Nodes].ResponseTimeHistory.ObservationTimestamp >= AddHour(-1, GETUTCDATE())

-- This is where you'd put your custom property name and then the value you want to match
--  AND [Nodes].CustomProperties. = ''
GROUP BY [Nodes].Caption
ORDER BY [Nodes].Caption


-- This is a more detailed view

SELECT [Nodes].Caption
-- the multiply by 1.0 turns this into a decimal instead of an integer
     , AVG([Nodes].ResponseTimeHistory.AvgResponseTime * 1.0) AS [AverageResponseTime]
     , DATETRUNC('hour', [Nodes].ResponseTimeHistory.ObservationTimestamp) AS [HourSlice]
FROM Orion.Nodes AS [Nodes]
WHERE
-- This says to filter down for only the last 1 hour
--  [Nodes].ResponseTimeHistory.ObservationTimestamp >= AddHour(-1, GETUTCDATE())
-- This says to filter down for only the last 1 day
  [Nodes].ResponseTimeHistory.ObservationTimestamp >= AddDay(-1, GETUTCDATE())
-- This is where you'd put your custom property name and then the value you want to match
--  AND [Nodes].CustomProperties. = ''
GROUP BY [Nodes].Caption, DATETRUNC('hour', [Nodes].ResponseTimeHistory.ObservationTimestamp)
ORDER BY [Nodes].Caption, DATETRUNC('hour', [Nodes].ResponseTimeHistory.ObservationTimestamp)

heldy

Any help appreciated , SWQL studio seems better option but right now i am unable to connect on it maybe because of version upgrade to 2023 and rest client port changed

dan_jagnow

What version of the product are you using? And what version of the Orion SDK? It is entirely possible that the port change described in https://thwack.solarwinds.com/product-forums/the-orion-platform/f/orion-sdk/98142/swis-rest-api-port-deprecation-did-you-know is the cause of the problem.