I admit that I am very much a newbie when it comes to doing anything advanced with databases, so forgive this question which may sound stupid as I am sure it has an obvious answer. Here's my situation:
--I installed ISA2004 for a client who wanted very detailed reports on the sites his users visit. I planned to add WebSpy as a reporting tool.
--I didn't realize that ISA2004 logged in MSDE file format (ldf and mdf) by default and didn't think to check it. Big mistake on my part!
--I have changed logging to go to standard files. In the meantime, the client absolutely insists that he be able to import and analyze his existing log file data for the past several weeks into WebSpy. WebSpy says it supports MSDE, and support said setup was "easy".
How on earth does one get these ISA Log files into a database? The good news is that MS SQL 2000 is also running on this same server. Here is what I have done thus far, and I don't think it has helped any (please chime in):
1. Installed MSDE with instance Old_ISA. 2. Tried to import the .ldf and .mdf files into full-blown SQL (not MSDE) through its import/export utility. It appears that .ldf and .mdf file types are not supported. (?)
How can I accomplish making a usable MSDE or MS-SQL database that I can name using either full-blown MS SQL 2000 or MSDE? Or is there a way to convert these .mdf and .ldf files to either text or other ISA format so I can actually use them? I noticed that there is a MSSQL$MSFW instance which appears to be related to ISA, but those files have not been updated since the initial install of ISA.
I know this is more of a database question (and you can tell I am not an expert!), but I have recommended ISA2004 for this client based upon its reporting abilities and this client feels that I have not lived up to my promise. I was hoping Dr. Shinder's new book would help shed some light on how to use MSDE, but I am afraid that I am still in the dark (probably due to my lack of knowledge regarding MSDE). Please help! Thanks in advance for any advice you can provide.
Sorry for the delay--I got hit hard with the flu bug going around. My dilemma is that I don't know how to load the data files into MSDE. From what I have read, it appears I can only load one day's worth at a time, too, which is pointless for this client because he wants to look at 1-2 weeks worth of data at one time in a single report. Even if it's less elegant, I like the older text file logging better because it was much easier to work with!
Fortunately, right before I got sick, I changed the logging type on the server to the w3c file format, which to my understanding is like a glorified text file (with XML-like markup tags, etc.). I will find out tomorrow if WebSpy can load multiple days of these files simply be pointint to the directory path, of which their support says should be easy to do.
I will post to let everyone know if this workaround does the job for me. However, an article about how to load/alter/change MSDE databases regarding ISA logs would be very useful to us database newbies at some point in the future.
I haven't been here in a while, as our ISA 2004 setup has been working great (after using this site extensively in the begining & reading the two ISA2k books from the Schinders')! Regarding viewing the data in the MSDE file, i also had this question, and this is what i've done to succesfully view the logs:
1) Install MSDE on a machine with a pretty good amount of RAM. 2) Ensure MS Access is also installed on the machine -- I'm using Access XP/2002 3) Open Access, select File --> New --> (on right side) "Project (Existing Data)" 4) Enter a name & location for where to save your project -- desktop is fine, click "Create" 5) If following these instructions, the server name will be your local workstation that you installed MSDE. 6) Select 1st radio button to, "Use Windows NT Integrated security" 7) Select next radio button to, "Attach a database file as a database name." 8) On first line give a name of your liking. 9) On 2nd line, select, "..." to browse to the .mdf -- I usually copy the .mdf file locally to reduce network lag (beforehand). 10) Click "OK"
Viola! The MSDE logs are now in Access and you can view the tables, export to text, run queries on the logs, reports, etc! Moreover, the data logs stored in MSDE will have continuous data since the last time the MSDE engine was stopped in ISA, therefore you don't have to import files for each day
Let me know how it goes! Edgardo
(Just putting my $.02 for a bid on the autographed ISA 2004 book )
RE: Totally Confused about MSDE Logging - 27.Jan.2005 6:21:00 PM
You could have done the same thing (attach the DB)in SQL server or MSDE. In SQL Server Enterprise Manager, you right-click the folder "Databases" of the instance, then select "All tasks" then "Attach Database"
I tried what grinn253 posted, to view my MSDE logs. I'm confused by a couple of things, first, the post says to "Install MSDE...". Since I'm using MSDE Logging, isn't MSDE installed as part of the ISA installation? (I'm doing this on ISA)
I went on, following the instructions (also using AccessXP), until I got to number 9 "...browse to the .mdf...) there are quite a few .mdf's in the logging folder, all with the same modified date. Why so many different .mdf's? I tried choosing several different ones, but access always reported an error.
Since nothing was said about doing anything with ODBC, I didn't, but is that necessary?
I have a fairly simple network, not SQL Servers. I just want to be able to view logs over time, preferebly in Access, since I'm familiar with it. I've tried using the log viewer for queries, but it takes quite a while, plus it does seems like it would be a lot simpler to print in Access.
Any suggestions; either with the original attempt or a suggestion as to this in a different way?
Still trying to figure out if this is possible; to use MSDE, so can have realtime info available, but also to send data to access database at same time, so can more easily (in my opinion, anyway) to query & print the data.
quote:Originally posted by moose: I tried what grinn253 posted, to view my MSDE logs. I'm confused by a couple of things, first, the post says to "Install MSDE...". Since I'm using MSDE Logging, isn't MSDE installed as part of the ISA installation? (I'm doing this on ISA)...
Sorry i should've specified (clearly) to perform the above operations on a different machine from your ISA server. Office or other tasks/programs I don't think are recommended to be running with the ISA firewall. Most likely the error aroused from trying to open a file already in use by ISA/MSDE. Which is sort of why i mentioned to copy the .mdf locally (onto a different machine than ISA), ensuring the file isn't locked.
Summary: As I've seen mentioned on this board at times, 'do not use ISA for anything else but firewall.' Usually in regards to having ISA/DC combos.