Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials

DateTime SWQL Question

I need help converting this to SWQL : This is how far i got.....

convert( varchar (10 ), ( DATEDIFF (HH , 0, GETDATE () - MAX ( Events. EventTime )) % 24 )) + ' Hours '

This is how far i got.....

ToString( HourDiff(GETDATE (),MAX ( Events. EventTime )) % 24 ) + ' Hours '

The % 24 is giving me the issue, i guess that function doesn't exist in SWQL unless it does and i need to know the equivalent any help would be awesome!

Find more posts tagged with

Accepted answers

azabielski

Slight change and i got it i just had to reverse the math a bit

,ToString(DayDiff(0,GETUTCDATE() - MAX(EventTime))) + ' Days ' +

ToString(Ceiling((HourDiff(0, GETUTCDATE() - MAX(EventTime)) / 24.0 - Floor(HourDiff(0,GETUTCDATE() - MAX(EventTime)) / 24.0)) * 24 )) + ' Hours ' +

ToString(Ceiling((MinuteDiff(0, GETUTCDATE() - MAX(EventTime)) / 60.0 - Floor(MinuteDiff(0,GETUTCDATE() - MAX(EventTime)) / 60.0) ) * 60 )) + ' Minutes ' AS DownTime

All comments

Hi,

you are right SWQL doesn't support MODULO operator. But you can still do it self

SELECT

ToString( Ceiling( (HourDiff(MAX (EventTime ), GETUTCDATE()) / 24.0 - Floor(HourDiff(MAX ( EventTime ), GETUTCDATE ()) / 24.0) ) * 24 )) + ' Hours ' AS Hours

FROM Orion.Events

In SWQL use almost always GETUTCDATE since it's default time kind we work with.

Thanks

ET;

azabielski

Thank you sir! Now it looks nice again

ToString(DayDiff(MAX(EventTime),GETUTCDATE())) + ' Days ' +

ToString(Ceiling((HourDiff(MAX(EventTime), GETUTCDATE()) / 24.0 - Floor(HourDiff(MAX(EventTime),GETUTCDATE()) / 24.0) ) * 24 )) + ' Hours ' +

ToString(Ceiling((MinuteDiff(MAX(EventTime), GETUTCDATE()) / 60.0 - Floor(MinuteDiff(MAX(EventTime),GETUTCDATE()) / 60.0) ) * 60 )) + ' Minutes ' AS DownTime

azabielski

Actually i lied , seems there is an issue Let's say the alert comes in at 12:45pm then its now 1:05PM it will show as 1 hour and 20 mins instead of just 20 mins because the ceiling is 1 more int than the floor now which makes sense but the MODULO op takes care of that already.... Any other ideas?

azabielski

Slight change and i got it i just had to reverse the math a bit

,ToString(DayDiff(0,GETUTCDATE() - MAX(EventTime))) + ' Days ' +

ToString(Ceiling((HourDiff(0, GETUTCDATE() - MAX(EventTime)) / 24.0 - Floor(HourDiff(0,GETUTCDATE() - MAX(EventTime)) / 24.0)) * 24 )) + ' Hours ' +

ToString(Ceiling((MinuteDiff(0, GETUTCDATE() - MAX(EventTime)) / 60.0 - Floor(MinuteDiff(0,GETUTCDATE() - MAX(EventTime)) / 60.0) ) * 60 )) + ' Minutes ' AS DownTime

rsprim

I'm trying the below code on my Orion using v3 and I keep getting the Error "Entity Orion.Nodes does not contain requested navigation property Events". Any idea how I can fix this? I eventually want to place this information into an EOC Custom Query Resource to pull the data from all my Orions. Possible?

Select N.Caption, N.CustomProperties.Base as Location, N.CustomProperties.Tower, N.CustomProperties.Comments, ToString(DayDiff(0,GETUTCDATE() - MAX(N.Events.EventTime))) + ' Days ' + ToString(Ceiling((HourDiff(0, GETUTCDATE() - MAX(N.Events.EventTime)) / 24.0 - Floor(HourDiff(0,GETUTCDATE() - MAX(N.Events.EventTime)) / 24.0)) * 24 )) + ' Hours ' + ToString(Ceiling((MinuteDiff(0, GETUTCDATE() - MAX(N.Events.EventTime)) / 60.0 - Floor(MinuteDiff(0,GETUTCDATE() - MAX(N.Events.EventTime)) / 60.0) ) * 60 )) + ' Minutes ' AS DownTime From Orion.Nodes N Where N.CustomProperties.Tower = 'DST' AND N.Status = 2

tdanner

What version(s) of Orion are you using?

You might be able to work around the missing "Events" navigation property by using a LEFT JOIN instead. Like this:

Select N.Caption, N.CustomProperties.Base as Location, N.CustomProperties.Tower, N.CustomProperties.Comments,     ToString(DayDiff(0,GETUTCDATE() - MAX(NEvents.EventTime))) + ' Days '     + ToString(Ceiling((HourDiff(0, GETUTCDATE() - MAX(NEvents.EventTime)) / 24.0 - Floor(HourDiff(0,GETUTCDATE() - MAX(N.Events.EventTime)) / 24.0)) * 24 )) + ' Hours '     + ToString(Ceiling((MinuteDiff(0, GETUTCDATE() - MAX(NEvents.EventTime)) / 60.0 - Floor(MinuteDiff(0,GETUTCDATE() - MAX(NEvents.EventTime)) / 60.0) ) * 60 )) + ' Minutes ' AS DownTime From Orion.Nodes N LEFT JOIN Orion.Events NEvents ON N.NodeID= NEvents.NetworkNodeWhere N.CustomProperties.City = 'DST' AND N.Status = 2GROUP BY N.Caption, N.CustomProperties.Base, N.CustomProperties.Tower, N.CustomProperties.Comments

I added the LEFT JOIN on line 6 and changed all occurences of "N.Events" to "NEvents".