I am stuck in SQL Hell! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

I am stuck in SQL Hell!

Author

Topic

spar1grep
Starting Member

3 Posts

Posted - 2010-01-26 : 03:52:01

Hello All,

This is my first posting so hi everybody! I have given up trying to get a SELECT statement to work as I have spent hours on it and my brain is just not cubed enough to figure out what I am doing wrong, heres hoping that one of you SQL experts will be able to help. Here is the problem:

I have two tables which contain data as follows:

[Inv_Aex_Ac_Identification]
Name nvarchar(64)
_ResourceGuid uniqueidentifier

[Evt_Aex_Client_Logon]
_id bigint
_ResourceGuid uniqueidentifier
User nvarchar(30)
Domain nvarchar(30)
Login Time datetime
Duration int

The first table contains the name of computers, the second table contains logon events. The aim of the SQL is to produce a report that contains the following columns of information :

Name, User, Domain, Login Time, Duration

Now the AVP of I.S. requires a daily report that shows the latest administrator and sys account logons to all computers, which I can succesfully do if I exclude the Duration column by using the following SQL:

SELECT tblMerged1.[Name], tblMerged1.[User], tblMerged1.[Domain], Max(tblMerged1.[Login Time]) AS 'Login Time'
FROM
(
SELECT tblComputerID.[Name], tblMerged.[User], tblMerged.[Domain], tblMerged.[Login Time], tblMerged.[Duration], tblMerged.[_id]
FROM
[Inv_Aex_Ac_Identification] tblComputerID
INNER JOIN
(
SELECT tblEvents.[User], tblEvents.[Duration], tblEvents.[_ResourceGuid], tblEvents.[Domain], tblEvents.[Login Time], tblEvents.[_id]
FROM
[Evt_Aex_Client_Logon] tblEvents
WHERE
tblEvents.[User] = 'Administrator' AND tblEvents.[Duration] > 0
OR
tblEvents.[User] LIKE 'sys%' AND tblEvents.[Duration] > 0
)
tblMerged ON tblMerged.[_ResourceGuid] = tblComputerID.[_ResourceGuid]
) tblMerged1
GROUP BY tblMerged1.[Name], tblMerged1.[User], tblMerged1.[Domain]

