https://github.com/osquery/osquery logo
Title
k

K

11/02/2022, 9:53 PM
Any idea why I get no column
data
for this query:
SELECT l.port, group_concat(DISTINCT l.port) AS 'data'
FROM listening_ports AS l
WHERE l.address != "127.0.0.1"
I also see only one port, exptected 2 ports
z

zwass

11/02/2022, 10:36 PM
fwiw I get a bunch of results:
osquery> SELECT l.port, group_concat(DISTINCT l.port) AS 'data'
    ...> FROM listening_ports AS l
    ...> WHERE l.address != "127.0.0.1"
    ...> ;
+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| port | data                                                                                                                                                                                                                                                                                                |
+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 0    | 0,8021,137,138,49154,49155,49156,49157,49158,49159,49160,49161,49162,49163,57088,5353,56647,3722,7000,5000,55285,7579,16587,62912,60499,60538,59334,3308,8412,7003,7002,6379,3307,7004,7005,9000,9001,1025,8025,3306,7001,7006,9080,9090,9443,4566,4571,1337,57621,50853,1900,60351,56723,49738,631 |
+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
a

Aleksandr Maus

11/05/2022, 2:01 PM
Looks like it is a defect in the osquerybeat approach specifically. It relies on the column datatype to attempt to convert the resulting data from string into concrete type. In this case it gets BIGINT as a data type but the value itself is a string of comma separated numbers that can’t be parses into one number
BEFORE RESOLVE TYPES: HITS: []map[string]string{map[string]string{"data":"0,22,137,138,49154,49155,49156,49157,49158,49159,49160,49161,49162,49163,54923,5353,705,984,1023,1022,2049,623,849,1021,1019,111,962,1015,1017,1012,726,688,813,999,996,7000,5000,52547,53846,56430,50635,58907,50055", "port":"0"}}, COLTYPES: map[string]string{"data":"BIGINT", "port":"INTEGER"}
The value is discarded if it can’t be converted in the current implementation. Fixing osquerybeat to keep the field as is even if it can’t be converted would be a solution. Will address that.
@zwass don’t know what is involved on osqueryd side. should osquery report a string/text data type for that column that is returned from the
group_concat
function?
It’s possibly a defect in osquery. For example for this query
SELECT cast(pid as text) text_pid from osquery_info
The column type is returned as TEXT
BEFORE RESOLVE TYPES: HITS: []map[string]string{map[string]string{"text_pid":"5551"}}, COLTYPES: map[string]string{"text_pid":"TEXT"}
Should the osquery for the query above with
group_concat
return TEXT as a type instead of BIGINT?
the workaround for this defect for now
SELECT cast(group_concat(DISTINCT port) as text) as data FROM listening_ports
in this case the field type is returned as TEXT from osquery