abel5405 Absent Member.
Absent Member.
2691 views

Getting a list of events from the QDB.

Greetings All,
I was asked to get a list of events and how often they occurred on which servers so I dug into the Event and EventDetail tables to try and get that info. I came up with the query below but it takes forever to run. Is there a more efficient way to write this query so I may also use it with Linked Servers so I can span QDB's in one SSRS Report? As always I most appreciate any help.

______________________________

DECLARE @Days as INT
set @Days = 1

--Get UTC Time
declare @UTCtd AS datetime
set @UTCtd = getutcdate()

--Get Today's Date and Time
declare @TodayTD AS datetime

set @TodayTD = GETDATE()

--Get Time Zone Informaiton
declare @TimeDif as int
set @TimeDif = DATEDIFF(hh, getutcdate(), getdate())

select
e.MachineName,
ed.AgentMsglong,
ed.AgentMsgshort,
DATEADD (HH,@TimeDif,dbo.fnSQLDateTime (ed.FirstOccurTime)) as FirstOccurTime,
DATEADD (HH,@TimeDif,dbo.fnSQLDateTime (e.LastOccurTime)) as LastOccurTime
from
dbo.Event as e
inner join dbo.EventDetail as ed on e.eventid = ed.eventid
where
e.KPName like '%NT_General_EventLog'
and
ed.AgentMsglong not like '%USER32%'
and
DATEDIFF(DD, @TodayTD, (DATEADD (HH,@TimeDif,dbo.fnSQLDateTime (e.LastOccurTime)))) * -1 <= @Days
and
ed.AgentMsglong like '%6008%'
or
ed.AgentMsgshort like '%6008%'
Tags (1)
0 Likes
5 Replies
andy_doran Absent Member.
Absent Member.

Re: Getting a list of events from the QDB.

When dealing with large tables - and specifically with include/exclude clauses - it is better to break the queries you use up rather than to try and do everything in a single query. Firstly, you are searching the long and short message in the Detail table. Only one or the other of these fields is ever populated (if the detail is less than 256 characters the "short" is populated otherwise the "long" is populated).

You should also refrain from using any sort of calculation in a query doing the initial "extraction" - especially if that involves the use of a function (since basically every row processes in the function). You can try the query below and see if that's any better for what you are doing.

Also - if you are doing this with multiple QDBs then it might be better to use the ComputerEvent table in the CCDB instead as that has almost everything you need. But if you really need to filter out based on the detail message then you do need to run this against the QDB.

You could of course run this in the CCDB - have a new temp populated by the first query running in a loop against each of the QDBs registered in the CCDB. So loop through the DataSource table, use the DataSource Name (a proc in CC named "GetDSName" takes the "Name" from the DataSource table and turns it into a string that can be used in a dynamic query). After looping through the QDBs, have the final query to get the final extraction...

declare @Days int = 1
declare @offset int = datediff(s, '19700101', getutcdate()) - datediff(s, '19700101', getdate())
declare @exclude nvarchar(max) = 'USER32'
declare @include nvarchar(max) = '6008'

declare @startTime int = datediff(s, '19700101', getutcdate() - @Days)

create table #Events(MachineName nvarchar(128), EventMessage nvarchar(max), Occurrence int, FirstOccurTime int, LastOccurTime int)
insert #Events (MachineName, EventMessage, Occurrence, FirstOccurTime, LastOccurTime)
select
E.MachineName,
Isnull(D.AgentMsgshort, D.AgentMsglong),
E.Occurrence,
D.FirstOccurTime,
E.LastOccurTime
from
dbo.Event E with (nolock)
inner join dbo.EventDetail D with (nolock)
on D.EventID = E.EventID
where
E.LastOccurTime >= @startTime
and E.KPName like '%MemUtil'
and E.ParentEventID is not null

select
MachineName,
EventMessage,
Occurrence,
FirstOccurTime = dateadd(s, (FirstOccurTime - @offset), '19700101'),
LastOccurTime = dateadd(s, (LastOccurTime - @offset), '19700101')
from
#Events
where
charindex(@exclude, EventMessage) = 0
and charindex(@include, EventMessage) !=0
order by
MachineName,
LastOccurTime desc

drop table #Events
0 Likes
abel5405 Absent Member.
Absent Member.

Re: Getting a list of events from the QDB.

Interesting, so the use of a function could be detrimental to a query or at least large queries? I always found using a function to calculate the Unix time as being easier to code but will have to rethink that. I tried using the ComputerEvent table in the CCDB but since we are only showing open acknowledged events in Control Center closed Events do not appear. I will definitely look into applying this method to my reports. Many many thanks Andy!

v/r
Chris
0 Likes
abel5405 Absent Member.
Absent Member.

Re: Getting a list of events from the QDB.

On other question and I apologize if it is a SQL 101 question, you use Isnull(D.AgentMsgshort, D.AgentMsglong) to some how join both the AgentMSGLong and AgentMSGShort columns, how does that work?

v/r
Chris
0 Likes
andy_doran Absent Member.
Absent Member.

Re: Getting a list of events from the QDB.

abel5405;2469677 wrote:
On other question and I apologize if it is a SQL 101 question, you use Isnull(D.AgentMsgshort, D.AgentMsglong) to some how join both the AgentMSGLong and AgentMSGShort columns, how does that work?

v/r
Chris


;^) No worries... ask away...

ISNULL(VALUE1, VALUE2) basically will return VALUE1 unless it is NULL in which case it will return VALUE2 (COALESCE does something similar). So all this is doing is to say "if Agentmsgshort has a null value then return Agentmsglong instead". It's a straight compare between 2 fields and does nothing complicated internally in SQL.

Back to the question on functions... There is nothing wrong in using a function. But you have to be aware that - especially on a "WHERE" clause, it can cause a performance problem. Consider that in a WHERE clause SQL has to know the answer before it can decide whether the row it is looking at should be returned or not. So it has to make the call on every row. Functions generally contain some logic themselves and so that logic is applied to each row.

It can sometimes be better to exclude some rows first if that can be done with some of the more simple clauses (ie just return all rows matching a given KS first and then process the results to further refine them).

In terms of date formatting - it is ALWAYS better to deal with things in their raw form in the query itself - leaving the formatting of the information to the fields being returned. So if you are looking at pulling the data based on timestamps - work out the start/end times in UTC format (an integer) first and then pass those values to the WHERE clause (because the UTC integer is what is used in the data itself in this case). That way you are not doing the date calculation on every row in order to figure out if it should be returned or not.

Some people take this approach using subqueries... "select * from ABC where def in (select def from XYZ where Status = 1)" - it's a similar thing to pulling the data into a temp table and using those results in a 2nd query because SQL processes the sub-query first and then uses the results in the main query

So generally speaking - smaller queries work better on large datasets than big complex queries with lots of joins and lots of WHERE clauses...
0 Likes
abel5405 Absent Member.
Absent Member.

Re: Getting a list of events from the QDB.

Many thanks Andy!! I will definitely take all this to heart. I see a number of queries I will need to revisit and re-write with this new information! I can't say thank you enough for all yawls help!!

v/r
Chris
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.