[RESOLVED] Request to view UC "Disconnected" - unreachable

[florentR2]

Hello,
I want to create an SQL query to export all PCs in an OU that have a "Disconnected" status.
I searched the "hosts" table.
I did find the "reachable" attribute, but is that correct because sometimes it's set to NULL even though the PC appears reachable on my WAPT console?

[dcardon]

In the latest version of WAPT, the WebSocket table has been separated because it changes very frequently, and PostgreSQL didn't handle it very well (a problem related to the TOAST table with JSON blobs that created VACUUM errors). Therefore, you need to query the following table: hostwebsocket. You can try something like this:

select session_id, hostwebsocket.host_id, hosts.uuid, hosts.computer_fqdn from hostwebsocket, hosts where hostwebsocket.host_id = hosts.uuid and session_id is null;

[florentR2]

Thank you, that's exactly what I'm looking for!
However, I wanted to add the test of a string with a LIKE % in the computer_ad_ou, this works with Dbeaver but not from the reporting tab on the console.
This doesn't bother me because I'm going to use it with a Python script, but I wanted to report it to find out if this is normal.
example:

Code: Select all

select computer_name, computer_ad_ou
from hostwebsocket, hosts 
where hostwebsocket.host_id = hosts.uuid
and session_id is null
[b]and computer_ad_ou LIKE '%Salles%'[/b]
order by computer_ad_ou ASC;    

Code: Select all

Error on server
IndexError['tuple index out of range']

[htouvet]

Yes, there is a small problem with % which is interpreted by Python.
Can you try doubling all the %

Code: Select all

select computer_name, computer_ad_ou
from hostwebsocket, hosts 
where hostwebsocket.host_id = hosts.uuid
and session_id is null
and computer_ad_ou LIKE '%%Salles%%'
order by computer_ad_ou ASC;    

[dcardon]

There's a bug that's crept in, indeed. The percentages need to be doubled... I'll check with Hubert to fix it.

select computer_name, computer_ad_ou
from hostwebsocket, hosts
where hostwebsocket.host_id = hosts.uuid
and session_id is null
and computer_ad_ou LIKE '%%Rooms%%'
order by computer_ad_ou ASC;

Denis

[florentR2]

With the double quotation marks, it works well on the console!
Have a good day!

[dcardon]

After digging a little deeper, it seems this is a bug/feature of the psycopg2 library...

https://www.psycopg.org/docs/usage.html ... s-and-like

We need to find a simple and elegant way to fix this. For now, we'll add it to the documentation.