基于SQL Server的情报数据分析实践

2013-04-11 14:58杨雁莹
湖北警官学院学报 2013年12期
关键词:话单子句情报人员

杨雁莹

(南京森林警察学院 信息技术系,江苏 南京210023)

基于SQL Server的情报数据分析实践

杨雁莹

(南京森林警察学院 信息技术系,江苏 南京210023)

对涉案基础数据进行深度分析研判是获取有效情报的重要手段,然而人工分析耗时太久,贻误战机,而情报数据分析软件耗资巨大。利用SQLServer数据库管理系统实现话单次数时长分析、新号码分析、数据比对碰撞等常用情报数据分析功能,在不增加任何费用的基础上完成了软件系统的类似功能,且简单易学、使用灵活,对基层公安机关的情报人员学习有一定的帮助。

SQL Server;情报数据;数据分析

一、引言

随着公安信息化建设的发展,各种类型的公安数据库应用系统相继建立,同时也催生了各种各样的情报数据分析软件。这些软件依托公安信息基础数据库,利用数据仓库、数据挖掘技术对涉案数据进行分析处理,为情报人员、侦查人员对案件的研判提供了便利。但这些软件系统的费用动辄几十万甚至上百万,为公安机关增加了经费开支,特别是基层处所购买这类软件负担很重。

笔者在使用这类情报数据分析软件时发现,其中很多常用数据分析功能都可以用SQL Server中的T-SQL语句实现。笔者使用免费的SQLServer2005Express进行了尝试,发现其简单易学,同时不增加任何经费开支。

二、SQL Server与excel表数据的互访

民警在日常工作中经常使用各个公安信息基础数据库,大多提供了多种查询操作。情报人员根据案情分析,可首先进行数据的初步查询,筛选得到待分析的批量嫌疑数据。因为数据库的构建可能基于不同的数据库管理系统,比较通用的方法是将查询结果导出,生成excel表,然后利用SQLServer访问excel表数据,或者直接将excel表导入SQL Server中进行分析研判。

SQLServer数据库和异构数据库之间的数据传递常用的有两种方式,使用链接服务器和使用分布式查询。链接服务器的建立称作数据源的永久性连接,如果经常进行excel表和SQL Server数据库之间的导入导出,可使用该种方法。分布式查询则是为单个查询建立的临时连接。因为SQLServer2005express中没有设置数据导入导出功能,可以使用T-SQL语句实现这两种连接的建立,从而完成excel表和数据库表的数据交互。在此,以临时连接、即分布式查询方法为例,excel以2003版本为例进行详细介绍。

(一)将excel表数据导入数据库表

分布式查询可以使用SQL Server中的OPENDATASOURCE或OPENROWSET函数来实现。OPENDATASOURCE可以打开任何支持OLEDB的数据库,并且可以将OPENDATASOURCE做为SELECT、UPDATE、INSERT和DELETE后所跟的表名。OPENROWSET函数和OPENDATASOURCE函数类似,只是它可以在打开数据库的同时对数据库中的表进行查询。本文以OPENDATASOURCE为例进行说明。

OPENDATASOURCE的语法格式为:OPENDATASOURCE(provider_name,init_string)

其中的参数:provider_name为注册用于访问数据源的OLE DB访问接口的PROGID的名称。init_string为连接字符串,该字符串将要传递给目标提供程序的 IDataInitialize接口。

比如下面的T-SQL语句:

SELECT*FROM OPENDATASOURCE('M icrosoft. Jet.OLEDB.4.0','Data Source=D:ill.xls;Extended Properties=Excel8.0')...Sheet1$

将创建与excel电子表格的临时链接,并查询本地服务器D盘根目录下的bill.xls文件中sheet1工作表中的数据。

该方法也可在select语句中,增加into子句,将excel表直接存储为SQL Server的库表。如:SELECT*into hd FROM OPENDATASOURCE('M icrosoft.Jet.OLEDB.4.0',' Data Source=D:ill.xls;Extended Properties=Excel 8.0')...Sheet1$则将excel表数据存储为当前数据库中的表hd,库表的属性根据excel表列自动生成。数据库表生成后,即可方便地进行各种查询。

(二)将数据库表输出到excel

利用数据库管理系统对数据进行各种查询分析后,获得的有效信息可直接用库表的形式存储。如果需要将该信息与别人交流,转换成excel格式是更方便的渠道。常用的一种做法是用Insertinto语句实现。INSERT into用于将新行追加到表中,可以利用OPENDATASOURCE函数直接将数据库表追加的excel表中。语法格式为:

Insert Into OPENDATASOURCE('M icrosoft.Jet.OLEDB.4.0','Data Source={Your Excel File Path};Extended Properties=Excel8.0')...[SheetName$]Values(....)

