September 27, 2001 at 11:19 am
I am creating a a web based smtp log file reporting system. I want to create a procedure that takes a column (ip) and counts the number of times a particular ip appears, then adds a temporary column to the table for that particular ip to store the count. Ultimately there will be up to 30+ temporary columns.I'm not sure how to go about doing it or if that is the best way. Here is what I have that does not seem to work the way I want it too, but may give a better idea as to what I'm trying to accomplish.
CREATE PROCEDURE SP_SMTPSTATS AS
SELECT count(ip) mcd
FROM test
where ip = '10.0.0.5'
count(ip) mcdd
from test
where ip = '10.0.0.4'
GO
September 27, 2001 at 11:54 am
ok, you didn't give a whole heck of a lot of info, but here's a suggestion.
Rather than add a column to a table to store IP counts, use a separate table.
So, your SMTP log could be uploaded into a temp table, the statistics then calculated and stored into a permant SMTPStats table. So, in this example, I have a assumed that your smtp log file has already been uploaded/converted into a table called SMTP_LOG_TEMP, and that the table that contains all your smtp statistics is called SMTP_STATS. SMTP_STATS in this example has 2 fields: IP_ADDRESS, and IP_COUNT.
-- First, get the IPs, and their counts from the latest log file
INSERT INTO #SMTP_STATS_WORK
SELECT IP, Count(IP) as LATEST_COUNT
FROM SMTP_LOG_TEMP
GROUP BY IP
-- Second, update the SMTP_STATS table
UPDATE SMTP_STATS
SET a.IP_COUNT = a.IP_COUNT + b.LATEST_COUNT
FROM SMTP_STATS a, #SMTP_STATS_WORK b
WHERE a.IP_ADDRESS = b.IP
-- Lastly, insert any IPs from this run, that weren't in the SMTP_STATS table already
INSERT INTO SMTP_STATS (IP_ADDRESS, IP_COUNT)
SELECT IP, LATEST_COUNT
FROM #SMTP_STATS_WORK
WHERE IP not in (SELECT IP_ADDRESS FROM SMTP_STATS)
Good luck!
Matthew Mamet
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy