Page 1 of 1
[RESOLVED] Request to view UC "Disconnected" - unreachable
Published: June 14, 2021 - 10:56 AM
by 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?
Re: Request to view UC "Disconnected" - unreachable
Published: June 15, 2021 - 10:39
by 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;
Re: [RESOLVED] Request to view UC "Disconnected" - unreachable
Published: June 15, 2021 - 4:36 PM
by 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']
Re: [RESOLVED] Request to view UC "Disconnected" - unreachable
Published: June 15, 2021 - 7:14 PM
by 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;
Re: [RESOLVED] Request to view UC "Disconnected" - unreachable
Published: June 15, 2021 - 7:14 PM
by 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
Re: [RESOLVED] Request to view UC "Disconnected" - unreachable
Published: June 16, 2021 - 09:50
by florentR2
With the double quotation marks, it works well on the console!
Have a good day!
Re: [RESOLVED] Request to view UC "Disconnected" - unreachable
Published: June 16, 2021 - 11:21
by 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.