마카로닉스

269 No

스프링 웹 로그 분석 - 웹 접속자 정보 위치 정보 DB에 삽입 및 DB쿼리 중복 IP 제거

2017-09-03 18:46:12 By 머나먼나라 0 1368 댓글수 4 포스트 하기

11월 2024
일	월	화	수	목	금	토
					1	2
3	4	5	6	7	8	9
10	11	12	13	14	15	16
17	18	19	20	21	22	23
24	25	26	27	28	29	30

다음과 같은 사이트를 이용해서 만들었다.

//http://www.mkyong.com/java/java-find-location-using-ip-address/

//https://github.com/maxmind/geoip-api-java

//https://dev.maxmind.com/geoip/legacy/geolite/

pom.xml 에 라이브러리를 등록 한다

<!-- 위치 정보 -->
<dependency>
        <groupId>com.maxmind.geoip</groupId>
        <artifactId>geoip-api</artifactId>
        <version>1.3.1</version>
    </dependency>

예전에 나온 버전이지만 무료이다. 2는 유료라서 라이센스가 필요하다

우선 접속자 ip 가져오기

Contact.class

@Data
public class Contact {

	private Integer idx;
	private String ip;
	private String agent;
	private String brower;
	private String os;

}

WebLog.class

package com.macaronics.www.util.weblog;

import javax.inject.Inject;
import javax.servlet.http.HttpServletRequest;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;

@Component
public class WebLog {

	private static final Logger logger = LoggerFactory.getLogger(WebLog.class);
	@Inject
	private GetLocation getLocation;
	
	public ServerLocation Log(HttpServletRequest request, ServerLocation serverLocation){
			
		// 접속자 IP
		String ip = request.getHeader("X-FORWARDED-FOR");
		if (ip == null || ip.length() == 0) {
		   ip = request.getHeader("Proxy-Client-IP");
		}
		if (ip == null || ip.length() == 0) {
		   ip = request.getHeader("WL-Proxy-Client-IP");  // 웹로직
		}
		if (ip == null || ip.length() == 0) {
		   ip = request.getRemoteAddr() ;
		}
		
		
		
		// 에이전트
		String agent = request.getHeader("User-Agent");
		
		// 브라우져 구분
		String brower = null;
		 
		if (agent != null) {
		   if (agent.indexOf("Trident") > -1) {
		      brower = "MSIE";
		   } else if (agent.indexOf("Chrome") > -1) {
		      brower = "Chrome";
		   } else if (agent.indexOf("Opera") > -1) {
		      brower = "Opera";
		   } else if (agent.indexOf("iPhone") > -1 && agent.indexOf("Mobile") > -1) {
		      brower = "iPhone";
		   } else if (agent.indexOf("Android") > -1 && agent.indexOf("Mobile") > -1) {
		      brower = "Android";
		   }
		}
		
		// OS 구분
		String os = null;
		 
		if(agent.indexOf("NT 6.0") != -1) os = "Windows Vista/Server 2008";
		else if(agent.indexOf("NT 5.2") != -1) os = "Windows Server 2003";
		else if(agent.indexOf("NT 5.1") != -1) os = "Windows XP";
		else if(agent.indexOf("NT 5.0") != -1) os = "Windows 2000";
		else if(agent.indexOf("NT") != -1) os = "Windows NT";
		else if(agent.indexOf("9x 4.90") != -1) os = "Windows Me";
		else if(agent.indexOf("98") != -1) os = "Windows 98";
		else if(agent.indexOf("95") != -1) os = "Windows 95";
		else if(agent.indexOf("Win16") != -1) os = "Windows 3.x";
		else if(agent.indexOf("Windows") != -1) os = "Windows";
		else if(agent.indexOf("Linux") != -1) os = "Linux";
		else if(agent.indexOf("Macintosh") != -1) os = "Macintosh";
		else os = ""; 
		
		serverLocation.setIp(ip);
		serverLocation.setAgent(agent);
		serverLocation.setBrower(brower);
		serverLocation.setOs(os);
		
		getLocation.getLocation(ip, request, serverLocation);	
		
			
		return serverLocation;
	}
	
	
}

ip 데이터를 geoip-api 라리브러리를 이용해서 접속자 위치 정보를 가져온다.

우선 위에 링크된 주소에서 GeoLiteCity.dat 를 다운 받아서

resources 에서 파일을 생성한다.

ServerLocation.class

package com.macaronics.www.util.weblog;

import java.util.Date;

//http://www.mkyong.com/java/java-find-location-using-ip-address/

//https://github.com/maxmind/geoip-api-java

//https://dev.maxmind.com/geoip/legacy/geolite/

@Data
public class ServerLocation extends Contact {

	private String countryCode;
	private String countryName;
	private String region;
	private String regionName;
	private String city;
	private String postalCode;
	private String latitude;
	private String longitude;
	private Date regdate;
	
	private Integer rn;
	private Integer ipcount;
	

ServerLocation [countryCode=US, countryName=United States, region=CA,
		regionName=California, city=Sunnyvale, postalCode=94089,
		latitude=37.424896, longitude=-122.0074]*/

	
	
}

GetLocation.class

package com.macaronics.www.util.weblog;

import java.io.File;
import java.io.IOException;

import javax.servlet.http.HttpServletRequest;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;

import com.macaronics.www.util.fileupload.UploadPath;
import com.maxmind.geoip.Location;
import com.maxmind.geoip.LookupService;
import com.maxmind.geoip.regionName;


@Component
public class GetLocation {

	private static final Logger logger = LoggerFactory.getLogger(GetLocation.class);

 private static final String FILE_PATH="resources/location";	
	
 
  public ServerLocation getLocation(String ipAddress, HttpServletRequest request, ServerLocation serverLocation) {
	
	UploadPath.attach_path=FILE_PATH;
	String fileName=UploadPath.path(request)+File.separatorChar+ "GeoLiteCity.dat";
	File file = new File(fileName);
	
	return getLocation(ipAddress, file, serverLocation);

  }

  public ServerLocation getLocation(String ipAddress, File file, ServerLocation serverLocation ) {
	  
	try {
		
		
		LookupService lookup = new LookupService(file,LookupService.GEOIP_MEMORY_CACHE);
		Location locationServices = lookup.getLocation(ipAddress);
		//Location locationServices = lookup.getLocation("114.108.157.50");
		
		if(locationServices!=null){
			
			serverLocation.setCountryCode(""+locationServices.countryCode);
			serverLocation.setCountryName(""+locationServices.countryName);
			serverLocation.setRegion(""+locationServices.region);
			serverLocation.setRegionName(""+regionName.regionNameByCode(
		             locationServices.countryCode, locationServices.region));
			serverLocation.setCity(""+locationServices.city);
			serverLocation.setPostalCode(""+locationServices.postalCode);
			serverLocation.setLatitude(""+String.valueOf(locationServices.latitude));
			serverLocation.setLongitude(""+String.valueOf(locationServices.longitude));
			
			logger.info(" 위치 정보 출력 성공");
		}

	} catch (IOException e) {
		System.err.println(e.getMessage());
	}

	return serverLocation;

  }
  
  
  
  
  
}

데이터는 다음과 같은 형식으로 반환 처리 된다.

ServerLocation [countryCode=US, countryName=United States, region=CA,
regionName=California, city=Sunnyvale, postalCode=94089,
latitude=37.424896, longitude=-122.0074]*/

서버에 올리기전 테스트에서는 ip 값이 없기 때문에 접속 위치 정보를 찾지 못해 에러가 발생한다. 따라서

임의 주소로 나는 dauml.net (114.108.157.50) 의 ip 를 이용해서 테스트를 진행 하였다.

AdminWebLogController.class

package com.macaronics.www.admin.controller;

import java.text.DecimalFormat;
import java.util.Calendar;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.inject.Inject;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;

import com.macaronics.www.admin.service.AdminWebLogService;
import com.macaronics.www.user.model.dto.GalleryDTO;
import com.macaronics.www.util.oralce.PageAndSearch;
import com.macaronics.www.util.oralce.Pager;
import com.macaronics.www.util.weblog.ServerLocation;

@Controller
@RequestMapping("/admin/weblog")
public class AdminWebLogController {

	private static Logger logger =LoggerFactory.getLogger(AdminWebLogController.class);
	
	@Inject
	private AdminWebLogService adminWebLogService;
	
	private final String JSP_PAGE  ="/admin/weblog/";
	
	
	@RequestMapping(value="/list", method=RequestMethod.GET)
	public String webLogList(ServerLocation serverLocation, Model model
			, @RequestParam(required=false) String weblogStartDate,
			@RequestParam(required=false) String weblogEndDate,
			PageAndSearch pas
			){

		String startDate="";
		String endDate="";
		if(weblogStartDate!=null && weblogEndDate!=null){
			startDate=weblogStartDate.substring(0, 4)+weblogStartDate.substring(5,9);
			endDate=weblogEndDate.substring(0, 4)+weblogEndDate.substring(5,9);
			logger.info( " 받아온 값" + startDate + " : " + endDate);
		
		}else{
			
			Calendar calendar =Calendar.getInstance();
			int year =calendar.get(Calendar.YEAR);
			String month=new DecimalFormat("00").format(calendar.get(Calendar.MONTH)+1);
			String day=new DecimalFormat("00").format(calendar.get(Calendar.DATE));	
						
			startDate=year+month+day;//20170207
			calendar.add(Calendar.DATE, +1); //하루추가 
			endDate=year+month+new DecimalFormat("00").format(calendar.get(Calendar.DATE));//20170208
			logger.info("endDate : " + endDate);
		}
		
		//페이지 나누기 관련 처리
		if(pas.getCurPage()==null){pas.setCurPage(1);}
		int count =adminWebLogService.webLogCount(startDate, endDate);
	
		Pager.PAGE_SCALE=50;
		Pager pager =new Pager(count, pas.getCurPage());
		int start =pager.getPageBegin();
		int end =pager.getPageEnd();

		List<ServerLocation> list =adminWebLogService.webLogList(start, end, startDate, endDate);
		
		Map<String, Object> map =new HashMap<>();
		map.put("countList", count);
		map.put("list", list);
		map.put("pager", pager);

		model.addAttribute("map", map);
		model.addAttribute("pageAndSearch", pas);

		model.addAttribute("list", list);

		
		
		return JSP_PAGE+"list";
	}
	
	

	
}

검색 전 첫 화면에서 오늘 날짜를 보여주기 위한 처리 를 하였다.

CSS 토드에서 SQL 로 데이터를 Html 로 생성 하면 반응형으로 보여주는 것을 활용 해 보았다.

<style type="text/css">
.ToadExtensionTableContainer {
	padding: 0px;
	border: 6px solid #8ACAE0;
	-moz-border-radius: 8px;
	-webkit-border-radius: 8px;
	-khtml-border-radius: 8px;
	border-radius: 8px;
	overflow: auto;
}

.ToadExtensionTable {
	width: 100%;
	border: 0px;
	border-collapse: collapse;
	font-family: Arial, Tahoma, Verdana, "Times New Roman", Georgia, Serif;
	font-size: 12px;
	padding: 1px;
	border: 1px solid #fff;
}

th {
	padding: 2px 4px;
	border: 1px solid #fff;
}

td {
	padding: 2px 4px;
	border: 1px solid #fff;
}

.HeaderColumnEven {
	background: #8ACAE0;
	color: #fff;
}

.HeaderColumnOdd {
	background: #4CAECF;
	color: #fff;
}

.R0C0 {
	background: #F3FAFC;
}

.R0C1 {
	background: #CFE9F2;
}

.R1C0 {
	background: #CFE9F2;
}

.R1C1 {
	background: #B3DCEB;
}

.lft {
	text-align: left;
}

.rght {
	text-align: right;
}

.cntr {
	text-align: center;
}

.jstf {
	text-align: justify;
}

.nowrap {
	white-space: nowrap;
}
</style>

list.jsp

검색

                 <div class="input-group" style="margin-top: 10px;">
                 <form action="/admin/weblog/list" method="get" name="searchForm">
                    <input type="hidden"  id="weblogStartDate" name="weblogStartDate">      
          			<input type="hidden"  id="weblogEndDate" name="weblogEndDate">
                  <input type="button" class="btn btn-success" id="btnSearch" value="검색하기">
                  
                  </form>
                </div>


<div class="ToadExtensionTableContainer">
<table class="ToadExtensionTable">
                <tr>
<th class="HeaderColumnEven">번호</th>

<th class="HeaderColumnEven">접속횟수</th>
<th class="HeaderColumnOdd">IP</th>

