cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 7

Attempting to filter this great custom query I found on here

Jump to solution

I'm trying to filter this by a custom property and i've been trying for days so i figured i'd finally toss in the towel and ask.  The code is:

select n.caption as [Device]

-- shows the current status icon 

, '/Orion/images/StatusIcons/Small-' + n.StatusIcon AS [_IconFor_Device] 

-- makes a clickable link to the node details 

, n.DetailsUrl as [_linkfor_Device] 

-- shows the timestamp of the down event, if there is no timestamp then is says the event was greater than the number of days in your event retention settings 

, isnull(tostring(t2.[Down Event]),concat('Greater than ',(SELECT CurrentValue FROM Orion.Settings where settingid='SWNetPerfMon-Settings-Retain Events'),' days ago')) as [Down Event] 

-- shows the timestamp of the up event, unless the object is still down 

, isnull(tostring(t2.[Up Event]),'Still Down') as [Up Event] 

-- figures out the minutes between the down and up events, if the object is still down it counts from the down event to now, displays 99999 if we cannot accurately determine the original downtime, and  

, isnull(MINUTEDIFF(t2.[Down Event], isnull(t2.[Up Event],GETDATE())),99999) as Minutes 

 

from orion.nodes n 

left join (SELECT     

-- Device nodeid used for our join    

StartTime.Nodes.NodeID        

-- Down Event time stamp in local time zone     

,ToLocal(StartTime.EventTime) AS [Down Event]     

WHERE n.CustomProperties.Managedby ='WatechNOC'   

-- Up Event time stamp in local time zone     

,(SELECT TOP 1     

ToLocal(EventTime) AS [EventTime]     

FROM Orion.Events AS [EndTime]     

-- picks the first up event that is newer than the down event for this node 

WHERE EndTime.EventTime >= StartTime.EventTime    

-- EventType 5 is a node up  

AND EndTime.EventType = 5

AND EndTime.NetObjectID = StartTime.NetObjectID     

AND EventTime IS NOT NULL     

ORDER BY EndTime.EventTime     

) AS [Up Event]     

   

-- This is the table we are querying     

FROM Orion.Events StartTime     

   

-- EventType 1 is a node down 

WHERE StartTime.EventType = 1     

     

) t2 on n.NodeID = t2.nodeid 

 

}

-- this is how I catch nodes that are down but have aged out of the events table 

where (n.status = 2 or t2.nodeid is not null) 

 

-- If you want to filter the results to only show outages of a minimum duration uncomment the below line 

--and MINUTEDIFF(isnull(t2.[Down Event],(GETUTCDATE()-30)), isnull(t2.[Up Event],GETUTCDATE())) >  1800 

 

-- if you want to use this query in a search box of the Custom Query resource uncomment the below line 

--and n.Caption like '%${SEARCH_STRING}%' 

 

 

order by t2.[down event] desc 

and i'm trying to filter by a custom property called managedby.  I've tried putting n.CustomProperties.Managedby = 'NAME' all over the place in this with either Where or And and it always gives me an error.  Can someone assist?  Thank you very much for your time. I'm very new at this part of solarwinds and I appreciate your time.

0 Kudos
1 Solution

Accepted Solutions
Highlighted

Re: Attempting to filter this great custom query I found on here

Jump to solution

Try this:

SELECT n.Caption AS [Device]
-- shows the current status icon 
, '/Orion/images/StatusIcons/Small-' + n.StatusIcon AS [_IconFor_Device] 
-- makes a clickable link to the node details 
, n.DetailsUrl AS [_LinkFor_Device] 
-- shows the timestamp of the down event, if there is no timestamp then is says the event was greater than the number of days in your event retention settings 
, ISNULL(TOSTRING(t2.[Down Event]),CONCAT('Greater than ',(SELECT CurrentValue FROM Orion.Settings WHERE SsettingId='SWNetPerfMon-Settings-Retain Events'),' days ago')) AS [Down Event] 
-- shows the timestamp of the up event, unless the object is still down 
, ISNULL(TOSTRING(t2.[Up Event]),'Still Down') AS [Up Event] 
-- figures out the minutes between the down and up events, if the object is still down it counts from the down event to now, displays 99999 if we cannot accurately determine the original downtime, and  
, ISNULL(MINUTEDIFF(t2.[Down Event], ISNULL(t2.[Up Event],GETDATE())),99999) AS [Minutes] 
FROM Orion.Nodes n

