quote:SELECT Applications.FullName AS ApplicationName,AVG(ApplicationStatistics.Availability) AS AVERAGE_of_AvailabilityFROM Applications JOIN ApplicationStatistics ON (Applications.AppID = ApplicationStatistics.AppID)WHERE ( DateTime > (GetDate()-30) ) AND ( (Applications.FullName LIKE '%ECM%') )AND (ApplicationStatistics.Availability<>0)GROUP BY Applications.AppID, ApplicationStatistics.AppID, ApplicationStatistics.Availability, Applications.FullNameORDER BY FullName ASC
Howdy Lars,
I've had some limited success with a similar bit that uses nested queries. I can execute this just fine from within Solar Winds Report Writer (which I then manually export to .csv to manipulate as I choose on another system), or a web based SQL report. It's essentially locating some specific host types that have been down for 240 minutes or longer (4 hours).
SELECT * FROM
(
SELECT
Nodes.Site_ID,
Nodes.Site_Name,
Nodes.Caption,
Nodes.Latitude,
Nodes.Longitude,
Nodes.City,
Nodes.State,
Nodes.Office_Type,
Nodes.Alert_Importance,
Nodes.Status,
StartTime.Message,
StartTime.EventTime AS DownEventTime,
SELECT TOP 1 EventTime
FROM Events AS EndTimeTable
where EndTimeTable.EventTime >= StartTime.EventTime
AND EndTimeTable.EventType = 5
AND EndTimeTable.NetObjectType = 'N'
AND EndTimeTable.NetworkNode = StartTime.NetworkNode
AND EventTime IS NOT NULL
AND Office_Type IS NOT NULL
ORDER BY EndTimeTable.EventTime
)
AS UpEventTime,
DATEDIFF
(Mi, StartTime.EventTime,
SELECT TOP 1 EventTime FROM Events AS Endtime
where EndTime.EventTime > StartTime.EventTime AND EndTime.EventType = 5
AND EndTime.NetObjectType = 'N'
AND EndTime.NetworkNode = StartTime.NetworkNode
ORDER BY EndTime.EventTime
AS OutageDurationInMinutes
FROM Events StartTime
INNER JOIN Nodes ON StartTime.NetworkNode = Nodes.NodeID
WHERE (StartTime.EventType = 1)
AND Nodes.Status = '2'
AND (Nodes.Office_Type NOT LIKE '%Telework%')
AND (Nodes.Office_Type NOT LIKE '%Test Site%')
AS UpTimeTable
where outageDurationInMinutes >= 240
ORDER BY DownEventTime DESC, Site_ID ASC
The real caveat comes if/when you attempt a non-SQL method of fetching. In my case I'm forced to use the SWIS service via "https://ourserver_host_name/orion/admin/swis.aspx", and am using curl in a BASH shell thusly:curl -k -u USER:'password' -X GET -H "Content-Type: application/json" https://our_server_hostname:17778/SolarWinds/InformationService/v3/Json/Query?query=SELECT Site_ID, Site_Name, NodeCaption, Latitude, Longitude, City, State, Status, ZipCode+ FROM+Cirrus.Nodes WHERE AgentIP+LIKE+'%126'+and+status+=+'2'
You'll note I've not as yet 100% converted the entirety of the original SQL for my 4 hour window yet; I just started working on this earlier this morning. When making the SQL to SWIS jump, you'll find a LOT of interesting table selections available and although it originally stood for Semantic Web Query Language, you'll find access to data no longer requiring joins your former SQL statements needed, etc. (More in depth info recommended here.)
What makes this most tricky is SWQL isn't "pure SQL". In particular, you cannot use global SELECT * FROM * wildcards or the like. You'll also find if moving to SWIS you're querying a different animal, data will be in slightly different tables and such.
The SWQL query above is displayed below. I crank it through a URL encoder such as URL Encode Decode - URL Percent Encoding and Decoding. to get the format I need for curl/wget use.:
Site_ID,
Site_Name,
NodeCaption,
AgentIP,
Latitude,
Longitude,
City,
State,
Status,
ZipCode
FROM Cirrus.Nodes
WHERE
AgentIP LIKE '%126' and status = '2'
First time I've bothered to post on Thwack - I hope it helps!
Dustin