Now this is where I get stuck! I cannot use the duration column in the GROUP BY clause of the SQL as this will return all instances of the administrator account logons for the computer concerned rather then just the latest instance (as the duration differs for every record, i.e. user administrator logged onto computer X for 10 minutes in the morning of the 26/01/2010 and then in the afternoon of the 26/01/2010 for 20 minutes, without the Duration column the SQL works (it returns the record on the afternoon of the 26th only), with the Duration column in the GROUP BY clause it returns both records (which is not what is required).

Therefore I tried the following SQL to resolve this (returning the latest administrator logon event and the Duration column):

SELECT
tblComputers.[Name], tblResults.[User], tblResults.[Domain], tblResults.[Login Time], tblResults.[Duration], tblResults.[_id]
FROM
[Inv_Aex_Ac_Identification] tblComputers
INNER JOIN
(
SELECT tblAllEvents.[User], tblAllEvents.[Duration], tblAllEvents.[_ResourceGuid], tblAllEvents.[Domain], tblAllEvents.[Login Time], tblAllEvents.[_id]
FROM
[Evt_Aex_Client_Logon] tblAllEvents
)
tblResults ON tblResults.[_ResourceGuid] = tblComputers.[_ResourceGuid]
WHERE
EXISTS
(
SELECT
tblMerged1.[Name], tblMerged1.[User], tblMerged1.[Domain], Max(tblMerged1.[Login Time]) AS 'Login Time'
FROM
(
SELECT tblComputerID.[Name], tblMerged.[User], tblMerged.[Domain], tblMerged.[Login Time], tblMerged.[Duration], tblMerged.[_id]
FROM
[Inv_Aex_Ac_Identification] tblComputerID
INNER JOIN
(
SELECT tblEvents.[User], tblEvents.[Duration], tblEvents.[_ResourceGuid], tblEvents.[Domain], tblEvents.[Login Time], tblEvents.[_id]
FROM
[Evt_Aex_Client_Logon] tblEvents
WHERE
tblEvents.[User] = 'Administrator' AND tblEvents.[Duration] > 0
OR
tblEvents.[User] LIKE 'sys%' AND tblEvents.[Duration] > 0
)
tblMerged ON tblMerged.[_ResourceGuid] = tblComputerID.[_ResourceGuid]
) tblMerged1
WHERE
tblResults.[_id] = tblMerged1.[_id]
GROUP BY tblMerged1.[Name], tblMerged1.[User], tblMerged1.[Domain]
)

GUESS WHAT - It returns all instances of the administrator account logon, I just cannot figure out why? Any help would be great!!!

BTW It has to be achieved using SQL only as we cannot use stored procedures etc as the SQL is then used in the Altiris Notification Server solution which allows you to create nicely formatted reports using SQL only.

Regards.

[spar1GreP]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-26 : 04:06:06

can you please explain your problem with sample data and reqd output rather than with queries?

spar1grep
Starting Member

3 Posts

Posted - 2010-01-26 : 04:29:26

OK here is a sample of data.

Name User Domain Login Time Duration _id
LT_GDUGGAN Administrator LT_GDUGGAN 2009-11-06 00:05:25.000 6 268956
NCF Administrator NCF 2009-09-01 13:07:35.000 9 218400
NCF sysvb OSG 2010-01-18 15:47:08.000 1 316371
WK_PDIAZ Administrator WK_PDIAZ 2009-12-30 16:25:01.000 11 305257
WK_PDIAZ Administrator WK_PDIAZ 2009-12-31 09:36:41.000 25 308352
WK_PDIAZ Administrator WK_PDIAZ 2009-12-30 16:36:54.000 974 308355
WK_PDIAZ Administrator WK_PDIAZ 2009-12-31 09:24:18.000 12 308357
WK_PDIAZ Administrator WK_PDIAZ 2010-01-21 12:08:45.000 45 319186

The aim is to only show the latest recorded instance of Administrator logon per computer (Name), User, and Domain, so for the computer WK_PDIAZ there should only be the record showing with the latest Login Time, for the computer NCF both records should be showing as the Domain column value differs.

Thanks for the quick response.

Regards.

[spar1GreP]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-26 : 04:36:06

quote:
Originally posted by spar1grep

OK here is a sample of data.

Name User Domain Login Time Duration _id
LT_GDUGGAN Administrator LT_GDUGGAN 2009-11-06 00:05:25.000 6 268956
NCF Administrator NCF 2009-09-01 13:07:35.000 9 218400
NCF sysvb OSG 2010-01-18 15:47:08.000 1 316371
WK_PDIAZ Administrator WK_PDIAZ 2009-12-30 16:25:01.000 11 305257
WK_PDIAZ Administrator WK_PDIAZ 2009-12-31 09:36:41.000 25 308352
WK_PDIAZ Administrator WK_PDIAZ 2009-12-30 16:36:54.000 974 308355
WK_PDIAZ Administrator WK_PDIAZ 2009-12-31 09:24:18.000 12 308357
WK_PDIAZ Administrator WK_PDIAZ 2010-01-21 12:08:45.000 45 319186

The aim is to only show the latest recorded instance of Administrator logon per computer (Name), User, and Domain, so for the computer WK_PDIAZ there should only be the record showing with the latest Login Time, for the computer NCF both records should be showing as the Domain column value differs.

Thanks for the quick response.

Regards.

[spar1GreP]

Ok here you go


SELECT c.Name,e.User,
e.Domain,
e.[Login Time],
e.Duration
FROM [Inv_Aex_Ac_Identification] c
INNER JOIN [Evt_Aex_Client_Logon] e
ON e.[_ResourceGuid]=c.[_ResourceGuid]
INNER JOIN (SELECT _ResourceGuid,MAX([Login Time]) AS Latest
            FROM [Evt_Aex_Client_Logon]
            GROUP BY _ResourceGuid)e1
ON e1.[_ResourceGuid]=e.[_ResourceGuid]
AND e1.Latest = e.[Login Time]

spar1grep
Starting Member

3 Posts

Posted - 2010-01-26 : 06:24:07

Hi Visakh16,

Wow that was a quick response, I now can see how to do it from your SQL so I reformatted what you have provided to get it to work in SQL server, but it's the insight that was more important then the correct SQL.

Thanks for the very quick reply, I now have the report up and running so next stage is to perform a comparision against the raw unfiltered data to confirm all is well.

Regards.

[spar1GreP]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-26 : 06:55:00

welcome

let us know if you still hit any blockades!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources