Extract Data from Event Description

[griffin61299]

I'm trying to track the logon/off times for Users using certain Event ID's. The entry/example below is the Event Description which is contained in a single cell within my table/report. I need to create a simple query that will extract the Logon ID (just the numbers) - so for the example below, the output would simply be 0x0,0x13BBA434.
I've investigated the Mid function but you need a starting position... Unfortunately the start position will be different for each cell of data and so the query needs to simply extract what is in between the parenthesis for every entry within the column.

Event Description

Successful Network Logon:
User Name: smithj
Domain:
Logon ID: (0x0,0x13BBA434)
Logon Type: 3
Logon Process: Kerberos
Authentication Package: Kerberos
Workstation Name:
Logon GUID: {29492761-2f71-9589-8c1d-d9c51fad1732}
Caller User Name: -
Caller Domain: -
Caller Logon ID: -
Caller Process ID: -
Transited Services: -
Source Network Address: -
Source Port: -

[nico5038]

Try:

select mid(yourfield,instr(yourfield,"("),instr(yourfield ,")")-instr(yourfield,"(")) as EventDescr from tblYors

This will retrieve all between the "(" and ")" and will only work when no other fields before the EventID hold parentheses...

Nic;o)

[NeoPa]

A reworked version to match the LogonID only in the format specified :

SELECT Left([sLogonID],InStr([sLogonID],')')-1) AS LogonID
FROM (SELECT Mid([YourField],InStr([YourField],'Logon ID: (')+11,99) AS sLogonID
      FROM tblYours)

This uses a subquery as doing this directly in SQL is certainly awkward. VBA would handle this sort of thing a lot better, but SQL will crunch through the records better of course.

[griffin61299]

Thank you both for the replies... Now lets say that my report (created in Visual Studio) contains the following information:

Column A: Logon/Off Times for Users
Column B: User Name/ID
Column C: Server Name
Column D: Event ID
Column E: Logon ID (the one that was extracted)

Every 'Logon ID' (the one that was extracted) will have a match. See examples below (Rows 1 and 3 on my report are a match - based on Logon ID):

Row 1
Column A: 3/13/07 10:00 AM
Column B: SmithJ
Column C: SQLSERVER01
Column D: 540 (logon)
Column E: 0x0,0x13BBA434

Row 2
Column A: 03/11/07 1:00 PM
Column B: JonesM
Column C: SQLSERVER01
Column D: 540 (logon)
Column E: 0x0,0x13QFG450

Row 3
Column A: 10:15 AM
Column B: SmithJ
Column C: SQLSERVER01
Column D: 538 (logoff)
Column E: 0x0,0x13BBA434

So i now need a query that will find a match for every LOGON ID (Column E). There will never be more than two occurances of the logon id but there may be instances where the logon id doesn't have a match (i.e. the report only captured the logon event and not the logoff event - null). Once the query finds the match, it will further need to compute the time difference contained in Column A. For the example above, the time difference would be 15 mintues. I am only concerned with logon sessions that exceed 3 Hours and that are conducted by certain Users. So the end result of the report will only produce those sessions that exceed 3 hours... Am I dreaming? Thanks -

[nico5038]

You'll need to start with a groupby query on the column E and with a MAX() and MIN() function for column A like:

select E, Min(A) as StartTime, Max(A) as EndTime from tblYours;

This query can than be used for determning the difference between Min and Max.

Getting the idea ?

Nic;o)

[NeoPa]

I'm not sure this can be done simply in SQL. I'm assuming here, from what you say, that multiple logons can appear for the same ID and that, even within that, you cannot guarantee a matching logoff for every logon. You may need to consider processing through the dataset in VBA.