The Computing Conference 2018 Workshop: Log Based Security Analysis

  1. Host brute-force attack and abnormal logon identification
  2. Database SQL attack and export identification
  3. Web service CC attack behavior analysis

Log Service Initial Preparation

Scenario 1: Host Brute-Force Attack and Abnormal Logon Identification

View logon logs

__topic__ : winlogin
__topic__:  winlogin         // log topic: the logon log is winlogin
client_ip: 197.210.226.56 // the IP address of the logon client
result: success // logon result: success/fail
target: host4.test.com // the host being logged on to
target_type: server // machine type: server/normal
type: ssh // logon method:ssh/rdp
user: admin // logon account

Brute-force attack identification

__topic__: winlogin and target_type: server  | select date_format(max_by(__time__, __time__), '%m-%d %H:%i:%s') as "×î½üʱ¼ä",  target as "·þÎñÆ÷", 4 as "Æƽâ´ÎÊý", count(1) as "ʼþ´ÎÊý" FROM  (select __time__, target, result, lag(result, 1, 'δ֪') over ( PARTITION  by target order by __time__) as pre1, lag(result, 2, 'δ֪') over ( PARTITION  by target order by __time__) as pre2 , lag(result, 3, 'δ֪') over ( PARTITION  by target order by __time__) as pre3 , lag(result, 4, 'δ֪') over ( PARTITION  by target order by __time__) as pre4 from log) where result='success' and pre1='fail' and pre1='fail' and pre2='fail' and pre3='fail' and pre4='fail' group by target
__topic__: winlogin and target_type: server
__topic__: winlogin and target_type: server  | select __time__, target, result, lag(result, 1, 'δ֪') over ( PARTITION  by target order by __time__) as pre1, lag(result, 2, 'δ֪') over ( PARTITION  by target order by __time__) as pre2 , lag(result, 3, 'δ֪') over ( PARTITION  by target order by __time__) as pre3 , lag(result, 4, 'δ֪') over ( PARTITION  by target order by __time__) as pre4 from log
__topic__: winlogin and target_type: server  | select date_format(max_by(__time__, __time__), '%m-%d %H:%i:%s') as "×î½üʱ¼ä",  target as "·þÎñÆ÷", 4 as "Æƽâ´ÎÊý", count(1) as "ʼþ´ÎÊý" FROM  (select __time__, target, result, lag(result, 1, 'δ֪') over ( PARTITION  by target order by __time__) as pre1, lag(result, 2, 'δ֪') over ( PARTITION  by target order by __time__) as pre2 , lag(result, 3, 'δ֪') over ( PARTITION  by target order by __time__) as pre3 , lag(result, 4, 'δ֪') over ( PARTITION  by target order by __time__) as pre4 from log) where result='success' and pre1='fail' and pre1='fail' and pre2='fail' and pre3='fail' and pre4='fail' group by target

Identify abnormal logons and configure IP drill down

__topic__: winlogin and result: success and target_type: server | select date_format(min_by(__time__, __time__), '%m-%d %H:%i:%s') as "×îÔçʱ¼ä", date_format(max_by(__time__, __time__), '%m-%d %H:%i:%s') as "×î½üʱ¼ä", target as "·þÎñÆ÷", count(1) as "µÇ¼´ÎÊý", arbitrary(client_ip) as "¿ÉÒÉ¿Í»§¶Ë£¨ÑùÀý£©" , '²é¿´IPÐÅÏ¢' as "²Ù×÷" where ip_to_country(client_ip) <> 'Öйú' and security_check_ip(client_ip) = 1 group by target order by "µÇ¼´ÎÊý" DESC

Refine logon security dashboard

__topic__: winlogin and result: success | select ip_to_country(client_ip) as country, count(1) as "³É¹¦µÇ¼´ÎÊý" group by  country

Scenario 2: Database SQL Attack and Export Identification

View logon logs

__topic__ : mysql
__topic__:  mysql      // log topic: the SQL execution log is for MySQL
sql: SELECT * FROM accounts WHERE id >= 20000
and id < 30000 limit 10000 // executed SQL statement
target: db1.abc.com // database server
db_name: crm_system // database
table_name: accounts // table
sql_type: select // SQL statement type: select, update, delete, etc.
user: op_user1 // SQL statement executor
client_ip: 1.2.3.4 // IP address of the client for the execution
affected_rows: 10000 // affected function, for example, the returned rows
response_time: 1210 // response time of the execution (ms)

Identify SQL attacks

__topic__: mysql | select date_format(__time__, '%m-%d %H:%i:%s') as "ʱ¼ä", 'ÎļþдÈë' as "¹¥»÷ÀàÐÍ", client_ip as "¿Í»§¶Ë", concat(db_name, table_name) as "Êý¾Ý¿â", target as "·þÎñÆ÷", sql as "¹¥»÷SQL"  where regexp_like(sql, '(?i).+into\s+dumpfile\b.+')

Identify database export

__topic__: mysql and sql_type: select | SELECT date_format(min_by(__time__, __time__), '%m-%d %H:%i:%s') as "ÍϿ⿪ʼʱ¼ä", max_by(__time__, __time__)-min_by(__time__, __time__) as "ÍÏ¿âºÄʱ(Ãë)", db_name as "Êý¾Ý¿â", table_name as "±í¸ñ",  sum(affected_rows) as "ÍÏ¿âÐÐÊý", arbitrary(sql) as "ÍÏ¿âSQL(ÑùÀý)",  arbitrary(client_ip) as "¿Í»§¶Ë(ÑùÀý)" group by db_name, table_name HAVING  "ÍÏ¿âÐÐÊý" > 200

Build database security dashboard

Scenario 3: Web Service CC Attack Behavior Analysis

View logon logs

__topic__ : ddos_access_log

Identify CC attack rules

View DDoS security dashboard

--

--

--

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Be a Master of Simplicity

The Easy and Best Way To Learn Programming

What happens when you type https://www.holbertonschool.com in your browser and press Enter?

Difference Between List, Sets and Dictionary.

What to Expect From Your Future Software Engineering Degree

Terraform basics: Variables

FITEVO the first multiplayer #move2earn game (chapter 2)

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Alibaba Cloud

Alibaba Cloud

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com

More from Medium

Migrating to GCVE with VMware HCX — Part 3

Why Multi-Cloud Probably Isn’t the Answer for Resilience

Diagram of two cloud providers being used simultaneously

GCP Command Line - gcloud

Enabling Multi Dimensional pod auto scaling in Autopilot GKE is really great