<th class="HeaderColumnOdd">AGENT</th>
<th class="HeaderColumnEven">브라우저</th>
<th class="HeaderColumnEven">운영체제</th>
<th class="HeaderColumnOdd">국가코드</th>
<th class="HeaderColumnEven">국가</th>
<th class="HeaderColumnOdd">지역</th>
<th class="HeaderColumnEven">지역이름</th>
<th class="HeaderColumnOdd">도시</th>
<th class="HeaderColumnEven">우편번호</th>
<th class="HeaderColumnOdd">위도</th>
<th class="HeaderColumnOdd">경도</th>
<th class="HeaderColumnOdd">접속일</th>
</tr>
   

   			
   <c:forEach items="${list }" var="row" varStatus="status">
		<tr>
		<td class="R${status.index}C0">${row.rn }</td>
		<td>${row.ipcount }</td>
		<td class="R${status.index}C1"><span class="badge bg-purple"> 
		<a href="http://www.tcpiputils.com/browse/ip-address/${row.ip }" target="_blank" style="color:#fff;">${row.ip }</a></span></td>
		<td class="R${status.index}C0"> ${fn:substring(row.agent, 0,9)}</td>	
		<td class="R${status.index}C1"> ${row.brower }</td>
		<td class="R${status.index}C0"> ${row.os }</td>
		<td class="R${status.index}C1"> ${row.countryCode }</td>
		<td class="R${status.index}C0"> ${row.countryName }</td>
		<td class="R${status.index}C1"> ${row.region }</td>
		<td class="R${status.index}C0"> ${row.regionName }</td>
		<td class="R${status.index}C1"> ${row.city }</td>
		<td class="R${status.index}C0"> ${row.postalCode }</td>
		<td class="R${status.index}C1"> ${row.latitude }</td>
		<td class="R${status.index}C0"> ${row.longitude }</td>
		
		<td class="R${status.index}C1"> <fmt:formatDate value="${row.regdate }" pattern="yyyy-MM-dd HH시 mm분"  /></td>
		
		</tr>
	</c:forEach>

		</table>
            </div>

javascript

<!-- jQuery 2.2.3 -->
<script src="/resources/admin/plugins/jQuery/jquery-2.2.3.min.js"></script>
<!-- Bootstrap 3.3.6 -->
<script src="/resources/admin/bootstrap/js/bootstrap.min.js"></script>
<!-- Select2 -->
<script src="/resources/admin/plugins/select2/select2.full.min.js"></script>
<!-- InputMask -->
<script src="/resources/admin/plugins/input-mask/jquery.inputmask.js"></script>
<script src="/resources/admin/plugins/input-mask/jquery.inputmask.date.extensions.js"></script>
<script src="/resources/admin/plugins/input-mask/jquery.inputmask.extensions.js"></script>
<!-- date-range-picker -->
<script src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.11.2/moment.min.js"></script>
<script src="/resources/admin/plugins/daterangepicker/daterangepicker.js"></script>
<!-- bootstrap datepicker -->
<script src="/resources/admin/plugins/datepicker/bootstrap-datepicker.js"></script>
<!-- bootstrap color picker -->
<script src="/resources/admin/plugins/colorpicker/bootstrap-colorpicker.min.js"></script>
<!-- bootstrap time picker -->
<script src="/resources/admin/plugins/timepicker/bootstrap-timepicker.min.js"></script>
<!-- SlimScroll 1.3.0 -->
<script src="/resources/admin/plugins/slimScroll/jquery.slimscroll.min.js"></script>
<!-- iCheck 1.0.1 -->
<script src="/resources/admin/plugins/iCheck/icheck.min.js"></script>
<!-- FastClick -->
<script src="/resources/admin/plugins/fastclick/fastclick.js"></script>
<!-- AdminLTE App -->
<script src="/resources/admin/dist/js/app.min.js"></script>
<!-- AdminLTE for demo purposes -->
<script src="/resources/admin/dist/js/demo.js"></script>
<!-- Page script -->
<script>
  $(function () {
    //Initialize Select2 Elements
    $(".select2").select2();

    //Datemask dd/mm/yyyy
    $("#datemask").inputmask("dd/mm/yyyy", {"placeholder": "dd/mm/yyyy"});
    //Datemask2 mm/dd/yyyy
    $("#datemask2").inputmask("mm/dd/yyyy", {"placeholder": "mm/dd/yyyy"});
    //Money Euro
    $("[data-mask]").inputmask();

    //Date range picker
    $('#reservation').daterangepicker();
    //Date range picker with time picker
    $('#reservationtime').daterangepicker({timePicker: true, timePickerIncrement: 30, format: 'MM/DD/YYYY h:mm A'});
    //Date range as a button
    $('#daterange-btn').daterangepicker(
        {
          ranges: {
            'Today': [moment(), moment()],
            'Yesterday': [moment().subtract(1, 'days'), moment().subtract(1, 'days')],
            'Last 7 Days': [moment().subtract(6, 'days'), moment()],
            'Last 30 Days': [moment().subtract(29, 'days'), moment()],
            'This Month': [moment().startOf('month'), moment().endOf('month')],
            'Last Month': [moment().subtract(1, 'month').startOf('month'), moment().subtract(1, 'month').endOf('month')]
          },
          startDate: moment().subtract(29, 'days'),
          endDate: moment()
        },
        function (start, end) {
               
        	$('#daterange-btn span').html(start.format('MMMM D, YYYY') + ' - ' + end.format('MMMM D, YYYY'));
        }
    );

    //Date picker
    $('#datepicker').datepicker({
      autoclose: true
    });

    //iCheck for checkbox and radio inputs
    $('input[type="checkbox"].minimal, input[type="radio"].minimal').iCheck({
      checkboxClass: 'icheckbox_minimal-blue',
      radioClass: 'iradio_minimal-blue'
    });
    //Red color scheme for iCheck
    $('input[type="checkbox"].minimal-red, input[type="radio"].minimal-red').iCheck({
      checkboxClass: 'icheckbox_minimal-red',
      radioClass: 'iradio_minimal-red'
    });
    //Flat red color scheme for iCheck
    $('input[type="checkbox"].flat-red, input[type="radio"].flat-red').iCheck({
      checkboxClass: 'icheckbox_flat-green',
      radioClass: 'iradio_flat-green'
    });

    //Colorpicker
    $(".my-colorpicker1").colorpicker();
    //color picker with addon
    $(".my-colorpicker2").colorpicker();

    //Timepicker
    $(".timepicker").timepicker({
      showInputs: false
    });
  });