LEFT JOIN (SELECT     
-- Device nodeid used for our join    
StartTime.Nodes.NodeID        
-- Down Event time stamp in local time zone     
,TOLOCAL(StartTime.EventTime) AS [Down Event]     
-- Up Event time stamp in local time zone
,(SELECT TOP 1     
TOLOCAL(EventTime) AS [EventTime]     
FROM Orion.Events AS [EndTime]     
-- picks the first up event that is newer than the down event for this node 
WHERE EndTime.EventTime >= StartTime.EventTime    
-- EventType 5 is a node up  
AND EndTime.EventType = 5
AND EndTime.NetObjectID = StartTime.NetObjectID     
AND EventTime IS NOT NULL     
ORDER BY EndTime.EventTime     
) AS [Up Event]     
-- This is the table we are querying     
FROM Orion.Events StartTime     
-- EventType 1 is a node down 
WHERE StartTime.EventType = 1
) t2 ON n.NodeID = t2.NodeID

-- this is how I catch nodes that are down but have aged out of the events table 
WHERE (n.Status = 2 OR t2.NodeId IS NOT NULL) AND n.CustomProperties.Managedby ='WatechNOC'   

-- If you want to filter the results to only show outages of a minimum duration uncomment the below line 
--and MINUTEDIFF(ISNULL(t2.[Down Event],(GETUTCDATE()-30)), ISNULL(t2.[Up Event],GETUTCDATE())) >  1800 
-- if you want to use this query in a search box of the Custom Query resource uncomment the below line 
--and n.Caption like '%${SEARCH_STRING}%' 

ORDER BY t2.[Down Event] DESC
- David Smith

View solution in original post

2 Replies
Highlighted

Re: Attempting to filter this great custom query I found on here

Jump to solution

Try this:

SELECT n.Caption AS [Device]
-- shows the current status icon 
, '/Orion/images/StatusIcons/Small-' + n.StatusIcon AS [_IconFor_Device] 
-- makes a clickable link to the node details 
, n.DetailsUrl AS [_LinkFor_Device] 
-- shows the timestamp of the down event, if there is no timestamp then is says the event was greater than the number of days in your event retention settings 
, ISNULL(TOSTRING(t2.[Down Event]),CONCAT('Greater than ',(SELECT CurrentValue FROM Orion.Settings WHERE SsettingId='SWNetPerfMon-Settings-Retain Events'),' days ago')) AS [Down Event] 
-- shows the timestamp of the up event, unless the object is still down 
, ISNULL(TOSTRING(t2.[Up Event]),'Still Down') AS [Up Event] 
-- figures out the minutes between the down and up events, if the object is still down it counts from the down event to now, displays 99999 if we cannot accurately determine the original downtime, and  
, ISNULL(MINUTEDIFF(t2.[Down Event], ISNULL(t2.[Up Event],GETDATE())),99999) AS [Minutes] 
FROM Orion.Nodes n

LEFT JOIN (SELECT     
-- Device nodeid used for our join    
StartTime.Nodes.NodeID        
-- Down Event time stamp in local time zone     
,TOLOCAL(StartTime.EventTime) AS [Down Event]     
-- Up Event time stamp in local time zone
,(SELECT TOP 1     
TOLOCAL(EventTime) AS [EventTime]     
FROM Orion.Events AS [EndTime]     
-- picks the first up event that is newer than the down event for this node 
WHERE EndTime.EventTime >= StartTime.EventTime    
-- EventType 5 is a node up  
AND EndTime.EventType = 5
AND EndTime.NetObjectID = StartTime.NetObjectID     
AND EventTime IS NOT NULL     
ORDER BY EndTime.EventTime     
) AS [Up Event]     
-- This is the table we are querying     
FROM Orion.Events StartTime     
-- EventType 1 is a node down 
WHERE StartTime.EventType = 1
) t2 ON n.NodeID = t2.NodeID

-- this is how I catch nodes that are down but have aged out of the events table 
WHERE (n.Status = 2 OR t2.NodeId IS NOT NULL) AND n.CustomProperties.Managedby ='WatechNOC'   

-- If you want to filter the results to only show outages of a minimum duration uncomment the below line 
--and MINUTEDIFF(ISNULL(t2.[Down Event],(GETUTCDATE()-30)), ISNULL(t2.[Up Event],GETUTCDATE())) >  1800 
-- if you want to use this query in a search box of the Custom Query resource uncomment the below line 
--and n.Caption like '%${SEARCH_STRING}%' 

ORDER BY t2.[Down Event] DESC
- David Smith

View solution in original post

Highlighted
Level 7

Re: Attempting to filter this great custom query I found on here

Jump to solution

i solved it awhile ago and i think i posted this in the wrong section. I appreciate the help very much. Thank you. 

0 Kudos