=Start=

缘由：

在处理SSO账号安全的问题时，单步核心在于对SSO认证日志的分析（深层次的还需要结合用户日常行为分析，属于安全UEBA的范畴）。这里的大体逻辑在于比较SSO登录账号与IP关联账号和设备关联账号是否相同？是否同组？如果不同就需要关注（当然同组不同人也需要关注，不过相对而言风险低一点，也有一定的业务实际需求在里面，虽然也不太合理，但总体风险可控，可以放在后面处理）。

这里主要说一下在Hive SQL中如何进行多列之间的比较，以实现上面的逻辑。

正文：

参考解答：

现在假设：SSO登录账号为账号a(部门a)、IP关联账号为账号b(部门b)、设备关联账号为账号c(部门c)。账号是否相同（和部门是否相同）直接用字符串比较就行，即 a==b 和 a==c 。但是是否同组这个就不能直接比较了，需要用到类似于字符串查找/匹配来实现，在编程语言中实现起来比较简单，这里简单记录一下在Hive SQL中如何实现，方便以后参考。

假设表里现在主要有以下几个字段：

`timestr`
,`action`
,`ip`
,`sso_user`
,`sso_user_org`
,`ip_user`
,`ip_user_org`
,`device_user`
,`device_user_org`
,`sso_user_base`
,`ip_location`
,`ip_attributes`
,...

其中 xxx_org 类的数据是 l1,l2,l3,l4,l5 这种逗号连接起来的字符串，类似于下面这种

`ip_user_org` = 'l1,l2,l3,l4,l5'

substring_index(`ip_user_org`,',',1) as ip_user_1st_org,    -- l1
substring_index(`ip_user_org`,',',2) as ip_user_2_org,    -- l1,l2
substring_index(`ip_user_org`,',',3) as ip_user_3_org,    -- l1,l2,l3
regexp_extract(`ip_user_org`, '(.+?),([^,]{1,})$', 1) as ip_user_x_org,    -- l1,l2,l3,l4
substring_index(`ip_user_org`,',',-1) as ip_user_last_org,    -- l5

补充一下之前记录的一个字符串截取方法：

Hive SQL中如何截取「变长」「特定分隔符」字符串的除最后一段内容之外的内容？
如果格式确定对话，建议使用 regexp_extract 进行提取；除此之外，还可以用 substring_index 来操作，从前往后或从后往前取固定数量分隔符的子串，实在不嫌麻烦再加一个 replace 就行。

比较两个（或多个）部门之间的关系，这里以 ip_user_org 和 sso_user_org 为例进行说明：

case
when ip_user_org = sso_user_org then 'a=b'
when ip_user_org like concat(sso_user_org,'%') then 'a_like_b'
when sso_user_org like concat(ip_user_org,'%') then 'a_like_b'
when substring_index(`ip_user_org`,',',3) = substring_index(`sso_user_org`,',',3) then 'a-b_common-3org'
else 'unknown'
end as org_relation,

# 上面这部分的核心在于将 column1/column2 的内容先转成字符串，然后用 concat 函数给添加一个 % 以便进行like模糊匹配。
# 如果要用 rlike 做正则匹配，也类似，需要注意的就是表示正则表达式的那一列内容需要提前处理好转义字符，否则达不到预期目标。

用一些简单的案例来模拟验证上面判断逻辑是否能满足目标：

# 有关系的例子

example1:

ip_user_org = 'l1,l2,l3,l4,l5'
sso_user_org = 'l1,l2,l3,l4,l5'

a=b

example2:

ip_user_org = 'l1,l2,l3,l4'
sso_user_org = 'l1,l2,l3,l4,l5'

ip_user_org like concat(sso_user_org,'%') -> false
sso_user_org like concat(ip_user_org,'%') -> true

example3:

sso_user_org = 'l1,l2,l3,l4'
ip_user_org = 'l1,l2,l3,l4,l5'

ip_user_org like concat(sso_user_org,'%') -> true
sso_user_org like concat(ip_user_org,'%') -> false

example4:

sso_user_org = 'l1,l2,l3,l4,lx'
ip_user_org = 'l1,l2,l3,l4,l5'

ip_user_org like concat(sso_user_org,'%') -> false
sso_user_org like concat(ip_user_org,'%') -> false
substring_index(`ip_user_org`,',',3) = substring_index(`sso_user_org`,',',3) -> true

example5:

sso_user_org = 'l1,l2,l3,l4,lx'
ip_user_org = 'l1,l2,l3,l4,l5'

ip_user_org like concat(sso_user_org,'%') -> false
sso_user_org like concat(ip_user_org,'%') -> false
substring_index(`ip_user_org`,',',3) = substring_index(`sso_user_org`,',',3) -> true

[不及预期]example6:

sso_user_org = 'l1,l2,lx'
ip_user_org = 'l1,l2,l3'

ip_user_org like concat(sso_user_org,'%') -> false
sso_user_org like concat(ip_user_org,'%') -> false
substring_index(`ip_user_org`,',',3) = substring_index(`sso_user_org`,',',3) -> false

# 没有关系的例子

example7:

sso_user_org = 'l1,l2,l3,l4,lx'
ip_user_org = 'a1,a2,a3,a4'

ip_user_org = sso_user_org -> false
ip_user_org like concat(sso_user_org,'%') -> false
sso_user_org like concat(ip_user_org,'%') -> false
substring_index(`ip_user_org`,',',3) = substring_index(`sso_user_org`,',',3) -> false

好了，暂时先记录到这里，后面再视情况写些新的内容。

补充一下网上搜到的方法：

# method 1
CASE
WHEN CONCAT('%',"B",'%') LIKE CONCAT('%',"A",'%')  THEN 1 
WHEN CONCAT('%',"A",'%') LIKE CONCAT('%',"B",'%') THEN 1
ELSE 0
END AS col_relation

# method 2
instr(column1, column2) !=0

参考链接：

从 SSO 账号安全监控说起
https://ixyzero.com/blog/archives/5329.html

How to compare two columns using partially match result to display
https://community.yellowfinbi.com/topic/how-to-compare-two-columns-using-partially-match-result-to-display

hive join tables and extracts value based on column values
https://community.cloudera.com/t5/Support-Questions/hive-join-tables-and-extracts-value-based-on-column-values/td-p/196804

How to compare two columns with different data type groups
https://stackoverflow.com/questions/58240566/how-to-compare-two-columns-with-different-data-type-groups

=END=