Client Map

[mrmrslobos]

Hi,

Has there been a fix for the Client map. I get the "db query error: pq: column "stdout" does not exist"

SELECT
agents_agentcustomfield.string_value AS "location",
concat(agents_agent.hostname, ' / ', agents_agent.description) as "nameAgent",
greatest (
CASE
--WHEN CAST(substring(stdout from ', failing: (.+), warning') AS DECIMAL ) = 1 THEN 201
WHEN CAST(substring(stdout from ', failing: (.+), warning') AS DECIMAL ) = 0 THEN 0
ELSE 100
END,
CASE
--WHEN CAST(substring(stdout from '\warning: (.+), info') AS DECIMAL ) = 1 THEN 101
WHEN CAST(substring(stdout from '\warning: (.+), info') AS DECIMAL ) = 0 THEN 0
ELSE 200
END
) as "finalState"
FROM
checks_check,
agents_agent
INNER JOIN clients_site on site_id = clients_site.id
LEFT OUTER JOIN agents_agentcustomfield on agents_agent.id = agents_agentcustomfield.agent_id
LEFT OUTER JOIN core_customfield on agents_agentcustomfield.field_id = core_customfield.id
WHERE
(field_id IN (SELECT id FROM core_customfield WHERE name = 'geohas')
OR field_id is null)
and agents_agentcustomfield.string_value <> ''
and checks_check.agent_id = agents_agent.id
AND site_id IN (SELECT id FROM clients_site WHERE client_id IN (SELECT id FROM clients_client WHERE name IN ($Client)))

Here is the query that I am using. Has any got this working? The script is setup in the RMM to populate the geohash field. But Grafana can't find the stdout field.

[sistemmsn]

So this is more of an issue with the Grafana map than with the script?

[dinger1986]

I don't know sorry

[calbertouepb]

Hi everybody, after a huge code audit, I get rid off the error presented, and the view are fully functional. This is all the changes I have made:

First: changed the "stout" with the table used to get the values:

From: "WHEN CAST(SUBSTRING(stdout from '\, failing: (.+)\, warning') AS DECIMAL ) = 0 THEN 0"
To: "WHEN CAST(SUBSTRING(checks_check.alert_severity FROM ', failing: (.+), warning') AS DECIMAL) = 0 THEN 0"

From: "WHEN CAST(SUBSTRING(stdout from '\warning: (.+)\, info') AS DECIMAL ) = 0 THEN 0"
To: "WHEN CAST(SUBSTRING(checks_check.alert_severity FROM '\warning: (.+), info') AS DECIMAL) = 0 THEN 0"

Another change I made to present all the geohas was simplify the query, I wipe off the clause with null condition to show only the nodes with information,

From:
WHERE
(field_id IN (SELECT id FROM core_customfield WHERE name = 'geohas')
OR field_id is null)
To:
WHERE
agents_agentcustomfield.field_id IN (SELECT id FROM core_customfield WHERE name = 'geohas')

and excluded the "and checks_check.agent_id = agents_agent.id" condition.

In my case the dashboard is working fine. Hope helps someone. Thanks a lot!!

[sistemmsn]

Hi everybody, after a huge code audit, I get rid off the error presented, and the view are fully functional. This is all the changes I have made:

First: changed the "stout" with the table used to get the values:

From: "WHEN CAST(SUBSTRING(stdout from ', failing: (.+), warning') AS DECIMAL ) = 0 THEN 0" To: "WHEN CAST(SUBSTRING(checks_check.alert_severity FROM ', failing: (.+), warning') AS DECIMAL) = 0 THEN 0"

From: "WHEN CAST(SUBSTRING(stdout from '\warning: (.+), info') AS DECIMAL ) = 0 THEN 0" To: "WHEN CAST(SUBSTRING(checks_check.alert_severity FROM '\warning: (.+), info') AS DECIMAL) = 0 THEN 0"

Another change I made to present all the geohas was simplify the query, I wipe off the clause with null condition to show only the nodes with information,

From: WHERE (field_id IN (SELECT id FROM core_customfield WHERE name = 'geohas') OR field_id is null) To: WHERE agents_agentcustomfield.field_id IN (SELECT id FROM core_customfield WHERE name = 'geohas')

and excluded the "and checks_check.agent_id = agents_agent.id" condition.

In my case the dashboard is working fine. Hope helps someone. Thanks a lot!!

But what script do you run on the server side so you get the coordinates.

[dinger1986]

Do you want to submit a PR?

[calbertouepb]

Hi everybody, after a huge code audit, I get rid off the error presented, and the view are fully functional. This is all the changes I have made:
First: changed the "stout" with the table used to get the values:
From: "WHEN CAST(SUBSTRING(stdout from ', failing: (.+), warning') AS DECIMAL ) = 0 THEN 0" To: "WHEN CAST(SUBSTRING(checks_check.alert_severity FROM ', failing: (.+), warning') AS DECIMAL) = 0 THEN 0"
From: "WHEN CAST(SUBSTRING(stdout from '\warning: (.+), info') AS DECIMAL ) = 0 THEN 0" To: "WHEN CAST(SUBSTRING(checks_check.alert_severity FROM '\warning: (.+), info') AS DECIMAL) = 0 THEN 0"
Another change I made to present all the geohas was simplify the query, I wipe off the clause with null condition to show only the nodes with information,
From: WHERE (field_id IN (SELECT id FROM core_customfield WHERE name = 'geohas') OR field_id is null) To: WHERE agents_agentcustomfield.field_id IN (SELECT id FROM core_customfield WHERE name = 'geohas')
and excluded the "and checks_check.agent_id = agents_agent.id" condition.
In my case the dashboard is working fine. Hope helps someone. Thanks a lot!!

But what script do you run on the server side so you get the coordinates.

Community scripts: https://github.com/amidaware/community-scripts/blob/main/scripts_wip/Win_Location_Get

[calbertouepb]

Do you want to submit a PR?

As you wish. How I do?

[dinger1986]

fork the repo, make the changes then pr it back

[sistemmsn]

Hi, I've been doing some tests, but the teams don't appear on the Grafana maps.

It seems that it does bring the data, but I don't know what happens with Grafana

I also used this one but it doesn't show anything in grafana

https://github.com/amidaware/community-scripts/blob/main/scripts_wip/Win_Location_Get.ps1

mmmm... I don't know but something isn't working right hehehe

[calbertouepb]

Hi @sistemmsn if you use location coords (lat and long) needs work with two fields, "latitude" and "longitude" separately, and in grafana change the Location Mode to Coords.
The code updated works with GeoHash, and needs only one field with the location information. In the grafana uses the "Geohash" mode, and the "geohash" field, as configured by you. Lets did some play with manual locations to learn how it's works. Thanks.