Linked server tot icingadb (postgresql)

Icinga 2 Icinga DB
1

the basic stuf works fine ! also select a table to SQL like :

SELECT * FROM OPENQUERY (icinga2 , ‘select * from public.hostgroup_member’);

but this one :
SELECT * FROM OPENQUERY (icinga2 , 'select * from public.hostgroup_member where HOST_ID = ‘‘0xF310E058986E8ED0A7E20D05FB90E6333E69B1E0’’ ');

does not work no record, but 100 % for sure the value is in the table.
but wat is the real datatype for host_id

Bigint ?
Serial ?

SQL set it tot image datatype … hoe can i query the tabels ?

2

it is binary
do something like this for mariadb/mysql:

... WHERE `host`.`id` = CAST(0xF310E058986E8ED0A7E20D05FB90E6333E69B1E0 AS BINARY)

havn’t tried for postgres:

... WHERE id = decode('F310E058986E8ED0A7E20D05FB90E6333E69B1E0', 'hex')::bytea;
1 Like
3

this works so thanx for that

SELECT * FROM OPENQUERY (icinga2 , ‘select * from public.hostgroup_member where id = decode ( ‘‘E4872E0ABDCAABBAEE010FF06D38E67A42483764’’, ‘‘hex’’ ) ::bytea’);

how to get the whole table in SQL i try this, but no results but an error in SSMS

SELECT * FROM OPENQUERY (icinga2 , 'select name, decode (id, ‘‘hex’’ ) ::bytea from public.host ') ;

but the problem is that de id is not stripped with the 0x