Hi,
My problem is -
I have a trigger for auditing the changes(insert/update/delete) in the database table.That is done and is working fine. But I need to have the client's IP address from where the changes are done. That I need in T-SQL, that means, not in any web form but in the SQL/T-SQL.
As I have checked many forums, I got that there is extended stored procedure in master database named xp_cmdshell which has xplog70.dll and when we execute this stored procedure with 'ipconfig' we can get the IP Address. But I do not need that in master database. I need that in my database say myDB.
So how to proceed further. I don't know whether to create extended SP which contains DLL or is there any other option.
Pls help
Thanks in advanceHave you tried calling the stored procedure* from within your "myDB"? :)|||See this the stored procedure code and call,
create Procedure sp_get_ip_address (@.ip varchar(40) out)
as
begin
Declare @.ipLine varchar(200)
Declare @.pos int
set nocount on
set @.ip = NULL
Create table #temp (ipLine varchar(200))
Insert #temp exec master..xp_cmdshell 'ipconfig'
select @.ipLine = ipLine
from #temp
where upper (ipLine) like '%IP ADDRESS%'
if (isnull (@.ipLine,'***') != '***')
begin
set @.pos = CharIndex (':',@.ipLine,1);
set @.ip = rtrim(ltrim(substring (@.ipLine ,
@.pos + 1 ,
len (@.ipLine) - @.pos)))
end
drop table #temp
set nocount off
end
go
declare @.ip varchar(40)
exec sp_get_ip_address @.ip out
print @.ip
But this is in the master Database.If I do this same thing in my database say myDB it gives error that it do not have extended SP|||Try
Exec dbo.sp_get_ip_address @.ip out|||If we exec this stored procedure in the trigger, How to store the output in the table or in some variable. Since I have tried this way - Insert #temp exec master..xp_cmdshell 'ipconfig' which gives error saying that -' insert and execute statments cannot be nested'. And if I first create the table with a column and then write - select * into #temp from exec sp_get_my_ip_address @.ip out, it gives error as incorrect syntax near exec|||Let's just confirm the DBMS that we're using...
I'm guessing SQL Server 2000, but please correct me if I'm wrong.
I'll then move the thread to the appropriate topic.|||I am using SQL Server 2005|||This works in 2000 and 2005 and might be of some use to you - let me know how you get on.
DECLARE @.host varchar(255)
SET @.host = host_name()
CREATE TABLE #Results (
Results varchar(255)
)
DECLARE @.cmd varchar(260)
SET @.cmd = 'ping ' + @.host
INSERT INTO #Results
EXEC master..xp_cmdshell @.cmd
SELECT Replace(Left(Results, CharIndex(']', Results)), 'Pinging ', '') As [client]
, host_name() As [host_name()]
FROM #Results
WHERE Results LIKE 'Pinging%'
DROP TABLE #Results|||Now my problem is - if the client machine do not have host name assigned in that case how can we ping and get the IP ?|||You can't...
Back to your method it is then...
Try this
--DROP trigger and/or table if they exist
IF EXISTS(SELECT 1 FROM sysobjects WHERE type = 'TR' AND name = 'myTable_InsertUpdate') BEGIN
DROP TRIGGER myTable_InsertUpdate
END
IF EXISTS(SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'myTable') BEGIN
DROP TABLE myTable
END
--Create out table; note the audit fields
CREATE TABLE myTable (
id int PRIMARY KEY NOT NULL IDENTITY(1,1)
, field1 char(1)
, changed_by_ip char(15)
, changed_by_host char(15)
, datetime_changed datetime
)
GO
--Create trgger for update and insert
CREATE TRIGGER myTable_InsertUpdate
ON myTable
FOR insert, update
AS
DECLARE @.ipLine varchar(255)
DECLARE @.pos int
DECLARE @.ip char(15)
--temporary table creation
CREATE TABLE #ip (
ipLine varchar(255)
)
--Insert the return of ipconfig into the temp table
INSERT #ip EXEC master..xp_cmdshell 'ipconfig'
--find the line which contains the IP and assign it to a variable
SET @.ipLine = (
SELECT ipLine
FROM #ip
WHERE ipLine LIKE '%IP Address%'
)
--If the IP is known
IF Coalesce(@.ipLine, '***') <> '***' BEGIN
--Find the index of the colon from the END of the string
SET @.pos = CharIndex(':', Reverse(@.ipLine), 1) - 1
--Trim the IP off the end of the string
SET @.ip = Right(@.ipLine, @.pos)
--Remove any trailing or leading white space
SET @.ip = RTrim(LTrim(@.ip))
END
--Drop the temp table
DROP TABLE #ip
--Update the audit fields based on the value being updated
UPDATE myTable
SET changed_by_ip = @.ip
, datetime_changed = GetDate()
, changed_by_host = host_name()
WHERE id IN (SELECT id FROM inserted)
GO
--Insert some test values
INSERT INTO myTable (field1) VALUES ('a')
INSERT INTO myTable (field1) VALUES ('a')
--Display initial values
SELECT * FROM myTable
--Update one of the fields
UPDATE myTable
SET field1 = 'b'
WHERE id = 2
--Display changed values.
SELECT * FROM myTable
--Notice the change in datetime_changed where id = 2
GO
--And finally; clean up after ourselves
DROP TRIGGER myTable_InsertUpdate
DROP TABLE myTable
This works on my install of 2000 and 2005.|||Let me see if I understand what the original poster (neetu bhagtani) was looking for, because this line of reasoning doesn't sound correct to me.
You have at least a SQL 2005 server, and a web server in a data center. You have clients that connect to the web server using HTTP, but those clients do not log in to the SQL Server directly.
If I've described the configuration that you've currently got, then your SQL Server can only get the client machine's IP address from the web server, because the SQL Server only "sees" the web server via TCP/IP, it never deals directly with the client so the SQL Server won't know the IP address of the client.
-PatP|||See as I told you before I get an error at this line -
INSERT #ip EXEC master..xp_cmdshell 'ipconfig' (as per the code given by you)
Insert #temp exec master..xp_cmdshell 'ipconfig' (and as per the code written by me)
which gives error saying that -' insert and execute statments cannot be nested'.
Also, I would like to tell that I was working on testing environment means on local server but not on live server since it this works on testing server and only we can upload and test on live server. Live Server is SQL Server 2003
Now when I uploaded your pinging version of Stored Procedure I get an error telling that master DB owner is someone else and when I give '[dbo].master' for executing the xp_cmdshell it gives another error saying that server is not the sysservers list use 'sp_addlinkedserver' Stored Procedure to add the server in the sysservers list
Pls help|||Yes, as described below is very much true
If I've described the configuration that you've currently got, then your SQL Server can only get the client machine's IP address from the web server, because the SQL Server only "sees" the web server via TCP/IP, it never deals directly with the client so the SQL Server won't know the IP address of the client.|||So all users will appear to have the same IP... (which kinda ruins what you're trying to achieve, no?)|||Some users will access the application on web server from UK, some from US, and some from India. So all will connect to same web server on which the application resides but will have different IP addresses of their machines.|||You do realize that the IIS logs keep all of the information you are after. Right?|||You should be handling this at the application level and/or analyzing logs as MCrowley suggests. It is not physically possible to do at the database level per Pat's assertion.
Also, if you enabled the ability to fire xp_cmdshell under the privileges that your web app uses, I suggest you disable it right now.|||But how to exactly do that ? how to get it from IIS log ?|||Let's go back to the original requirement for a second. You need to get the webserver client's IP address, so you can put that into an audit trail for any updates. Is this about what you need?|||See I am inserting the data in my Audit table in the trigger while insert/update/delete and I have the log file created by IIS which has all IP Addresses. My problem is how to get the IP from log file and insert in the table since insert in the table is done in the trigger while insert/update/delete|||This sounds like it should be done by passing the variable from the client-side.
For example, if you're users are accessing the databse through a web front end then pick up the value from the client's workstation and pass it on submit of a query as one of the values.
Make sense?|||Why would this need to be done by a trigger? Why can't the webserver pass this information to you? Are there users accessing this data by methods other than the webserver?|||Hi there,
Does it have to be IP address? will hostname do? with a lookup table for IP address or something.
If you want the hostname then do this:
select hostname from sysprocesses
where spid = @.@.spid|||True, but in an IIS implementation both the web server's address and hostname ought to be constants. The original poster wants the IP address of the client, which is not available to the SQL Server.
-PatP
