This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

HELP IN SQL QUERY

zabs over 6 years ago

I want to add ip address field in this query. please help.

SELECT

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 UpEventTime,

Nodes.Caption,StartTime.Message, 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)

ORDER BY eventtime desc

Top Replies

njoylif over 6 years ago +2

Insert right below ", Nodes.Caption" on line 12
zabs over 6 years ago in reply to njoylif +1

issue resolved

0 njoylif over 6 years ago

Insert right below ", Nodes.Caption" on line 12
Cancel
Vote Up +2 Vote Down

Cancel
0 zabs over 6 years ago in reply to njoylif

Thanks
Cancel
Vote Up 0 Vote Down

Cancel
0 zabs over 6 years ago in reply to njoylif

if i want to add Where clause to filter the specific groups.
Cancel
Vote Up 0 Vote Down

Cancel
0 njoylif over 6 years ago in reply to zabs

in the WHERE statement before the ORDER BY add:
Where (StartTime.EventType=1)
AND <condition>
not sure if you are talking about using a Node Custom property..that's easy
AND Nodes.<groupCP> like '%partOfName%'
Cancel
Vote Up 0 Vote Down

Cancel
0 zabs over 6 years ago in reply to njoylif

Yes i am talking about custom properties. i have a field in custom properties "Branches" in branches filed i have contain "International", now i want to filter data base on branches custom properties
Cancel
Vote Up 0 Vote Down

Cancel
0 zabs over 6 years ago in reply to zabs

i apply below query
AND Nodes.Branches = International.branches.
but query not execute showing a error.
Cancel
Vote Up 0 Vote Down

Cancel
0 zabs over 6 years ago in reply to njoylif

issue resolved
Cancel
Vote Up +1 Vote Down

Cancel
0 zabs over 6 years ago in reply to njoylif

above query retrive data last 7 days. i wan to increase the duration from 7 days to one months
Cancel
Vote Up 0 Vote Down

Cancel
0 dgsmith80 over 6 years ago in reply to zabs

Hi zabs
Normally I would say you need to add a
WHERE (DateTime > (getdate()-31))
but I don't think that will help as I don't see any link to the time in your existing query, which would suggest you only have 7 days of events to query - this might be a threshold setting you need to adjust in your retention settings.
Cancel
Vote Up 0 Vote Down

Cancel