Importing IAS log files into a database

Applies To: Windows Server 2003, Windows Server 2003 R2, Windows Server 2003 with SP1, Windows Server 2003 with SP2

Importing IAS log files into a database

When you select Database compatible file format for IAS accounting and authentication logs, the information is recorded in a comma-delimited format. The consistent structure of this format makes it possible to use a database program, such as Microsoft Access, to import a log into a table, which presents data in a way that is easily viewed and interpreted. And, after importing a log, you can filter and analyze the data in a way that is useful for your accounting and auditing processes.

Importing IAS log files into a table with Microsoft Access 2002 or Microsoft Access 97

The table that you create with Access is formatted according to the type of data that is contained in each field. Although this example shows you how to create a new table, you can also import a log into an existing table.

Before creating the table, add the two lines in the next paragraph to the beginning of the log file created by IAS. The first line contains the attribute names of each field in the log and Access uses them to create the field names in its table. Using attribute names in the table makes it easier to interpret the log entries. The second line is used by Access to set up the appropriate data type for each column in its table. After you import the log file, you should delete these entries from the Access table.

ComputerName,ServiceName,Record-Date,Record-Time,Packet-Type,User-Name,Fully-Qualified-User-Name,Called-Station-ID,Calling-Station-ID,Callback-Number,Framed-IP-Address,NAS-Identifier,NAS-IP-Address,NAS-Port,Client-Vendor,Client-IP-Address,Client-Friendly-Name,Event-Timestamp,Port-Limit,NAS-Port-Type,Connect-Info,Framed-Protocol,Service-Type,Authentication-Type,NP-Policy-Name,Reason-Code,Class,Session-Timeout,Idle-Timeout,Termination-Action,EAP-Friendly-Name,Acct-Status-Type,Acct-Delay-Time,Acct-Input-Octets,Acct-Output-Octets,Acct-Session-ID,Acct-Authentic,Acct-Session-Time,Acct-Input-Packets,Acct-Output-Packets,Acct-Terminate-Cause,Acct-Multi-Ssn-ID,Acct-Link-Count,Acct-Interim-Interval,Tunnel-Type,Tunnel-Medium-Type,Tunnel-Client-Endpt,Tunnel-Server-Endpt,Acct-Tunnel-Conn,Tunnel-Pvt-Group-ID,Tunnel-Assignment-ID,Tunnel-Preference,MS-Acct-Auth-Type,MS-Acct-EAP-Type,MS-RAS-Version,MS-RAS-Vendor,MS-CHAP-Error,MS-CHAP-Domain,MS-MPPE-Encryption-Types,MS-MPPE-Encryption-Policy
x,x,01/01/1999,12:00:00,0,x,x,x,x,x,x,x,x,0,0,x,x,01/01/1999 12:00:00,0,0,x,0,0,0,x,0,x,0,0,0,x,0,0,0,0,x,0,0,0,0,0,x,0,0,0,0,x,x,x,x,x,0,0,0,x,0,x,x,0,0

• To create the table using Microsoft Access 2002

• To create the table using Microsoft Access 97

To create the table using Microsoft Access 2002

1. Rename log files and give them a file name extension of .txt.

2. In Access 2002, click Blank Database.

3. In File New Database, specify a FileName, and then click Create.

4. Click File, click Get External Data, and then click Import.

5. In Import, in Files of type, select Text Files, locate the IAS log file, select it, and then click Import.

6. In the Import Text Wizard, click Advanced.

7. In Import Specification:

   • In File Format, select Delimited.

   • In Field Delimiter, select the , (comma).

   • In the Text Qualifier, select " (quotation mark).

   • In the Dates, Times, and Numbers, select Four Digit Years and Leading Zeros in Dates, and then type the appropriate Date Order (such as MDY), Date Delimiter (such as / or forward slash), Time Delimiter (such as : or colon), and Decimal Symbol (such as . or period).

8. In the Import Text Wizard dialog box, click Next, select First Row Contains Field Names, and then click Next.

9. Select In a New Table, and then click Next.

10. Leave the defaults in Field Options, and then click Next.

11. Select Let Access add primary key, and then click Next.

12. In Import to Table, type the name of the new table, click Finish, and then click OK.

13. In the FileName**:Database** dialog box, select the name of your database, and then click Open to view your table.

To create the table using Microsoft Access 97

1. Rename log files and give them a file name extension of .txt.

2. In Access 97, click File, and then click New Database.

3. In New, click Blank Database, and then click OK.

4. In File New Database, specify a FileName, and then click Create.

5. In the FileName**:Database** dialog box, click New.

6. In New Table, select Import Table, and then click OK.

7. In Import, in Files of type, select Text Files, locate the IAS log file, select it, and then click Import.

8. In the Import Text Wizard, select Advanced.

9. In Advanced:

   • In File Format, select Delimited.

   • In Field Delimiter, select the , (comma).

   • In the Text Qualifier, select " (quotation mark).

   • In File Origin, select Windows (ANSI).

   • In the Dates, Times, and Numbers, select Four Digit Years and Leading Zeros in Dates, and then type the appropriate Date Order (such as MDY), Date Delimiter (such as / or forward slash), Time Delimiter (such as : or colon), and Decimal Symbol (such as . or period).

10. In Import Text Wizard, click Next, select First Row Contains Field Names, and then click Next.

11. Select In a New Table, and then click Next.

12. Leave the defaults in Field Options, and then click Next

13. Select Let Access add primary key, and then click Next.

14. In Import to Table, type the name of the new table, click Finish, and then click OK.

15. In the FileName**:Database** dialog box, select the name of your database, and then click Open to view your table.