Function not found: iscan_alg.ExtractOIDTLS

[JX-Wang]

Hello

When I run SQL on Google Cloud BQ, 2 functions not found error occur

1. Function not found: iscan_alg.ExtractOIDTLS(data)
2. Function not found: iscan_alg.ExtractTLSCert(data)

Seem These 2 functions are defined by your team to extract tls oid and cert, could your team please provide those codes?

[LizIzhikevich]

Thanks for pointing this out. Here is the code (I will put it on my todo list to integrate it into codebase..)

iscan_alg.ExtractTLSCert(data)

CREATE FUNCTION iscan_alg.ExtractTLSCert(datal string)
  RETURNS string
  AS ( (
SELECT  SUBSTR(certMes, 21, cast(CONCAT("0x",substr(certMes,0,6)) as int64)*2) server FROM (
SELECT SUBSTR(cert, cert_ind) certMes FROM (
SELECT  d,cert, STRPOS(cert,"0b") cert_ind FROM (
SELECT d,packets[OFFSET(2)] cert FROM (
SELECT  d,SPLIT(d, tls_v) packets FROM (
SELECT  d,SUBSTR(d, 0,6) tls_v
FROM (
SELECT TO_HEX(CAST(datal as bytes)) as d 
))
)
where ARRAY_LENGTH(packets) > 2
)
) where cert_ind <> 0)

where LENGTH(certMes) > 10
)
);

iscan_alg.ExtractOIDTLS(data):

(
  SELECT SPLIT(cert,"30") oids FROM (
  SELECT iscan_alg.ExtractTLSCert(datal) as cert
  ) where cert is not null
)

[JX-Wang]

The code works perfectly, thanks for your reply!