DB gurus: SQL question

[R00k]

I have a table (SQL Server 2000) that constantly collects info on the machines on our network - it looks something like this:

Code: Select all

UID          AssetTag           ComputerName            UserName             TimeStamp
001          4232                NAS-FN4232             FFletch              03/13/2008 14:20:24

The primary key is the UID column. This table gets populated via user login script by every computer on the network every time it's logged in, so it tends to get large fairly quickly.

What I'd like to do, is purge any records older than say, 5 days. BUT I don't want to purge a record if that is the only record in the table for that ComputerName.

So I need to somehow select based on ComputerName, and anywhere there are duplicates, to delete the duplicate records if their TimeStamp is more than 5 days old.

Can this be done with plain old SQL? Because I want to schedule this as a SQL Job, and would like to avoid having to run any external programs/scripts in the Job, if possible.

[Foo]

You could set a stored procedure that fires off when a new entry arrives in the table. Looks for other entries in the table with the same computer name, eliminates them if over 5 days old.

[R00k]

Well, here's how they are getting into the table...

When a user logs in, the login script, using mostly OS system variables, writes an .ini file to a share on the server, which is named <ComputerName>.ini. (this was already in place when I started with the company, I just changed the login script to write them to my DB server instead of the old one.)

I have a SQL Job that runs every five minutes, in which a perl script reads all the .ini files in the directory, parses the needed data into a tab-separated text file, and moves the .ini files to an Archive directory. The second step of the Job kicks off a SQL DTS Package, which uses field mappings to mass-import the data in the file to this table all at once.

So, the records aren't actually going into the table one at a time with SQL INSERT statements.

Knowing that, do you think the method you're describing would still work?

[Foo]

I think your system is horrible, and you should feel horrible.



Aside from that, I think it would still work. However, if your clients are all pretty modern, you should be able to just insert directly into the database from the login script and skip the whole file scavenging step.

[^misantropia^]

Got a bus to catch so no time to write out the full query, but look up SQL's HAVING clause.

[Foo]

Something like:

---------------------------
Dim OdbcDSN
Dim connect, sql, resultSet

OdbcDSN = "DSN=EMPRecords;UID=connlogger;PWD=l0ginscr1pt"
Set connect = CreateObject("ADODB.Connection")
connect.Open OdbcDSN

sql="INSERT into tblLoginRecords **date, time, username, computer etc**"

Set result = connect.Execute(sql)

result.Close
connect.Close
Set connect = Nothing
---------------------------

[R00k]

I think your system is horrible, and you should feel horrible.



Aside from that, I think it would still work. However, if your clients are all pretty modern, you should be able to just insert directly into the database from the login script and skip the whole file scavenging step.

lol, it's not the most elegant way to do things i agree.

i'm essentially writing my own inventory and deployment tracking system because the company is too cheap to buy one - and if i press the issue too much, my VP will invariably decide to go out and find a shoddy solution that was written hastily by somebody at another one of our sites, and which wouldn't address our needs (this is how we got our shitty, FrontPage-generated helpdesk system that runs on an Access database >=\ ). the alternative is installing SMS, which we already own a license for, and thus losing all control over it to corporate. the database wouldn't reside here, and the administrative functions would be severely limited to what they allowed us to have. i wouldn't even be able to install it - i would have to give them the name of one of our servers to put it on, and they would install it the way they want.

so i'm trying to make the best of what we already have, with my limited coding and DBA experience.

all things considered, i'm pretty proud that i've been able to make this much progress in no more time than i've put into it. creating DTS packages in SQL is no mean feat for someone as inexperienced as myself, even when you can find samples and tutorials online.

[R00k]

Got a bus to catch so no time to write out the full query, but look up SQL's HAVING clause.

Thanks for the tip, checking it out now.

[R00k]

Something like:

---------------------------
Dim OdbcDSN
Dim connect, sql, resultSet

OdbcDSN = "DSN=EMPRecords;UID=connlogger;PWD=l0ginscr1pt"
Set connect = CreateObject("ADODB.Connection")
connect.Open OdbcDSN

sql="INSERT into tblLoginRecords **date, time, username, computer etc**"

Set result = connect.Execute(sql)

result.Close
connect.Close
Set connect = Nothing
---------------------------

That's definitely a better solution. Even though all of our clients are Win2000 or XP, our login scripts are still completely KIXTart. I've thought about converting them to VBScript, but I simply don't have the time right now. And I hate to just throw in calls to VBScripts, to make it even sloppier than it already is.

I'd rather just use GPOs with VBScript to do everything, but guess what -- I don't have permissions to create/edit GPOs even on our own OUs (even though I have the highest level of access rights in our local organization).

Here's something else that has made me cringe ever since I started working there: our entire organization, spanning across the whole country with tens of thousands of employees, is all under a SINGLE DOMAIN. They say that they hired MS consultants to come in and help design our forest when they built it, but I have a hard time believing that since I have worked with MS consultants doing Active Directory deployments before. I can't imagine a scenario where an MS consultant, or even an MVP, would tell a company with such a large WAN that they should only create one domain.

[PhoeniX]

How about a query like this:

Code: Select all

