April 5, 2010 at 1:40 pm
Hi folks,
Can anyone tell me where is the login user's ip address? can that be linked with principal_id in sys.server_principals or any where in trace file?
Thanks in advance.
(sorry I posted it in wrong forum if you saw it already )
April 5, 2010 at 1:57 pm
SELECT client_net_address, client_tcp_port
FROM sys.dm_exec_connections
April 5, 2010 at 2:00 pm
Thank you George,
select * From sys.dm_exec_connections WHERE Session_id = @@SPID will give me the IP address info, and the following code gives me more detailed info except IP is missing:
SELECT
I.NTUserName,
I.loginname,
I.SessionLoginName,
I.databasename,
Min(I.StartTime) as first_used,
Max(I.StartTime) as last_used,
S.principal_id,
S.sid,
S.type_desc,
S.name
FROM
sys.traces T CROSS Apply
::fn_trace_gettable(CASE
WHEN CHARINDEX( '_',T.[path]) <> 0 THEN
SUBSTRING(T.PATH, 1, CHARINDEX( '_',T.[path])-1) + '.trc'
ELSE T.[path]
End, T.max_files) I LEFT JOIN
sys.server_principals S ON
CONVERT(VARBINARY(MAX), I.loginsid) = S.sid
WHERE
T.id = 1 And
I.LoginSid is not null
Group By
I.NTUserName,
I.loginname,
I.StartTime,
I.SessionLoginName,
I.databasename,
S.principal_id,
S.sid,
S.type_desc,
S.name
having datediff(dd, Max(I.StartTime), getdate()) <= 30
order by databasename, loginname, I.StartTime
I want to consolidate the two, I wonder if there is any link between session_id and principle_id, ssid?
Thank you.
April 5, 2010 at 4:22 pm
GregoryF (4/5/2010)
declare @IPAddress as varchar(15)declare @data XML
set @data = EVENTDATA()
set @IPAddress = @data.value('(/EVENT_INSTANCE/ClientHost)[1]', 'nvarchar(15)')
This is the method that I recommended on the other post on the same topic - but not quite so verbosely.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 5, 2010 at 4:32 pm
Interesting Query. I like what you have done with it.
Here is something that should work for you.
SELECT
I.NTUserName,
I.loginname,
I.SessionLoginName,
I.databasename,
Min(I.StartTime) as first_used,
Max(I.StartTime) as last_used,
S.principal_id,
S.sid,
S.type_desc,
S.name
,EC.client_net_address
FROM
sys.traces T CROSS Apply
::fn_trace_gettable(CASE
WHEN CHARINDEX( '_',T.[path]) <> 0 THEN
SUBSTRING(T.PATH, 1, CHARINDEX( '_',T.[path])-1) + '.trc'
ELSE T.[path]
End, T.max_files) I LEFT JOIN
sys.server_principals S ON
CONVERT(VARBINARY(MAX), I.loginsid) = S.sid
Left Outer Join sys.dm_exec_connections EC
On EC.session_id = I.SPID
WHERE
T.id = 1 And
I.LoginSid is not null
Group By
I.NTUserName,
I.loginname,
I.StartTime,
I.SessionLoginName,
I.databasename,
S.principal_id,
S.sid,
S.type_desc,
S.name
,EC.client_net_address
having datediff(dd, Max(I.StartTime), getdate()) <= 30
order by databasename, loginname, I.StartTime
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 5, 2010 at 6:21 pm
Another thing to really keep in mind, bearing in mind that I don't know what your prupose is. It's also wise to know the NetBIOS hostname of the client. If you are trying to do some type of auditing, IP Address is great, assuming that, in a timely fashion, you can "join*" IP Address with lease information. And if your shop is anything like anywhere I have ever worked, it's going to require a ticket that may take a week or so to get resolved by an OS team member (since you are going to have to ask them who had the lease for the address on Friday at 3:30).
It's just easier to collect the information ourselves.
Last I looked, DHCP logs are usually just text files. They certainly can be imported into a database for easier auditing...but usually are just left in their raw format, making it even more difficult to find who had what, when.
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
April 5, 2010 at 6:27 pm
The hostname can also be returned back into this query. That information is returned by fn_trace_gettable.
This would change the query as follows:
SELECT I.NTUserName,I.loginname,I.SessionLoginName,I.databasename,Min(I.StartTime) as first_used
,Max(I.StartTime) as last_used,S.principal_id,S.sid,S.type_desc,S.name,EC.client_net_address,I.HostName
FROM sys.traces T
CROSS Apply ::fn_trace_gettable(
CASE
WHEN CHARINDEX( '_',T.[path]) <> 0
THEN SUBSTRING(T.PATH, 1, CHARINDEX( '_',T.[path])-1) + '.trc'
ELSE T.[path]
End, T.max_files) I
LEFT Outer Join sys.server_principals S
ON CONVERT(VARBINARY(MAX), I.loginsid) = S.sid
Left Outer Join sys.dm_exec_connections EC
On EC.session_id = I.SPID
WHERE T.id = 1
And I.LoginSid is not null
Group By I.NTUserName,I.loginname,I.StartTime,I.SessionLoginName,I.databasename,S.principal_id,S.sid
,S.type_desc,S.name,EC.client_net_address,I.HostName
Having datediff(dd, Max(I.StartTime), getdate()) <= 30
Order By databasename, loginname, I.StartTime
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 5, 2010 at 7:52 pm
Big thanks to everyone, your kind helps make my life lots easier.
I am going to send the result as email attachment in certain period, is it possible to make the attachment in a report like in PDF format, or HTML format? I know it's easy to make it csv.
April 5, 2010 at 7:57 pm
halifaxdal (4/5/2010)
Big thanks to everyone, your kind helps make my life lots easier.I am going to send the result as email attachment in certain period, is it possible to make the attachment in a report like in PDF format, or HTML format? I know it's easy to make it csv.
You're welcome.
For the report, you would need something to read the data and convert it to PDF. For html, you would need to rework your query so it is output as html.
An alternative would be to build an SSRS report that will format it nicely for you that you can just email as pdf or html.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply