Using Excel Pivot tables to analyze IIS logs

Here is a relatively simple and very practical method, through some simple formula of Excel to make a series of data, such as time interval, crawling page, return status code, URL parameters, spider type, spider IP, etc., through the above data can be the problem of the site to troubleshoot, correct.

You must first have your own server or be able to view the IIS log permissions to download IIS log files from space to the local server via FTP: Open IIS, click the Web site to query > right > Properties > web Site tab > properties > See If it is space, some space services will put log files on the site root directory. If not, you can request it from the service provider.

　　

Then, depending on the path to find the appropriate folder, you can see that there are many. log files in the folder that are log files and then download them locally via FTP.

　　

If the file is too large to operate may not be the aspect, you can use UltraEdit to open, screen the data you want to get (specific download a study). The file is not very large you can use Notepad to open directly after you copy to Excel.

　　

Then delete the first 4 lines, select Column A, click Excel data > Columns > Split symbol

　　

　　

1 2 Next page

Next > Other > Enter spaces > Next > Finish. So the first step is done.

　　

Then select A1 Column > Right > Insert and then delete the C,d,e,i column. In the first line, enter: Date, time, webpage, parameter, port, IP, spider, Status code in addition to the parameters, parameters this is a Dynamic Web page question mark (?) The following section. http://www.***.org/jiaju/chufang/5309_3. HTML this path after the parameter value is 3, then the real URL after the combination is http://www.***.org/jiaju/chufang/5309_3. HTML? 3 so that the spider can still distinguish the parameters, some Web site ads are often taken on the parameters of the statistics, but after crawling parameters will be removed. So try not to use this kind of URL on the content page.

　　

Check g column > Data > Filter > Click the G-Column arrow > Text filter > Include

　　

Enter Baidupider Click OK. Can see all the Baidu Spider Access data, so that the approximate data has been presented, if you see Google in the filter when the input Googlebot. A simple pivot table is then made to facilitate analysis. One order: Insert > Pivot table > Pivot Table > Make sure to tick the page, spider, and time in the right order. Then click on the small triangle behind the spider,

　　

Click label Filter > Include > Enter Baidupider. You can filter the spider crawl time details of the Web page you want to see, of course, you can filter the data according to other combinations, it is not demonstrated.

　　

Using Excel Pivot tables to analyze IIS logs