July 16, 2014 at 3:36 am
Hi everyone. I need to be able to list a record set where a particular field has changed, I've written a program that records my ipaddress as well as the date it was recorded. I'd like to be able to show each time the ipaddress has changed, how is this possible ? many thanks
USE Misc
GO
-- Creating IPaddresses Table
CREATE TABLE IPaddresses([IPAddress] VARCHAR(20),[dte] [smalldatetime])
GO
-- Inserting Data into Table
INSERT INTO IPaddresses(IPAddress,dte)
VALUES('86.148.129.66','20-jun-2014 13:00:00')
INSERT INTO IPaddresses(IPAddress,dte)
VALUES('86.148.129.66','20-jun-2014 13:30:00')
INSERT INTO IPaddresses(IPAddress,dte)
VALUES('86.148.129.66','20-jun-2014 14:00:00')
INSERT INTO IPaddresses(IPAddress,dte)
VALUES('81.222.435.80','20-jun-2014 14:30:00')
INSERT INTO IPaddresses(IPAddress,dte)
VALUES('81.222.435.80','20-jun-2014 15:00:00')
INSERT INTO IPaddresses(IPAddress,dte)
VALUES('81.222.435.80','20-jun-2014 15:30:00')
INSERT INTO IPaddresses(IPAddress,dte)
VALUES('81.222.435.80','20-jun-2014 16:00:00')
INSERT INTO IPaddresses(IPAddress,dte)
VALUES('66.113.165.99','20-jun-2014 16:30:00')
July 16, 2014 at 4:54 am
The easiest way is to use a trigger and the updated() function on this field to catch changes. I'd build an audit table and record the changes there, adding a notification if you really need it.
July 16, 2014 at 5:07 am
You can also look to extended events as a way to capture this sort of behavior, filtering to ensure you're only capturing queries against the table and only those queries that result in a change to that column.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 16, 2014 at 5:13 am
Thanks for the replies
is there no way of running a stored procedure on the existing table and for it to only return a recordset of any changes?
July 16, 2014 at 7:15 am
Sure, but how do you determine if there has been a change? If you always update a datetime column, maybe you can just check for updates since the last one. But you have to a measure to compare against, or you have to capture the event. We've suggested to mechanisms to capture the event.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 17, 2014 at 1:16 am
Hi, I'm more interested in running like a report to show the IP changes rather than getting a notification
July 17, 2014 at 1:33 am
Then you need a way to mark the IP as changed (UpdateDate or something similar) or you have to capture the full data set and then compare it to spot changes. There's nothing in SQL Server that will just tell you that data is different than it was at some undetermined point in the past.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 17, 2014 at 1:36 am
I was wondering if sql read each line in date order and as it went through it could tell if the previous line was different to the current one, if so add it to a temp table or so
July 17, 2014 at 1:49 am
Quick thought on this problem, if the IP address is the unique key with a temporal attribute of Recorded Date (dte), there is no way of telling if an IP address has changed or it is an appearance of a new IP address. On the other hand, detecting change of the dte of an IP is straight forward.
😎
July 17, 2014 at 3:09 am
Thanks, dte will always change as it's a date and time field and is updated every 30 mins, the ipaddress field could remain the same over several days and then suddenly change
July 17, 2014 at 3:44 am
Let say you have a IP Addess
255.255.255.0
is changed to
255.255.255.1
how would you like to see this result set?
July 17, 2014 at 3:44 am
mick burden (7/17/2014)
Thanks, dte will always change as it's a date and time field and is updated every 30 mins, the ipaddress field could remain the same over several days and then suddenly change
Lets phrase it differently, what does an IP address relate to?
If the set is X{IP,DTE} with a member value of xxx.xxx.xxx.xxx,1900-01-01 and another member value of xxx.yyy.xxx.xxx,1900-01-02, how could you determine the relationship?
😎
July 17, 2014 at 4:13 am
Basically my program every 30 minutes records the date/time and the current IP Address of my PC onto a table. I want to be able to run a query that just returns when the ipaddress changed over the last few weeks or so, so I get a report something simular to:-
10/jun/2014 06:30:00 192.168.1.65
10/jun/2014 09:00:00 192.168.1.43
12/jun/2014 14:00:00 192.168.1.02
21/jun/2014 04:00:00 192.168.1.43
July 17, 2014 at 4:29 am
mick burden (7/17/2014)
Basically my program every 30 minutes records the date/time and the current IP Address of my PC onto a table.
When you say this, how exactly are you recording this record ?
Let say
Date : '01-Jun-2014 13:00:00'
IP Address : 192.168.1.1
After 30 Min
Date : '01-Jun-2014 13:30:00'
IP Address : 192.168.1.2
The above two records, how you are managing those records? the desired output is not a very difficult one if you have the data in a table.
July 17, 2014 at 4:33 am
That's more or less how I'm inputting the data, the thing is the IP might not change for a couple of days but the data is still being added to the table which is what I want for various reasons, but I'd like to be able to run a query that only lists up when the IP address on any given record is different to the previous record
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply