Editing: MAP: How to Enter Multiple Log File Paths for Log Parsing

Title
<html> <body> IMPORTANT: Keep checking the <a href="http://blogs.technet.com/b/mapblog/">MAP Blog</a> and <a href="http://social.technet.microsoft.com/wiki/contents/articles/1640.microsoft-assessment-and-planning-toolkit-en-us.aspx"> MAP Toolkit Survival Guide(en-US)</a> for updates and changes , especially after new versions of MAP are released. <ol> <li>Consolidate all the directories and subdirectories containing your server log files into a single folder (like D:\logs) <a href="http://social.technet.microsoft.com/wiki/cfs-file.ashx/__key/communityserver-wikis-components-files/00-00-00-00-05/2843.Folders.JPG"><img alt="" src="http://social.technet.microsoft.com/wiki/resized-image.ashx/__size/550x0/__key/communityserver-wikis-components-files/00-00-00-00-05/2843.Folders.JPG" style="border:0px solid"></a> </li><li>Create an Excel file as a directory builder with the following values/formulas in the cells: <table border="3" rules="all" width="550" style="border:thin solid black; font-family:'Gill Sans','Gill Sans MT',Calibri,'Trebuchet MS',sans-serif; font-size:9pt"> <tbody> <tr valign="top" style="background-color:silver"> <td style="width:10px"></td> <td align="center" style="width:75px">A</td> <td align="center" style="width:455px">B</td> </tr> <tr> <td valign="top" align="center" style="background-color:silver">1</td> <td style="height:21px">DBName</td> <td style="height:21px">=CONCATENATE("INSERT INTO [",A1,"].[dbo].[lp_usage_log_sources]")</td> </tr> <tr> <td align="center" style="background-color:silver">2</td> <td></td> <td>([log_path])</td> </tr> <tr> <td align="center" style="background-color:silver">3</td> <td></td> <td>VALUES</td> </tr> <tr valign="top"> <td align="center" style="background-color:silver">4</td> <td>START HERE</td> <td>=IF(LEFT(A4,2)="\\",CONCATENATE("('",A4,"')",IF(LEFT(A5,2)="\\",",","")), IF(MID(A4,2,1)=":",CONCATENATE("('",A4,"')",IF(MID(A5,2,1)=":",",","")),("GO")))</td> </tr> <tr> <td align="center" style="background-color:silver">5</td> <td></td> <td>copy the formula above from B5 down through B9</td> </tr> <tr> <td align="center" style="background-color:silver">6</td> <td></td> <td>...</td> </tr> <tr> <td align="center" style="background-color:silver">7</td> <td></td> <td>...</td> </tr> <tr> <td align="center" style="background-color:silver">8</td> <td></td> <td>...</td> </tr> <tr> <td align="center" style="background-color:silver">9</td> <td></td> <td>...</td> </tr> </tbody> </table> </li><li>The Excel document should look similar to this (coloring and spacing shown in image is optional): <a href="http://social.technet.microsoft.com/wiki/cfs-file.ashx/__key/communityserver-wikis-components-files/00-00-00-00-05/7331.XLS_5F00_DBname.JPG"><img alt="" src="http://social.technet.microsoft.com/wiki/resized-image.ashx/__size/550x0/__key/communityserver-wikis-components-files/00-00-00-00-05/7331.XLS_5F00_DBname.JPG" style="border:0px solid"></a> </li><li>Create a text file with the directory names by typing this from a command prompt: <code>dir /s /ad /b FilePath > C:\LogDirs.txt</code> <a href="http://social.technet.microsoft.com/wiki/cfs-file.ashx/__key/communityserver-wikis-components-files/00-00-00-00-05/3644.Command.JPG"><img alt="" src="http://social.technet.microsoft.com/wiki/resized-image.ashx/__size/550x0/__key/communityserver-wikis-components-files/00-00-00-00-05/3644.Command.JPG" style="border:0px solid"></a> </li><li>Open the text file and copy all the file paths <a href="http://social.technet.microsoft.com/wiki/cfs-file.ashx/__key/communityserver-wikis-components-files/00-00-00-00-05/7183.TextFile.JPG"><img alt="" src="http://social.technet.microsoft.com/wiki/resized-image.ashx/__size/550x0/__key/communityserver-wikis-components-files/00-00-00-00-05/7183.TextFile.JPG" style="border:0px solid"></a> </li><li>Open the directory builder spreadsheet and enter your database name in the yellow box cell, overwriting "DBName" and paste your file paths, starting in the cell labeled "START HERE" in the image. <a href="http://social.technet.microsoft.com/wiki/cfs-file.ashx/__key/communityserver-wikis-components-files/00-00-00-00-05/7331.XLS_5F00_DBname.JPG"><img alt="" src="http://social.technet.microsoft.com/wiki/resized-image.ashx/__size/550x0/__key/communityserver-wikis-components-files/00-00-00-00-05/7331.XLS_5F00_DBname.JPG" style="border:0px solid"></a> </li><li>Make sure you copy/paste the formula in B4 into every cell of column B that has a directory value in column A plus one more. See the image in step 8 for an example. </li><li>Copy the cells starting in column B with the INSERT statement, up to and including the first GO at the end of your paths list. <a href="http://social.technet.microsoft.com/wiki/cfs-file.ashx/__key/communityserver-wikis-components-files/00-00-00-00-05/4718.XLS_5F00_FilePath.JPG"><img alt="" src="http://social.technet.microsoft.com/wiki/resized-image.ashx/__size/550x0/__key/communityserver-wikis-components-files/00-00-00-00-05/4718.XLS_5F00_FilePath.JPG" style="border:0px solid"></a> </li><li>Open SQL Server Management Studio and expand the Databases node and find your database. Right click on the database and choose New Query. Paste the copied cells into the query editor window <a href="http://social.technet.microsoft.com/wiki/cfs-file.ashx/__key/communityserver-wikis-components-files/00-00-00-00-05/5432.Query.JPG"><img alt="" src="http://social.technet.microsoft.com/wiki/resized-image.ashx/__size/550x0/__key/communityserver-wikis-components-files/00-00-00-00-05/5432.Query.JPG" style="border:0px solid"></a> </li><li>Execute the query </li><li>Open MAP, select the Usage Tracking scenario group, click the tile corresponding to the scenario you would like to run, then "Configure Log Parser" and confirm it contains the data you expect. <a href="http://social.technet.microsoft.com/wiki/cfs-file.ashx/__key/communityserver-wikis-components-files/00-00-00-00-05/2262.Logpaths.PNG"><img alt="" src="http://social.technet.microsoft.com/wiki/resized-image.ashx/__size/550x0/__key/communityserver-wikis-components-files/00-00-00-00-05/2262.Logpaths.PNG" style="width:350px; height:256px; border-width:0px; border-style:solid"></a> </li></ol> </body> </html>
Comment
Tags
Please add 4 and 3 and type the answer here:

Products

Resources

Solutions

Updates

Trials

Related Sites

Training

Certifications

Other resources

Support options

More support

Not an IT pro?