如执行以下语句:

INSERT INTO OPENDATASOURCE('M icrosoft.Jet. OLEDB.4.0','DATA Source=D:ook1.xls;Extended Properties=Excel8.0')...Sheet1$SELECT*FROM xy

该语句将数据库表 xy中的内容存入 D盘根下的book1.xls表中。需要注意的是,book1.xls要已存在,且Sheet1中必须有和数据库表xy相对应的字段。

在SQLServer Express中,为提高系统安全性,减少对系统潜在的攻击途径,一些外围应用如某些功能、服务和连接是被禁用或停止的,其中包括使用OPENROWSET和OPENDATASOURCE进行的 Ad Hoc Distributed Queries即席分布式查询)。因此,要想使用此方法,需要启用Ad Hoc Distributed Queries。

可利用系统存储过程sp_configure实现。新建查询,编写并执行如下语句:

exec sp_configure'show advanced options',1

reconfigure

exec sp_configure'Ad Hoc Distributed Queries',1

reconfigure

此后,上述对excel表的查询即可成功。为提高系统安全性,查询结束后可利用如下语句进行Ad Hoc Distributed Queries的关闭。

exec sp_configure'Ad Hoc Distributed Queries',0

reconfigure

exec sp_configure'show advanced options',0

reconfigure

三、利用SQL Server实现情报数据分析

在建立起excel表和SQL Server的连接后,即可利用SQL Server的T-SQL语句,对待分析数据进行查询分析,主要利用的就是select命令。Select命令是SQLServer中使用频率最高的语句,可用来实现各种各类查询。

SELECT语句的完整语法较复杂,但其主要子句可归纳如下:SELECT select_list[INTO new_table][FROM table_source][WHERE search_condition][GROUPBY group_by_expression][HAVING search_condition][ORDER BY order_expression[ASC|DESC]]

需要注意的是,SELECT语句中的子句顺序非常重要。可以省略可选子句,但这些子句在使用时必须按适当的顺序出现。

(一)话单次数时长分析

话单分析是现在情报人员最常用的数据分析研判手段之一。话单信息客观记录了通话双方的详细情况,反映出持机人与被叫人时间、空间活动变化的轨迹以及双方之间的内在联系,将话单信息与案件中嫌疑人的活动轨迹、涉案电话相关联,可以为甄别作案嫌疑,明确侦查方向,摸清团伙成员,跟踪锁定对象提供重要依据。目前,主要利用的话单信息有以下几种:机主资料、主叫号码、被叫号码、通话时间、通话时长、基站、主叫归属地、主叫到访地、被叫归属地、被叫到访地、电子串号等。

话单分析中最常见的是话单次数时长分析,也称频率分析。即在一定范围内,将联系人号码重复出现的次数、频率或通话时长进行排列,进而分析规律。次数时长分析是查找机主密切联系人的常用手段。

话单次数时长分析体现在数据库中就是利用group by子句,按照不同的通话号码进行分组统计,利用count()、sum ()等统计函数,计算每个号码的通话次数和时间总和,利用orderby子句,按照由多到少、由长到短实现统计结果的排序。

以移动手机的语音话单为例,话单中包括类型、对方号码、起始时间、时长、通信地点、通信类型、基本话费、长途话费、实收通信费、套餐费、作用套餐等内容。在分析次数时长时,主要统计的就是“时长”一项。但不同的通讯运营商话单格式不同,有的话单中的时长采用的是hh-mm-ss格式,为精确统计,可统一换算成以秒为单位。

首先将手机语音话单的excel表导入数据库中,生成库表,设定表名为“话单”。需要注意的是,所生成的库表属性名称自动与excel表的列名称相同,但是数据类型均为字符型。然后,编写如下语句,实现话单次数时长的统计排序。

select对方号码,通话次数=count(*),通话总长=sum (substring(时长,1,2)*3600+substring(时长,4,2)*60+substring(时长,7,2))from话单group by对方号码order by通话次数desc,通话总长desc

SQL Server中可以自动进行简单的数据类型转换,所以上述语句中直接进行了数学计算。否则,还需利用函数进行字符型数据到数值型数据的转换。

情报分析人员可通过简单的语句调整,重新确定排序关键字的顺序,也可通过增加where子句,限定分析数据的通话日期、时间范围,或者通过增加having子句,筛选次数时长在某个阈值以上的对方号码。

当然,在具体案件中,次数时长排位靠前的未必就是案件重点联系人,要考虑可能会受到嫌疑人亲情号码的干扰,要注意甄别。

(二)话单中新出现号码的分析