</script>






<script type="text/javascript">

$(document).ready(function(){
	
	
	dateAlterInput();
	

	$("#btnSearch").click(function(event){
		dateAlterInput();	
		
		document.searchForm.submit();
	});
	
	

	
});


function dateAlterInput(){
	
	var dateStr =$("#reservation").val().replace('/','');
	var splitDate=dateStr.split("-");
	
	var startDate=splitDate[0].split("/");
	var endDate=splitDate[1].split("/");
	
	
	$("#weblogStartDate").val(startDate[1] +""+startDate[0]);
	$("#weblogEndDate").val(endDate[2]+ ""+ endDate[0] +""+endDate[1]);
	 
}

function list(page){

	location.href='/admin/weblog/list'+page;

}

</script>

SQL 쿼리

adminWebLogMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!-- 다른 mapper와 중복되지 않도록 네임스페이스 기재 -->
<mapper namespace="ORACLE.adminWebLogMapper">
	
	
	<insert id="webLogInsert" >
	
	insert INTO TBL_WEBLOG (IDX, IP, AGENT, BROWER, OS, COUNTRYCODE, COUNTRYNAME, 
	  REGION, REGIONNAME, CITY, POSTALCODE, LATITUDE, LONGITUDE)
	  
      VALUES (seq_weblog.nextval, #{ip}, #{agent}, #{brower}, #{os}, #{countryCode}, #{countryName}, 
	#{region}, #{regionName}, #{city}, #{postalCode}, #{latitude}, #{longitude} )
	</insert>
	
	
	<select id="webLogList" resultType="com.macaronics.www.util.weblog.ServerLocation">
		
		
		select * from (
		
			select rownum as rn, B.* from 
			 (
			select rownum, 
				  (select count(*) from tbl_weblog where ip=compareIp ) ipcount , 
				  (select max(regdate) from tbl_weblog  where ip=compareIp ) regdate,
				  (select max(idx) from tbl_weblog where ip=compareIp	) idx , 
				  A.*
			 from
			(
			select 
				 distinct  ip, ip compareIp , agent, brower, os, countrycode, countryname, region, regionname, city
				, postalcode, latitude, LONGITUDE
				from TBL_WEBLOG
				where 
				regdate between to_date(#{startDate}, 'YYYYMMDD') and to_date(#{endDate}, 'YYYYMMDD') 	
				) A order by regdate desc
			) B 
		
		) where  rn BETWEEN  #{start} and #{end} 
	
	</select>
	
	
	<select id="webLogCount" resultType="int">
				select count(*) from (
		
			select rownum as rn, B.* from 
			 (
			select rownum, 
				  (select count(*) from tbl_weblog where ip=compareIp ) ipcount , 
				  (select max(regdate) from tbl_weblog  where ip=compareIp ) regdate,
				  (select max(idx) from tbl_weblog where ip=compareIp	) idx , 
				  A.*
			 from
			(
			select 
				 distinct  ip, ip compareIp , agent, brower, os, countrycode, countryname, region, regionname, city
				, postalcode, latitude, LONGITUDE
				from TBL_WEBLOG
				where 
				regdate between to_date('20170206', 'YYYYMMDD') and to_date('20170208', 'YYYYMMDD') 	
				) A order by regdate desc
			) B 
		
		) 
	</select>
	
	
</mapper>

spring