{"id":1721,"date":"2013-11-12T00:00:00","date_gmt":"2013-11-12T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/microsofts-log-parser-utility-swell-etl\/"},"modified":"2021-05-11T15:57:23","modified_gmt":"2021-05-11T15:57:23","slug":"microsofts-log-parser-utility-swell-etl","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/tools-sql-server\/microsofts-log-parser-utility-swell-etl\/","title":{"rendered":"Microsoft’s Log Parser Utility: Swell ETL"},"content":{"rendered":"
\n

First off, Microsoft’s Log Parser utility is not<\/i> a SQL Server tool. Log Parser is a powerful Windows command-line utility that can extract data from a variety of sources-IIS logs, XML and CSV files, Active Directory objects, Network Monitor capture files, and the Windows registry, to name a few-and output the data to various files and systems, most notably SQL Server. In fact, Log Parser makes importing data into a SQL Server database so simple, you’ll wonder why you haven’t been using the tool all along.<\/p>\n

At the core of the Log Parser utility is a “SQL-like” engine that processes data as it’s retrieved from the source and sent to the destination. You can think of Log Parser as a mini extract, transform, and load (ETL) application that uses input formats<\/i> to extract data from its source and output formats<\/i> to send the data to its destination.<\/p>\n

An input format provides the source data to the engine as a record set, similar to the way rows are stored in a table. Each input format serves as a record provider specific to the source from which the data is retrieved. For example, you would use the xml<\/code><\/b> input provider to retrieve data from an XML file.<\/p>\n

Output formats also present the processed information as record data, with each output format specific to the target destination type. If you were sending data to a SQL Server database, for instance, you would use the sql<\/code><\/b> output format.<\/p>\n

For details about the available input and output formats supported by Log Parser, as well as information about other features, see the Log Parser help file (LogParser.chm). The file is added to the directory where Log Parser is installed when you do a complete installation or you include the documentation component as part of a custom installation. You can download Log Parser from the Microsoft Download Center<\/a>. There you will also find installation instructions.<\/p>\n

Using Log Parser to retrieve data<\/h1>\n

Log Parser comes in two versions: a command-line executable and a DLL containing COM objects that applications can use to run Log Parser operations. This article focuses on the command-line utility and how you can use it to import data into a SQL Server database.<\/p>\n

After installing Log Parser, you’ll likely want to modify your operating system’s Path<\/code><\/b> environmental variable to include the folder where Log Parser is installed. That way, you can run Log Parser at a command prompt without having to navigate to that folder. <\/p>\n

The process you use to modify the Path<\/code><\/b> system variable will vary from one Windows operating to the next. In Windows 7, for example, one way to access the Path<\/b> variable is to open Control Panel, click System<\/code><\/b> and<\/code><\/b> Security<\/code><\/b>, and then click System<\/b>. In the left pane, select Advanced<\/code><\/b> system<\/code><\/b> settings<\/code><\/b>, which launches the System<\/code><\/b> Properties<\/code><\/b> dialog box. On the Advanced<\/code><\/b> tab, click the Environment<\/code><\/b> Variables<\/code><\/b> button. When the Environmental<\/code><\/b> Variables<\/code><\/b> dialog box appears, select the Path<\/b> variable in the System<\/code><\/b> variables<\/code><\/b> list, and then click Edit<\/code><\/b>. This launches the Edit<\/code><\/b> System<\/code><\/b> Variable<\/code><\/b> dialog box. In the Variable<\/code><\/b> value<\/code><\/b> text box, append the current value by adding a semi-colon and the path to the Log Parser directory, as in ;C:\\Program<\/code><\/b> Files<\/code><\/b> (x86)\\Log Parser<\/code><\/b> 2.2<\/code><\/b>.<\/p>\n

Once you’ve modified the Path<\/code><\/b> variable, open a Windows command prompt and run the following command:<\/p>\n

logparser \/h<\/pre>\n
The command returns basic help information about the Log Parser utility.  It also provides an easy way to test whether Log Parser has been properly installed and the Path<\/code><\/b>  variable updated. Figure 1 shows the information returned by the command. Notice that it includes details about the  command syntax, provides several examples, and describes how you can find additional help. You can refer back to this  information at any time by rerunning the command.<\/p>\n
\"1895-clip_image001-630x499.jpg\"<\/p>\n
Figure 1: Viewing the basic help available to  Log Parser<\/b><\/p>\n
Much of the information in Figure 1 will make more sense after we work  through a few examples. One thing to take out of this information, however, is that a basic Log Parser command normally  requires four components: the utility’s filename (logparser<\/code><\/b>),  an input format, an output format, and a SQL query that tells the Log Parser engine how the inputted data should be  outputted to the target file or system.<\/p>\n
Let’s look at an example to better understand how this works. The  following Log Parser command retrieves data from the  System<\/b> event log and outputs the  data to the command prompt window:<\/p>\n
logparser -i:evt -o:nat \"select * into stdout from system\"<\/pre>\n
The first argument after the  logparser<\/code><\/b> filename is  -i:evt<\/code><\/b>, which specifies that the  evt<\/code><\/b> input format should be used. The  evt<\/code><\/b> input format provides the structure necessary to  retrieve events from Windows event logs or event log backup files on local or remote computers. As the -i:evt<\/code><\/b>  argument shows, when you include an argument in your command, you generally specify the option name (preceded with a  hyphen), a colon, and the option setting. <\/p>\n
The next argument in the example command is  -o:nat<\/b>, which specifies that the  nat<\/code><\/b> output  format should be used to output the data to the command prompt window. The  nat<\/code><\/b> output format is the default output format in Log Parser  and is used when no other format is specified.<\/p>\n
The final argument is the actual query, enclosed in double quotes. The  query specifies what data to retrieve and where to send it. Notice that the query looks like a T-SQL SELECT<\/code><\/b>  statement. Although the SQL supported by the Log Parser engine is its own language, it is very similar to T-SQL,  specifically with regard to creating a  SELECT<\/code><\/b> statement.<\/p>\n
In our example, the query includes  SELECT<\/code><\/b>, INTO<\/code><\/b>  and  FROM<\/code><\/b> clauses. Log Parser requires the  SELECT<\/b> and  FROM<\/code><\/b> clauses and, in most cases, the  INTO<\/b> clause. The  SELECT<\/code><\/b> clause specifies what fields to return from the data  source. If an asterisk is used, all fields are returned. The  INTO<\/b> clause determines where to send  the data. When the destination is the command prompt window, the  stdout<\/b> value should be used. The  FROM<\/code><\/b> clause  specifies what to retrieve from the source, in this case, the  System<\/code><\/b> event log. <\/p>\n
Because the command outputs the data to the command window, you can see  the information you’re retrieving as soon as you run the command, as shown in Figure 2. Not surprisingly, because the  command retrieves all fields for all  System<\/code><\/b>  events, the window is quiet cluttered. <\/p>\n
\"1895-clip_image003-630x679.jpg\"<\/p>\n
Figure 2: Using Log Parser to retrieve data  from the System event log<\/b><\/p>\n
Notice the Press<\/code><\/b>  a<\/code><\/b> key<\/code><\/b>  instruction at the bottom of the screen. By default, Log Parser returns only 10 records at a time. To move on to the  next page of results, press any key. To view all the data, you must continue with this process until you loop through  the entire result set, or you can stop the process and return to the command prompt by pressing  Ctrl+C<\/code><\/b>.<\/p>\n
The preceding example demonstrates all the components that a command  should include. However, in some cases, you don’t have to specify a command element if Log Parser can clearly tell what  you’re trying to do. For example, if your query’s  FROM<\/code><\/b> clause specifies the  System<\/code><\/b> event log, you don’t need to specify the  evt<\/code><\/b> input  format, and if you’re outputting the data to the command prompt, you don’t have to include the  nat<\/code><\/b> output  format or include an  INTO<\/code><\/b> clause  in your query. As a result, the following command will return the same results as the command in the previous example:<\/p>\n
logparser \"select * from system\"<\/pre>\n
Even though Log Parser lets you skip command elements, Microsoft  recommends that you include all of them as part of coding best practices, which is the approach I take in the remaining  examples.<\/p>\n
Now let’s return to Figure 2. As you can see, the command dumps a lot of  information on us when, in fact, we might need to retrieve only specific fields. For many of the input formats, you can  view Log Parser’s help to learn what fields are available to a specific input format. You can find this information in  the LogParser.chm file or by entering a help-related command at the command prompt. (You can use a second command prompt  window to view help content, if that makes it easier for you.) For example, the following command retrieves data about  the evt<\/code><\/b>  input format:<\/p>\n
logparser \/h -i:evt<\/pre>\n
This is similar to the command we used to retrieve the basic help  information, except that we’ve added the  -i:evt<\/code><\/b> argument. Now Log Parser help returns the  information shown in Figure 3. As you can see, the data includes a list of fields available to the specified input  format.<\/p>\n
\"1895-clip_image005-630x597.jpg\"<\/p>\n
Figure 3: Viewing Log Parser help for the evt<\/code> input format<\/b><\/p>\n
Notice that a letter follows each field and is enclosed in parentheses.  The letter indicates the field’s data type. Log parser supports the following data types:<\/p>\n
    \n
  • Integer (I): A whole number.<\/li>\n
  • Real (R): A floating-point numeric value (decimal).<\/li>\n
  • String (S): A variable length Unicode character  value.<\/li>\n
  • Timestamp (T): A date and time value, with an  accuracy up to one-hundredth of a nanosecond.<\/li>\n<\/ul>\n
    When returning data to the command prompt window, knowing the data types  of the returned fields is normally not important, but later in the article, when we insert data into a SQL Server table,  that information becomes far more valuable. In the meantime, let’s return to the fields themselves. Once we know the  fields available to an input format, we can use that information to refine out command’s query. For example, the  following command returns only the EventTypeName<\/code><\/b>,  TimeGenerated<\/code><\/b>, and  SID<\/code><\/b> fields:<\/p>\n
    logparser -i:evt -o:nat \"select EventTypeName, TimeGenerated, SID into  stdout from system\"<\/pre>\n
    Note that a Log Parser command is a single-line command. In this example  and those to follow, the command might wrap across multiple lines because of margin limitations. However, do not press  Enter<\/code><\/b> until you’ve typed in the entire command.  <\/p>\n
    Returning now to our example, you can see that we modified only the  SELECT<\/code><\/b>  clause, replacing the wildcard (*) with the field names. Now when we run the command, the results are much more  readable, as shown in Figure 4.<\/p>\n
    \"1895-clip_image007-630x152.jpg\"<\/p>\n
    Figure 4: Retrieving specific fields from the  System event log<\/b><\/p>\n
    If you return to Figure 3, you’ll notice that the help information on  the  evt<\/code><\/b> input  format also includes a list of supported parameters. Input and output formats support optional parameters that help you  refine your command even further. In the following example, I’ve added the  -resolveSIDs:on<\/code><\/b> parameter after the  -i:evt<\/code><\/b>  argument and the  -rtp:20<\/b> parameter after the  -o:nat<\/code><\/b>  argument:<\/p>\n
    logparser -i:evt -resolveSIDs:on -o:nat -rtp:20 \"select EventTypeName,  TimeGenerated, SID into stdout from system\"<\/pre>\n
    The  -resolveSIDs:on<\/code><\/b> parameter tells Log Parser to convert the  SID<\/code><\/b> values  (security IDs) to their full account names. By default, this setting is turned off, but the parameter in the command  turns the option on. In addition, the  -rtp:20<\/code><\/b> parameter after the  nat<\/code><\/b> output format tells Log Parser to return 20 rows at a  time to the command prompt window, rather than the default 10 rows. Our example command now returns the results shown in  Figure 5.<\/p>\n
    \"1895-clip_image009-630x245.jpg\"<\/p>\n
    Figure 5: Adding input and output format  parameters to your Log Parser command<\/b><\/p>\n
    As you can see, the non-null  SID<\/code><\/b> values have been converted to full names and 20 rows  have been returned, rather than 10.<\/p>\n
    The SQL in Log Parser also supports an extensive set of built-in  functions you can use within the command’s query. For example, the query in the following command uses the  extract_token<\/code><\/b> and  to_date<\/code><\/b> functions:<\/p>\n
    logparser -i:evt -resolveSIDs:on -o:nat -rtp:20 \"select  extract_token(EventTypeName, 0, ' '), to_date(TimeGenerated), SID into stdout from system\"<\/pre>\n
    The extract_token<\/code><\/b>  function lets you extract a substring from a value. The function takes three arguments. The first is the original value,  in this case, the  EventTypeName<\/code><\/b> field. The second argument is a 0-based index  that indicates what part of the value to return. If  0<\/code><\/b> is specified, the function starts at the beginning of the  value and returns all data up to the character specified in the third argument, in this case, a single space. For  instance, the function as it is used in the example above will return word  Information<\/code><\/b> from the value  Information<\/code><\/b>  event<\/code><\/b>.<\/p>\n
    The to_date<\/code><\/b>  function returns only the date portion of a Timestamp<\/code><\/b>  value. Because I use the function on the  TimeGenerated<\/code><\/b>  field, the query will return only the dates from that field’s values, rather then the date and time. Figure 6 shows the  data now outputted by the query. Not surprisingly, the  EventTypeName<\/code><\/b>  field returns only a single word and the  TimeGenerated<\/code><\/b>  field returns only the date.<\/p>\n
    \"1895-clip_image011-630x243.jpg\"<\/p>\n
    Figure 6: Adding functions to your Log Parser  query<\/b><\/p>\n
    As you can see in Figure 6, the column names in the results use the  entire field expression from our query. We can fix this by assigning aliases to our fields within the SELECT<\/code><\/b>  list. In the following command, I’ve assigned the TypeEvent<\/code><\/b>  alias to the  EventTypeName<\/b> field, the  DateGenerated<\/b> alias to the  TimeGenerated<\/b> field, and the  SecurityID<\/b> alias to the  SID<\/b> field:<\/p>\n
    logparser -i:evt -resolveSIDs:on -o:nat -rtp:20 \"select  extract_token(EventTypeName, 0, ' ') as TypeEvent, to_date(TimeGenerated) as DateGenerated, SID as SecurityID into  stdout from system\"<\/pre>\n
    As you can see, assigning an alias is simply a matter of adding the  AS<\/code><\/b> keyword,  followed by the alias. You can use the alias anywhere in the query after the point it has been assigned. If the alias  includes spaces or special characters, as in  Customer's<\/code><\/b>  Address<\/code><\/b>, you should enclose it in brackets, just like you do  with column names. Once you’ve defined your aliases, you’ll find the results are even more readable, as shown in Figure  7.<\/p>\n
     \"1895-clip_image013-630x242.jpg\"<\/p>\n
    Figure 7: Adding column aliases to your Log  Parser query<\/b><\/p>\n
    But we’re not done yet. Just like a  SELECT<\/code><\/b> statement in T-SQL, we can add a  WHERE<\/code><\/b> clause to the command’s query. For example, the  SELECT<\/code><\/b>  statement in the following command limits the results to those whose  SourceName<\/code><\/b> value equals  Service<\/code><\/b> Control<\/code><\/b>  Manager<\/code><\/b>:<\/p>\n
    logparser -i:evt -resolveSIDs:on -o:nat -rtp:20 \"select  extract_token(EventTypeName, 0, ' ') as TypeEvent, to_date(TimeGenerated) as DateGenerated, SID as SecurityID into  stdout from system where SourceName = 'Service Control Manager'\"<\/pre>\n
    In this case, we’ve simply added the  WHERE<\/code><\/b> keyword, following by the equal sign and the  Service<\/code><\/b>  Control<\/code><\/b> Manager<\/code><\/b>  value, enclosed in single quotes. However, we can qualify the  WHERE<\/code><\/b> clause even further by adding a second expression,  connected to the first by the AND<\/code><\/b>  comparison operator:<\/p>\n
    logparser -i:evt -resolveSIDs:on -o:nat -rtp:20 \"select  extract_token(EventTypeName, 0, ' ') as TypeEvent, to_date(TimeGenerated) as DateGenerated, SID as SecurityID into  stdout from system where SourceName = 'Service Control Manager' and SID is not null\"<\/pre>\n
    As you can see, after the  AND<\/code><\/b> comparison operator, I’ve specified that the  SID<\/code><\/b> value  must not be null. Figure 8 shows what the results now look like after running the command. Clearly, the result set is  much more specific than before.<\/p>\n
    \"1895-clip_image015-630x252.jpg\"<\/p>\n
    Figure 8: Adding a  WHERE<\/code> clause to your Log Parser query<\/b><\/p>\n
    If you already know how to build a  SELECT<\/code><\/b> statement in T-SQL, you should have no problem  adjusting to the SQL in Log Parser. Basically, you want to build a query that retrieves exactly the data you need and  sends it to the selected target.<\/p>\n
    Inserting data into SQL Server<\/h1>\n
    Up to this point, the examples we’ve looked at have returned their  results to the command prompt window. But in most cases, you’ll likely want to persist those results. Log Parser lets  you output data to various types of text and image files or send the data to a SYSLOG server. In addition, you can  output the data to a SQL Server database or another ODBC-compliant database.<\/p>\n
    When sending the data to SQL Server, you must take into account the  field data types that Log Parser is outputting. The following table lists the Log Parser data types and how they  correspond with SQL Server data types:<\/p>\n\n\n\n\n\n\n\n
    \n
    Log Parser data type<\/b><\/p>\n<\/td>\n
    		\n
    Existing table in SQL Server<\/b><\/p>\n<\/td>\n
    		\n
    New table in SQL Server<\/b><\/p>\n<\/td>\n<\/tr>\n
    \n
    integer<\/p>\n<\/td>\n
    		\n
    bit, tinyint, smallint, int, bigint<\/p>\n<\/td>\n
    		\n
    int<\/p>\n<\/td>\n<\/tr>\n
    \n
    real<\/p>\n<\/td>\n
    		\n
    real, float, decimal<\/p>\n<\/td>\n
    		\n
    real<\/p>\n<\/td>\n<\/tr>\n
    \n
    string<\/p>\n<\/td>\n
    		\n
    char, varchar, nvarchar<\/p>\n<\/td>\n
    		\n
    varchar<\/p>\n<\/td>\n<\/tr>\n
    \n
    timestamp<\/p>\n<\/td>\n
    		\n
    date, time, smalldatetime, datetime<\/p>\n<\/td>\n
    		\n
    datetime<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    For example, if an input format returns a field with a  string<\/code><\/b> data  type, you can insert the data from that field into an existing SQL Server column configured with the  char<\/code><\/b>,  varchar<\/code><\/b>, or  nvarchar<\/code><\/b> data type, as long as the data fits into the  column’s size. On the other hand, if you let Log Parser create a table for the data, that target column is automatically  configured with the  varchar<\/code><\/b>  data type.<\/p>\n
    Let’s create a table for storing event data to demonstrate how importing  data into SQL Server works. The following T-SQL creates the  ScmEvents<\/code><\/b>  table in the  AdventureWorks2012<\/b> database (though  you can create the table in any database you choose):<\/p>\n
    USE AdventureWorks2012;\nGO\nIF OBJECT_ID('ScmEvents','U')IS NOT NULL\nDROP TABLEScmEvents;\nGO\nCREATETABLE dbo.ScmEvents\n(\n  TypeEventvarchar(25) NOT NULL,\n  DateGenerateddatetime NOT NULL,\n  SecurityIDvarchar(50) NULL\n);\nGO\n<\/pre>\n
    The columns in the  ScmEvents<\/code><\/b> table correspond to the fields that we retrieved  from the  System<\/code><\/b>  event log in the Log Parser command in the preceding example. Now we’ll update that command to send the data to the  ScmEvents<\/code><\/b>  table, as shown in the following example:<\/p>\n
    logparser -i:evt -resolveSIDs:on -o:sql -server:localhost\\sqlsrv2012  -database:AdventureWorks2012 -driver:\"sql server\" \"select extract_token(EventTypeName, 0, ' ') as TypeEvent,  to_date(TimeGenerated) as DateGenerated, SID as SecurityID into ScmEvents from system where SourceName = 'Service  Control Manager' and SID is not null\"<\/pre>\n
    Notice that I first modified the  -o<\/code><\/b> argument to use the  sql<\/code><\/b> output format. I follow this with several parameters  specific to that output format. The first is  -server<\/code><\/b>, which points to the  SqlSrv2012<\/code><\/b> instance on my local system. Next, I use the  -database<\/code><\/b>  argument to specify the  AdventureWorks2012<\/code><\/b>  database and then use the  -driver<\/code><\/b>  argument to specify SQL Server. However, SQL Server is the default driver for the  sql<\/code><\/b> output format, so you can omit this parameter if you  want. I then updated the query’s  INTO<\/code><\/b> clause so it points to the  ScmEvents<\/code><\/b> table. <\/p>\n
    Now when we run the command, the command prompt window displays only the  number of source elements, the number of elements sent to SQL Server, and the time it took to run the query, as shown in  Figure 9.<\/p>\n
    \"1895-clip_image017-630x145.jpg\"<\/p>\n
    Figure 9: Inserting event data into a SQL  Server database<\/b><\/p>\n
    To view the actual returned data, we must query the table within SQL  Server. Figure 10 shows part of the results returned from running a query in SQL Server Management Studio (SSMS) that  retrieved all rows and columns from the  ScmEvents<\/code><\/b>  table.<\/p>\n
    \"1895-clip_image019.png\"<\/p>\n
    Figure 10: Viewing the data saved to the SQL  Server database<\/b><\/p>\n
    If we were to rerun the command in the previous example, it would  continue to add rows to the table, which in some cases might be fine. However, there might also be times when you want  to delete the data in the target table before inserting the new information. To do so, you can use the  -clearTable<\/code><\/b>  parameter with the  sql<\/code><\/b> output  format, as shown in the following example:<\/p>\n
    logparser -i:evt -resolveSIDs:on -o:sql -server:localhost\\sqlsrv2012  -database:AdventureWorks2012 -driver:\"sql server\" -clearTable:on \"select extract_token(EventTypeName, 0, ' ') as  TypeEvent, to_date(TimeGenerated) as DateGenerated, SID as SecurityID into ScmEvents from system where SourceName =  'Service Control Manager' and SID is not null\"<\/pre>\n
    By default, the  -clearTable<\/code><\/b> parameter is set to off. In our example, we’ve  switched it to on. Now when we run the command, the data is first deleted from the table and then the new data is  inserted. Be aware, however, that using the  -clearTable<\/code><\/b>  parameter is comparable to running a  DELETE<\/code><\/b>  statement against the table. You might find it more efficient to run a  TRUNCATE<\/code><\/b> statement against the table from within SQL Server,  especially against a large table in a production database.<\/p>\n
    For the most part, when you insert Log Parser data into a SQL Server  table, the number and position of fields must match the target table, as was the case in the preceding two examples.  However, there is one exception to the rule. If the target table includes a column configured with the  IDENTITY<\/code><\/b>  property, you can specify that no data be inserted into that column. Let’s re-create our target table to demonstrate how  this works. The following T-SQL again creates the  ScmEvents<\/code><\/b>  table, but this time includes the  EventID<\/code><\/b>  column, which is configured with the  IDENTITY<\/code><\/b>  property:<\/p>\n
    USE AdventureWorks2012;\nGO\nIF OBJECT_ID('ScmEvents','U')IS NOT NULL\nDROP TABLEScmEvents;\nGO\nCREATETABLE dbo.ScmEvents\n(\n  EventIDint primarykey identity,\n  TypeEventvarchar(25) NOT NULL,\n  DateGenerateddatetime NOT NULL,\n  SecurityIDvarchar(50) NULL\n);\nGO\n<\/pre>\n
    When using Log Parser to insert data into a table with an  IDENTITY<\/code><\/b>  column, we can use the  -ignoreIdCols<\/code><\/b>  parameter with the  sql<\/code><\/b> output  format to prevent the utility from trying to insert data into that column. The following example includes the  -ignoreIdCols<\/code><\/b>  parameter with it set to on:<\/p>\n
    logparser -i:evt -resolveSIDs:on -o:sql -server:localhost\\sqlsrv2012  -database:AdventureWorks2012 -driver:\"sql server\" -clearTable:on -ignoreIdCols:on \"select extract_token(EventTypeName,  0, ' ') as TypeEvent, to_date(TimeGenerated) as DateGenerated, SID as SecurityID into ScmEvents from system where  SourceName = 'Service Control Manager' and SID is not null\"<\/pre>\n
    If you were to now query the table in SSMS, you would discover that the  IDENTITY<\/code><\/b>  values have been added automatically and the rest of the values came from the  System<\/code><\/b> event log, as shown in Figure 11.<\/p>\n
     \"1895-clip_image021.png\"<\/p>\n
    Figure 11: Inserting data into a table with an  identity column<\/b><\/p>\n
    At times, you might want to insert data into a SQL Server database  without having to first create the table. Log Parser will create the table for you. For example, suppose that we drop  the  ScmEvents<\/code><\/b> table or have never created it. We can then add  the  -createTable<\/code><\/b>  parameter to the  sql<\/b> output format to specify that  the table be created automatically, as shown in the following example:<\/p>\n
    logparser -i:evt -resolveSIDs:on -o:sql -server:localhost\\sqlsrv2012  -database:AdventureWorks2012 -driver:\"sql server\" -clearTable:on -createTable:on \"select extract_token(EventTypeName, 0,  ' ') as TypeEvent, to_date(TimeGenerated) as DateGenerated, SID as SecurityID into ScmEvents from system where  SourceName = 'Service Control Manager' and SID is not null\"<\/pre>\n
    When you run the command, Log Parser will create the table in the target  database if the table does not already exist. However, if you were to run this command without including the  -createTable<\/code><\/b> parameter and the table did not exist, Log  Parser would return an error.<\/p>\n
    One other note about Log Parser. When your SQL query becomes too  unwieldy, you can put the SQL in a separate file and call that file from your Log Parser command. For example, suppose  we save the query in the previous example to the file  C:\\DataFiles\\EvtQuery.sql<\/code><\/b>, as shown in Figure 12.<\/p>\n
    \"1895-clip_image023-630x298.jpg\"<\/p>\n
    Figure 12: Creating a text file that contains  the SQL query<\/b><\/p>\n
    When creating the file for the query, you’ll likely want to use a text  editor such as Notepad because SSMS will show syntax errors in the code.<\/p>\n
    Once we’ve created the file for our query, we can modify our command as  follows:<\/p>\n
    logparser -i:evt -resolveSIDs:on -o:sql -server:localhost\\sqlsrv2012  -database:AdventureWorks2012 -driver:\"sql server\" -clearTable:on -createTable:on file:c:\\datafiles\\evtquery.sql<\/pre>\n
    As you can see, we’ve merely replaced the query with the  file<\/code><\/b>  option, pointing to our new .sql file. Otherwise, everything else about our command is the same.<\/p>\n
    Working with Log Parser<\/h1>\n
    The examples I’ve shown you in this article have all retrieved data from  the System<\/code><\/b>  event log, but you’re certainly not limited to that log. You can retrieve data from other event logs, multiple logs, and  a variety of other sources, such as Active Directory, the registry, IIS logs, text files, or information about the file  directory itself. Log Parser is a flexible and powerful tool that can be useful in a variety of circumstances. And  because of the utility’s “SQL-like” logic, most of the data you can retrieve through Log Parser can be saved to a SQL  Server database. If you can write a T-SQL SELECT<\/code><\/b>  statement, you can use Log Parser to store all sorts of information in your SQL Server databases.<\/p>\n<\/p><\/div>\n","protected":false},"excerpt":{"rendered":"
    For loading text, CSV or XML files into SQL Server, the Log Parser utility, with its amazing SQL engine,  is likely to be the obvious choice. Although initially developed purely for converting IIS logs, the Log Parser can turn its hand to a range of formats including even event logs or the Windows registry.…<\/p>\n","protected":false},"author":221841,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143532],"tags":[4824,4150,4151,4213,4217],"coauthors":[],"class_list":["post-1721","post","type-post","status-publish","format-standard","hentry","category-tools-sql-server","tag-etl","tag-sql","tag-sql-server","tag-sql-tools","tag-xml"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1721","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221841"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=1721"}],"version-history":[{"count":6,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1721\/revisions"}],"predecessor-version":[{"id":90955,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1721\/revisions\/90955"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1721"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1721"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1721"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1721"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}