在很多案件中,常常出现案发后嫌疑人将涉案手机停机不用的情况。这时,利用技侦手段通过手机定位找人难以实现。一种侦查思路是利用涉案手机的通话清单,分析出其密切联系人,再对其密切联系人的通话清单进行分析研判,查找在案发后的一定时间段内,当涉案手机停用后该话单中出现的新号码,该号码就有可能是案件嫌疑人新的联系方式。侦查人员可以此为线索进行进一步的排查。

为确定是否为“新”,需依据一段时间的通话情况进行判断。比如选择案发前3个月的通话号码为参照,若在前3个月中没有出现过,就可认为是新号码。

新出现号码反映到数据库表中,就是某属性值在参照表中不存在。可利用select语句的嵌套查询实现。

仍然以移动手机的语言话单为例。假设案发时间为2013年5月1日,利用如下语句生成参照号码表b1。

Selectdistinct对方号码into b1 from话单where起始时间between'2013-02-01'and'2013-05-01'

假定案发后的通话清单表为b2,可编写T-SQL语句:

select*from b2where对方号码notin(select对方号码from b1)

该语句可获得所有新出现号码的通话清单,在此基础上,可利用前述的方法进行次数时长的分析,再由侦查人员进行研判排查。

利用SQL Server还可以实现多话单中共同联系人的查询、通话清单中时段特征分析等很多功能。只要对T-SQL语句灵活运用,SQL Server可辅助情报人员实现很多情报数据分析功能。

(三)数据比对碰撞

数据比对碰撞也称为信息碰撞、批量数据比对等,是现在常用的可疑数据分析处理方法。犯罪分子再狡猾,也离不开社会生活,他的住、行、通讯等总会留下痕迹。数据比对碰撞就可充分利用航班、旅馆业、暂住人口、网吧管理等各种信息库资料以及社会基础信息资料,根据串并案信息制作批量比对文件,或者与全国在逃人员、涉毒人员、违法犯罪人员、本地工作对象等资源库数据资料进行碰撞比对,从而为侦查人员缩小排查范围,为发现嫌疑人提供线索。

数据比对碰撞体现在数据库中是在两个(或多个)表中查找某关键字相同的元组。由关键字相同进行表的连接查询是操作关键。

比如在案件侦查中,侦查人员发现可以将2013年1月在遵义的一起盗窃案和2013年4月在兴义的案件进行串并。情报人员进行碰撞比对,调出在两个城市相应时间段内的住宿信息共1万6千多条,由专人进行逐条比对,耗费了一天半的时间比对出两个嫌疑人。利用本文提到的方法,分别将两个城市的住宿信息文档读入数据库中,设定库表文件名分别为遵义、兴义,利用身份证号码相同进行碰撞比对,几分钟的时间即可比对成功。语句描述为:

Select a.姓名,a.身份证号,a.入住时间,a.宾馆名称,b.入住时间,b.宾馆名称from遵义a join兴义b on a.身份证号= b.身份证号

一条语句即可查出所有在涉案时段内、在涉案两地都有住宿的人员。根据需要,也可在select中增加退房时间、身份证住址等属性列,以方便侦查人员的排查。若在系统调出的原始住宿记录中时间、地点等与要求不符,也可先利用select中的where语句进行筛选,将筛选结果再进行比对碰撞。

四、结语

本文介绍的方法适合在没有情报数据分析软件的基层处所使用。只要情报人员对数据库技术有初步的认识,对T-SQL语句特别是select命令稍加学习,就可灵活运用。由情报人员自己编写语句进行数据分析,可最大限度地体现情报人员自身的侦查思路,同时使用免费版的 SQL Server2005Express,不需构建硬件服务器,不需软件费用支出,在公安类数据库数据查询结果和情报人员对案情初步分析的基础上,对数据进行进一步的分析,为侦查人员工作提供线索和方向,为案件的研判提供帮助。

D631

A

1673―2391(2013)12―0033―03

2013-07-04 责任编校:边 草

中央高校基本科研业务费专项资金项目“面向案件文本的敏感数据抽取技术研究”成果之一,项目编号LGZD201324。

猜你喜欢
话单子句情报人员
命题逻辑中一类扩展子句消去方法
命题逻辑可满足性问题求解器的新型预处理子句消去方法
河北大名话单元音韵母、单字调及双音节非轻声词连调的实验语音学初探
新形势下图书情报人员的知识能力培养有效方法
西夏语的副词子句
大数据时代下企业情报人员培养现状及其思考
基于个体优势的情报人员胜任特征评析
命题逻辑的子句集中文字的分类
如何培养图书情报人员的个人素质
GSM-R移动交换机ASN.1话单的解码