Unique Life

그림1

그림1은 unix time을 나타낸다

그림2는 Epoch 시간을 나타내며

unix_timestamp() 함수를 써주면 변환이 된다.

Quick summary

The Mini-Course

Let's first create a simple logging-table and some sample records.
All queries on this page will work with the following table.

copytextpop-up

-- Table "mytable" DDL
 
CREATE TABLE `mytable` (
  `userId` int(11) NOT NULL,
  `url` varchar(100) NOT NULL,
  `epoch` int(11) NOT NULL
);
 
INSERT INTO mytable VALUES(1,'homepage',1225540800); 
# User 1 visited the url homepage on Nov 1, 2008
INSERT INTO mytable VALUES(2,'contact',1225886400); 
# User 2 visited the url contact on Nov 5, 2008
INSERT INTO mytable VALUES(3,'contact',1225972800); 
# User 3 visited the url contact on Nov 6, 2008
INSERT INTO mytable VALUES(4,'homepage',1228132800); 
# User 4 visited the url homepage on Dec 1, 2008

-- Table "mytable" DDL

CREATE TABLE `mytable` (
  `userId` int(11) NOT NULL,
  `url` varchar(100) NOT NULL,
  `epoch` int(11) NOT NULL
);

INSERT INTO mytable VALUES(1,'homepage',1225540800); 
# User 1 visited the url homepage on Nov 1, 2008
INSERT INTO mytable VALUES(2,'contact',1225886400); 
# User 2 visited the url contact on Nov 5, 2008
INSERT INTO mytable VALUES(3,'contact',1225972800); 
# User 3 visited the url contact on Nov 6, 2008
INSERT INTO mytable VALUES(4,'homepage',1228132800); 
# User 4 visited the url homepage on Dec 1, 2008

Converting to readable dates

copytextpop-up

SELECT userId, url, FROM_UNIXTIME(epoch) FROM mytable

SELECT userId, url, FROM_UNIXTIME(epoch) FROM mytable

This query outputs:

1   homepage   2008-11-01 13:00:00
2   contact   2008-11-05 13:00:00
3   contact   2008-11-06 13:00:00
4   homepage   2008-12-01 13:00:00

You can format your date by using specifiers (look below for a list of specifiers).

copytextpop-up

SELECT userId, url, FROM_UNIXTIME(epoch,"%Y-%m-%d") FROM mytable

SELECT userId, url, FROM_UNIXTIME(epoch,"%Y-%m-%d") FROM mytable

Output:

1   homepage   2008-11-01
2   contact   2008-11-05
3   contact   2008-11-06
4   homepage   2008-12-01

Grouping Epochs

Let's say you want to get statistics by month. This query groups months, and counts the number of visitor (records) in each month. We order by epoch to get all results in the right order.

copytextpop-up

SELECT DISTINCT FROM_UNIXTIME(epoch,"%M, %Y") AS month, count(*) as numberOfVisits
FROM mytable
GROUP BY month
ORDER BY epoch

SELECT DISTINCT FROM_UNIXTIME(epoch,"%M, %Y") AS month, count(*) as numberOfVisits
FROM mytable
GROUP BY month
ORDER BY epoch

This outputs:

November, 2008   3
December, 2008   1

This query can be easily changed to get statistics per year, per day, per day of the week, per hour of the day etc. For example, to get yearly stats change the query to:

copytextpop-up

SELECT DISTINCT FROM_UNIXTIME(epoch,"%Y") AS year, count(*) as numberOfVisits
FROM mytable
GROUP BY year
ORDER BY epoch

SELECT DISTINCT FROM_UNIXTIME(epoch,"%Y") AS year, count(*) as numberOfVisits
FROM mytable
GROUP BY year
ORDER BY epoch

Adding a new record to our database

Use the UNIX_TIMESTAMP() function to convert MySQL dates/times (such as now() = current time) to epochs.

copytextpop-up

INSERT INTO mytable VALUES(1,'pagename',UNIX_TIMESTAMP(now()))

INSERT INTO mytable VALUES(1,'pagename',UNIX_TIMESTAMP(now()))

or use YYYY-MM-DD HH:MM:SS :

copytextpop-up

INSERT INTO mytable VALUES(1,'pagename',UNIX_TIMESTAMP('2008-12-01 12:00:00'))

INSERT INTO mytable VALUES(1,'pagename',UNIX_TIMESTAMP('2008-12-01 12:00:00'))

Negative Epochs

There's one big problem with MySQL: MySQL cannot convert negative epoch timestamps (dates before 1-1-1970). This creates problems with for example birthdates. But there are workarounds.

When converting from epoch to human readable date use the DATE_ADD function:

copytextpop-up

# converting to MySQL date:
SELECT DATE_ADD(FROM_UNIXTIME(0), interval -315619200 second);
# converting your epoch to a date string:
SELECT DATE_FORMAT(DATE_ADD(FROM_UNIXTIME(0), interval -315619200 second),'%Y-%m-%d');

# converting to MySQL date:
SELECT DATE_ADD(FROM_UNIXTIME(0), interval -315619200 second);
# converting your epoch to a date string:
SELECT DATE_FORMAT(DATE_ADD(FROM_UNIXTIME(0), interval -315619200 second),'%Y-%m-%d');

Where -315619200 is your negative epoch. This query returns: 1960-01-01 01:00:00

When converting normal dates to epoch use TIMESTAMPDIFF:

copytextpop-up

