Need help with Global Server List

InfluxDB University 103 - Beginner Flux
, , , ,
1

Hi all,

I am looking for some help on my data structure as well as methods to get a more meaningful result on my my geomap in Grafana.

I am using powershell and node-red to scrape a website for server information. I am then taking those IP addresses I get from that process and running them through ipinfo.io to gain information about the specific IP addresses. (City, Region, Country, ISP, Lat, Lon)

I also have a basic ‘Total Players’ and ‘Total Servers’ and that is working perfectly. (it is being published on a separate _measurement.

The problem is the 1800 - 2400 servers I need to process. Originally I was attempting to post an 800kb table with every server in it. Item in the massive table consisted of the following structure. (its in JSON cause that is how node-Red shows it)

payload: {
Name: “REDACTED”
Description: “REDACTED”
IP: “REDACTED”
Port: “REDACTED”
Players: REDACTED
MaxPlayers: REDACTED
DCSVersion: “2.9.5.55918”
Lat: 45.3134
Lon: -73.8725
City: “Beauharnois”
Region: “Quebec”
Country: “CA”
ISP: “AS16276 OVH SAS”
TimeZone: “America/Toronto”
Postal: “J6N”
TimeStamp: 1720005068236
}
measurement: “SERVER STATS”

The Measurement is the same for every server as there isn’t really anything static about the information here, technically anything can potentially change. I since changed it from dumping an entire massive table to a single table (server) per post. Using the same “SERVER STATS” measurement.

What I am trying to work out is why I cant seem to get any locations to show on the map with the name of the server when i do a mouse over the point.

I want there to be another option to see players per server on the map as well but at this stage i would just settle for anything showing up on the map.

Any help is appreciated.

I am just starting out so we can change the data structure to anything you want. But I need to be able to attribute each server to a Lat Lon, City, Player number etc etc.

My DiscordID is OzDeaDMeaT if you are able to assist. Thanks again for reading this far.

2

Sorry, I didnt give much in the way of information about my setup. I am running an InfluxDB via a docker container, that is feeding a grafana docker container. I am using the Flux language syntax i believe. (I am just copying the query out of influxDB webui straight into the grafana query.

Grafana
Grafana1975×1126 129 KB

Each field seems to be in its own table, is that correct to get a map point? I looked at joins but they appear to be talking about multiple queries. Everything I want is here, its just not together. I am thinking I will create a UID using the IP and Port as there shouldn’t ever be two servers with the same IP and Port on at the same time.

3

OK I solved my own problem. Pivot Tables 4tw.

Here is the working query that got me most of what I wanted. I am just trying to work out how to only display the most recent item per server. It is 4am, that is a problem for another day.

For those looking for a possible solution to their problem. Here is the query.

from(bucket: “ALL-DCS-SERVERS”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “SERVER STATS”)
|> filter(fn: (r) => r[“_field”] == “UID” or r[“_field”] == “Lon” or r[“_field”] == “Lat” or r[“_field”] == “MaxPlayers” or r[“_field”] == “Name” or r[“_field”] == “Players” or r[“_field”] == “Description”)
|> pivot(rowKey:[“_time”], columnKey: [“_field”], valueColumn: “_value”)
|> group()
|> drop(columns: [“_start”, “_stop”, “_measurement”])
|> yield(name: “last”)

image
image1920×1235 115 KB

4

@ozdeadmeat Thanks for sharing yoru solution with the community! Greatly appreciate it.