DELETE FROM `logs` WHERE DATEDIFF(CURRENT_TIMESTAMP(), STR_TO_DATE(`logs`.`timestamp`, '%m/%d/%Y'))>=5;

It should delete all entries from the logs table where the date comparison between the current timestamp (i.e. now) and the stored timestamp us greater than 5 days.

That's using MySQL's functions though, I have no idea if it will work on MSSQL.

[Dave]

Write a trigger to delete old entries for a machine when new records for that machine are inserted into the table. Just make sure it only deletes entries older than five days.

http://msdn2.microsoft.com/en-us/library/ms189799.aspx

[R00k]

So does a DTS Mass Import function activate triggers for INSERT events? I'm not sure what kind of SQL lies behind DTS objects.

[R00k]

How about a query like this:

Code: Select all

DELETE FROM `logs` WHERE DATEDIFF(CURRENT_TIMESTAMP(), STR_TO_DATE(`logs`.`timestamp`, '%m/%d/%Y'))>=5;

It should delete all entries from the logs table where the date comparison between the current timestamp (i.e. now) and the stored timestamp us greater than 5 days.

That's using MySQL's functions though, I have no idea if it will work on MSSQL.

The only caveat to that, is that I don't want to delete the record if its ComputerName value is unique in the table - only if a newer ComputerName record exists.

[Dave]

So does a DTS Mass Import function activate triggers for INSERT events? I'm not sure what kind of SQL lies behind DTS objects.

I dunno, just try it out and see what happens.. But if you do that query above, you delete all of your entires, including any record of machines that hadn't been logged into during the last 5 days. If you think you might ever need that info, you probably don't want to delete everything.

edit: oh, i see yo noticed that...

[PhoeniX]

heh I missed that part. How about something like this, possibly the worse written SQL statement available, but still.

Code: Select all

SELECT * FROM `logs` l WHERE DATEDIFF(CURRENT_TIMESTAMP(), STR_TO_DATE(l.`timestamp`, '%m/%d/%Y'))>5 AND (
SELECT COUNT(*) FROM `logs` lo WHERE lo.`computername`=l.`computername`)>=2

[Dave]

What I've been doing lately is abstracting all of my database applications into WCF web services, so any time I do something like this, all I need to do is invoke a service and not expose the calls I'm making to the SQL server. I'm not sure where your logon script is running and who can execute SQL on the server, but it seems dangerous to run that on the client--especially if you want to know who's connecting to what for security purposes.

[R00k]

You didn't read the whole thread.

The clients don't ever access the server.

What I'd like to do with this is schedule it to be a job that runs regularly on the server, through the SQL Agent.

Thanks Pho - I don't know how different MySQL and MSSQL are with those date/time operations, but I'll give it a shot.

I also found this, which looks like it might be helpful:
http://www.sql-server-performance.com/a ... es_p1.aspx

[PhoeniX]

If you switched to storing UNIX timestamps you'd probably be able to remove half of those date functions - plus you can do more with them.

[^misantropia^]

MySQL-flavoured, but you'll know what to do. You should be able to omit the first sub-select in MSSQL.

Code: Select all

DELETE FROM x
      WHERE UID IN (
     SELECT UID FROM (
     SELECT x.*
       FROM x
 INNER JOIN x y ON x.ComputerName = y.ComputerName
      WHERE x.TimeStamp < NOW() - INTERVAL 5 DAY
   GROUP BY x.UID
     HAVING COUNT(*) > 1
    AND NOT x.UID = MIN(y.UID))   
         AS work_around_mysql_bug)

[R00k]

Awesome man, thanks for that. I'll give it a go in the morning and see what I can do with it.

[R00k]

I don't understand what you're doing with the JOIN there. I only have a single table that I'm using for this dataset. It doesn't look like you're creating a temp table on the fly or anything, so I'm a little stumped.

BTW if you're curious, MSSQL has different date functions. The relevant part winds up looking like this

Code: Select all

WHERE DATEDIFF(day, LoginTime, GETDATE()) < 5

You can also use CURRENT_TIMESTAMP instead of the GETDATE().

[R00k]

I'm pretty close to having this done, but I feel like a noob because I can't get the syntax right.

Code: Select all

DELETE *
FROM
	(
	SELECT ComputerName
	FROM tbl_PurgeTestData
	GROUP BY ComputerName
	HAVING COUNT(ComputerName) > 1
	)
WHERE DATEDIFF(day, LoginTime, GETDATE()) > 3
ORDER BY LoginTime DESC

I know 'WHERE' won't work there, but I don't know how to modify it without it.

edit: duh, of course it won't work that way because I can't select * from a subselection in which I'm only selecting the ComputerName. >=\

[^misantropia^]

I don't understand what you're doing with the JOIN there. I only have a single table that I'm using for this dataset.

Note that it joins on the same table, it's to get all records with the same ComputerName (and, farther below, to filter the duplicates).

[^misantropia^]

PS: it's 'DELETE FROM', not 'DELETE * FROM' - sans asterisk.

[R00k]

Yea, I have it typed as a SELECT statement instead of delete - I just pasted that here and changed it to delete w/out dropping the asterisk.

I realized you were doing a self-join after I posted that. I still can't get this to work though. =\