SELECT TIMESTAMPDIFF(second,FROM_UNIXTIME(0),'1960-01-01 00:00:00' );

SELECT TIMESTAMPDIFF(second,FROM_UNIXTIME(0),'1960-01-01 00:00:00' );

Replace the 1960 date with your date in your local timezone (MySQL time_zone).

MySQL date format specifiers

^(?<time>[^ ]* [^ ]* [^ ]*) (?<host>[^ ]*) \[(?<toolName>[^ ]*)\] \[Attack_Name=]*(?<atackName>[^\]]*)\]\, \[Time=]*(?<time2>[^\]]*)\]\, \[Hacker=]*(?<hackerIP>[^\]]*)\]\, \[Victim=]*(?<victimIP>[^\]]*)\]\, \[Protocol=]*(?<protocol>[^\/]*)\/(?<port>[^\]]*)\]\, \[Risk=]*(?<priority>[^\]]*)\]\, \[Handling=]*(?<handling>[^\]]*)\]\, \[Information=]*(?<information>[^\]]*)\]\, \[SrcPort=]*(?<srcPort>[^\]]*)\]$

^(?<time>[^ ]* [^ ]* [^ ]*) (?<host>[^ ]*) \[(?<toolName>[^ ]*)\] \[Attack_Name=]*(?<attackName>[^\]]*)\] \[Time=]*(?<time2>[^\]]*)\] \[Src_ip=]*(?<srcIP>[^\]]*)\] \[Dst_ip=]*(?<dstIP>[^\]]*)\] \[Protocol=]*(?<protocol>[^\/]*)\/*(?<port>[^\]]*)\] \[Filter=]*(?<filter>[^\]]*)\] \[Action=]*(?<action>[^\]]*)\] \[Src_port=]*(?<srcPort>[^\]]*)\]

^(?<time>[^ ]* [^ ]* [^ ]*) (?<host>[^ ]*) \[(?<toolName>[^ ]*)\] \[Attack_Name=\(\d*\)]*(?<attackName>[^\]]*)\]\, \[Time=]*(?<year>\d{4})\/(?<month>\d{1,2})\/(?<day>\d{1,2})(?<time2>[^\]]*)\]\, \[Hacker=]*(?<hackerIP>[^\]]*)\]\, \[Victim=]*(?<victimIP>[^\]]*)\]\, \[Protocol=]*(?<protocol>[^\/]*)\/(?<port>[^\]]*)\]\, \[Risk=]*(?<priority>[^\]]*)\]\, \[Handling=]*(?<handling>[^\]]*)\]\, \[Information=]*(?<information>[^\]]*)\]\, \[SrcPort=]*(?<srcPort>[^\]]*)\]$

^(?<time>[^ ]* [^ ]* [^ ]*) (?<host>[^ ]*) \[(?<toolName>[^ ]*)\] \[Attack_Name=]*(?<attackName>[^\]]*)\] \[Time=]*(?<year>\d{4})\/(?<month>\d{1,2})\/(?<day>\d{1,2})(?<time2>[^\]]*)\] \[Src_ip=]*(?<srcIP>[^\]]*)\] \[Dst_ip=]*(?<dstIP>[^\]]*)\] \[Protocol=]*(?<protocol>[^\/]*)\/*(?<port>[^\]]*)\] \[Filter=]*(?<filter>[^\]]*)\] \[Action=]*(?<action>[^\]]*)\] \[Src_port=]*(?<srcPort>[^\]]*)\]

^(?<time>[^ ]*)  \[\*\*] [^ ]* (?<messages>[^\[]*)\[\*\*] \[Classification: (?<classification>[^\]]*)\] \[Priority: (?<priority>[^\]]*)] {(?<protocol>[^ ]*)} (?<srcIP>[^:]*):(?<srcPort>[^ ]*) -> (?<dstIP>[^:]*):(?<dstPort>[^ ]*)$

- 수리카타 fast.log 정규식

[Sun Nov 15 08:34:33 2015] [error] [client 111.85.191.131] request failed: error reading the headers

^\[[^ ]* (?<time>[^\]]*)\] \[(?<level>[^\]]*)\] \[client (?<client_ip>[^\]]*)\] (?<message>.*)$

-apache2 error 정규식

출처: http://moonlighting.tistory.com/140

MySQL 테이블에 데이터를 넣는 방법 중 csv 파일을 직접 넣는 방법이 있다.

csv 파일과 DB 테이블 구조가 일치하면 아래와 같이 입력하면 된다.

만약 파일의 크기가 크고 테이블에 index가 걸려 있으면 시간이 굉장히 오래 걸릴 수 있다.

그럴 경우에는 아래와 같이 index를 먼저 해제해 주고 파일을 올린 다음 다시 index를 걸면 상당히 빠르게 테이블에 데이터를 넣을 수 있다.

대략 2GB 정도 되는 파일도 10분 정도로 import가 완료됐다.

만약 csv 파일 구조와 DB 테이블 구조가 다른 경우는 아래 샘플 sql 문 참조해서 import 한다.

http://stackoverflow.com/questions/10762239/mysql-enable-load-data-local-infile

위 오류 해결 방안
 

You should set the option:

local-infile=1

into your [mysql] entry of my.cnf file or call mysql client with the --local-infile option:

mysql --local-infile -uroot -pyourpwd yourdbname

You have to be sure that the same parameter is defined into your [mysqld] section too to enable the "local infile" feature server side.

It's a security restriction.

이메일 파싱

parsemail.py

http://blog.magiksys.net/parsing-email-using-python-content