hbbackend / schema / hbbackend_functions.sql

create or replace function  safe_insert_ident(
    idents.ident%type,
    out ret_id idents.id%type, out ret_inserted bool
  ) as $$
begin
  begin
    insert into idents (ident) values ($1) returning id into ret_id;
    exception
      when unique_violation then
        begin
          select id into ret_id from idents where ident = $1;
          ret_inserted := false;
          return;
        end;
  end;
  ret_inserted := true;
  return;
end;
$$ language plpgsql;


create or replace function  safe_insert_binary(
    binaries.md5%type, binaries.sha512%type, binaries.filetype%type, binaries.filesize%type, binaries.stored%type,
    out ret_id binaries.id%type, out ret_inserted bool, out ret_stored binaries.stored%type
  ) as $$
begin
  begin
    insert into binaries (md5, sha512, filetype, filesize, stored) values ($1, $2, $3, $4, $5) returning id, stored into ret_id, ret_stored;
    exception
      when unique_violation then
        begin
          select id, stored into ret_id, ret_stored from binaries where md5 = $1;
          ret_inserted := false;
          return;
        end;
  end;
  ret_inserted := true;
  return;
end;
$$ language plpgsql;


create or replace function  safe_insert_ip_source(
    ips_source.ip%type,
    out ret_ip ips_source.ip%type, out ret_inserted bool
  ) as $$
begin
  ret_ip = $1;
  begin
    insert into ips_source (ip) values ($1);
    exception
      when unique_violation then
        begin
          ret_inserted := false;
          return;
        end;
  end;
  ret_inserted := true;
  return;
end;
$$ language plpgsql;


create or replace function  safe_insert_ip_target(
    ips_target.ip%type,
    out ret_ip ips_target.ip%type, out ret_inserted bool
  ) as $$
begin
  ret_ip = $1;
  begin
    insert into ips_target (ip) values ($1);
    exception
      when unique_violation then
        begin
          ret_inserted := false;
          return;
        end;
  end;
  ret_inserted := true;
  return;
end;
$$ language plpgsql;


create or replace function update_ident_stats(idents.id%type)
  returns void as $$
declare
  ts idents.seen_last%type := transaction_timestamp();
begin
  update idents set seen_count = seen_count + 1
    where id = $1;
  update idents set seen_first = ts
    where ts < seen_first and id = $1;
  update idents set seen_last = ts
    where ts > seen_last and id = $1;
end;
$$ language plpgsql;


create or replace function update_binary_stats(binaries.id%type)
  returns void as $$
declare
  ts binaries.seen_last%type := transaction_timestamp();
begin
  update binaries set seen_count = seen_count + 1
    where id = $1;
  update binaries set seen_first = ts
    where ts < seen_first and id = $1;
  update binaries set seen_last = ts
    where ts > seen_last and id = $1;
end;
$$ language plpgsql;


create or replace function update_ip_source_stats(ips_source.ip%type)
  returns void as $$
declare
  ts ips_source.seen_last%type := transaction_timestamp();
begin
  update ips_source set seen_count = seen_count + 1
    where ip = $1;
  update ips_source set seen_first = ts
    where ts < seen_first and ip = $1;
  update ips_source set seen_last = ts
    where ts > seen_last and ip = $1;
end;
$$ language plpgsql;


create or replace function update_ip_target_stats(ips_target.ip%type)
  returns void as $$
declare
  ts ips_target.seen_last%type := transaction_timestamp();
begin
  update ips_target set seen_count = seen_count + 1
    where ip = $1;
  update ips_target set seen_first = ts
    where ts < seen_first and ip = $1;
  update ips_target set seen_last = ts
    where ts > seen_last and ip = $1;
end;
$$ language plpgsql;


-- sync with hbstats.create_table_new_main()
create or replace function hbstats_new_main(attacks.id%type)
  returns table(
    id		 bigint,
    ts		 timestamp with time zone,
    ident_id	 bigint,
    ident	 varchar(16),
    binary_id	 bigint,
    md5          char(32),
    sha512       char(128),
    source_ip	 inet,
    target_port  int,
    target_ip    inet,

    agg_id          bigint,
    ident_id_local  bigint,
    binary_id_local bigint
  )
as $$

select
    attacks.id, ts, ident_id, ident, binary_id, md5, sha512, source_ip, target_port, target_ip,
    null::bigint as agg_id, null::bigint as ident_id_local, null::bigint as binary_id_local
  from attacks
  left join idents on attacks.ident_id = idents.id
  left join binaries on attacks.binary_id = binaries.id
  where attacks.id > $1
  limit 10000
;

$$ language sql;

-- sync with hbstats.create_table_new_virustotal()
create or replace function hbstats_new_virustotal(attacks.id%type, attacks.id%type)
  returns table(
    md5            char(32),
    response_code  int,
    result_count   bigint,
    scan_id    	   varchar,
    scan_date      timestamp with time zone,
    permalink      varchar,
    ts             timestamp with time zone,
    label          varchar,
    engine         varchar,
    e_version      varchar,
    e_update       varchar
  )
as $$

with a as (
  select distinct binary_id
  from attacks where id >= $1 and id <= $2
)
select
    md5, response_code, result_count, scan_id, scan_date, permalink, ts,
    label, engine, e_version, e_update
  from (select * from (select *, max(id) over (partition by binary_id) as id_max
                         from virustotal_reports
                         where binary_id in (select binary_id from a)
                      ) as t
          where id = id_max
       ) as t
  join binaries on t.binary_id = binaries.id
  left join virustotal_results on t.id = virustotal_results.report_id
;

$$ language sql;


-- sync with hbstats.create_table_new_asn_shadowserver()
create or replace function hbstats_new_asn_shadowserver(attacks.id%type, attacks.id%type)
  returns table(
    ip          inet,
    asn         bigint,
    as_name     varchar,
    cc          char(2),
    dom         varchar,
    isp         varchar,
    bgp_prefix  inet,
    ts          timestamp with time zone
  )
as $$

with a as (
  select distinct source_ip as ip from attacks where id >= $1 and id <= $2 union
  select distinct target_ip as ip from attacks where id >= $1 and id <= $2
)
select
    ip, asn, as_name, cc, dom, isp, bgp_prefix, ts
  from (select * from (select *, max(id) over (partition by ip) as id_max
                         from asn_shadowserver
                         where ip in (select ip from a)
                      ) as t
          where id = id_max
       ) as t
;

$$ language sql;


-- sync with hbstats.create_table_new_geoip_shadowserver()
create or replace function hbstats_new_geoip_shadowserver(attacks.id%type, attacks.id%type)
  returns table(
    ip         inet,
    cc         char(2),
    city       varchar,
    latitude   float,
    longitude  float,
    ts         timestamp with time zone
  )
as $$

with a as (
  select distinct source_ip as ip from attacks where id >= $1 and id <= $2 union
  select distinct target_ip as ip from attacks where id >= $1 and id <= $2
)
select
    ip, cc, city, latitude, longitude, ts
  from (select * from (select *, max(id) over (partition by ip) as id_max
                         from geoip_shadowserver
                         where ip in (select ip from a)
                      ) as t
          where id = id_max
       ) as t
;

$$ language sql;
Tip: Filter by directory path e.g. /media app.js to search for public/media/app.js.
Tip: Use camelCasing e.g. ProjME to search for ProjectModifiedEvent.java.
Tip: Filter by extension type e.g. /repo .js to search for all .js files in the /repo directory.
Tip: Separate your search with spaces e.g. /ssh pom.xml to search for src/ssh/pom.xml.
Tip: Use ↑ and ↓ arrow keys to navigate and return to view the file.
Tip: You can also navigate files with Ctrl+j (next) and Ctrl+k (previous) and view the file with Ctrl+o.
Tip: You can also navigate files with Alt+j (next) and Alt+k (previous) and view the file with Alt+o.