If you only care about the last boot time, this will take care of your needs (with the understanding that the metric is not available for ICMP-only devices)
SELECT * FROM dbo.Nodes WHERE LastBoot < DATEADD(year, -1, GetDate())
If you need a report that shows all devices with 100% availability for the last 1 year, that's something different. Basically, since Orion only polls for availability every 120 seconds by default, it is theoretically possible to have a device restart in between polling cycles and still show 100% availability. Additionally, the default data retention for these metrics is only 1 year, so to have a report showing more than 1 year, you need to increase your daily statistics retention to > 365. (Settings > Polling Settings)
For a report like that, you could use something more like this:
SELECT n.Caption ,n.IP_Address ,MIN(r.[Availability]) as Minimum_Availability FROM ResponseTime r JOIN Nodes n on n.NodeID = r.NodeID GROUP BY n.Caption,n.IP_Address HAVING MIN(r.[Availability]) = 100
Ideally, you would want both of these queries as separate tables in one web report so you have full visibility into all of your metrics.
Loop1 Systems: SolarWinds Training and Professional Services
Thanks for the quick response zackm! I'll try this and let you know how it goes.
Worked like a charm. Thank you very much for your help with this zackm!!