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: -
5 2198 nico5038 3,080
Recognized Expert Specialist
Try:
select mid(yourfield,i nstr(yourfield, "("),instr(your field,")")-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 32,579
Recognized Expert Moderator MVP
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.
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 3,080
Recognized Expert Specialist
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 32,579
Recognized Expert Moderator MVP
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.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Zhang Le |
last post by:
Hello,
I'm writing a little Tkinter application to retrieve news from
various news websites such as http://news.bbc.co.uk/, and display them
in a TK listbox. All I want are news title and url information. Since
each news site has a different layout, I think I need some
template-based techniques to build news extractors for each site,
ignoring information such as table, image, advertise, flash that I'm
not interested in.
So far I have...
|
by: Shakil Khan |
last post by:
Hi there ...
My question is about Meta Data which is automatically saved with files.
For example,when an MS Office Documents is saved, it automaticaly save some
extra information with the file such as Author, Title, Description.
Whenever we see the list of files in normal windows explorer, it will show
you the meta data as well without even opening that file. Similarly when you
Right-Click on a zip file to check its file properties , It...
|
by: sheree |
last post by:
I have 3 tables (amoung a few others) in a small access database. The
tables are as follows:
==
AEReport
--------
AEID (PK)
RptCatelog
GCRCID
PatientID
EvntDate
|
by: Tomek Kmiecik |
last post by:
Hello!
I'm writing simple database application. One table in the database stores
some information about keywords (among other, keyword name, id number and
description). Description is stored in rtf format.
Following code is responsible for data binding (lstKeywords is
ListBox,rtbDescription is RichTextBox):
lstKeywords.DataSource = manager.KeywordsDataView;
|
by: caine |
last post by:
I want to extract web data from a news feed page
http://everling.nierchi.net/mmubulletins.php.
Just want to extract necessary info between open n closing tags of
<title>, <categoryand <link>. Whenever I initiated the extraction,
first news title is always "MMU Bulletin Board RSS Feed" with the
proper bulletin's link stored, but not the correct news title being
stored.
Necessary info only appears within <itemand </itemwhich consists...
| |
by: steveyjg |
last post by:
I want to extract the following data from a retrieved html file and store the information as strings.
'get the text of "title"
<h1 id="test_title">title</h1>
'get the contents of the value attribute
<input name="test_code" type="text" value='<object </object>' >
'get the text of "category" or value of c
<div class="smallText">
|
by: Rick |
last post by:
I've moved code from a stage machine to the production machine, exact same
code works fine on the stage machine, they are both windows 2003 servers,
I'm getting a "Cannot generate SSPI context" error, there are other sites on
this server that access the same database. IIS is the same, permissions to
the database are correct.
Any suggestions?
Error:
|
by: spoken |
last post by:
Hi,
I quite new to Javascript and XML. Here a is snippet of my XML file.
<xml>
<cd>
<rock>
<item id='1'>
<name>Bon Jovi</name>
<price>$10</price>
|
by: bhavinnaik |
last post by:
Hi I am new to the IT enviro...although i've used QSL query for a while now but on simple or single queries.
Here is the problem...
I've got two queries looking at a set of tables to extract the Costed and Invoiced data from the same database. I've tried to use a JOIN but it keeps failing. I've got multiple fields that needs to be linked.
Any suggestions. I've attached the query to make it easier
Declare @IDateFrom DateTime,
...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |