Grafana Field Parsing from Elastic Search

Grafana Geomap Panel
1

Hi all,

I have been using Grafana for dashboards on SQL data sources without issue. I am now moving to an Elastic datasource and I am out of my element. I have learned quite a bit, but I am stuck on how to do something that seems simple.

I have geohash data supplied to Grafana in Latitude, Longitude (51.507351,-0.127758). The Geomap Panel seems to want the geohash data to be Longitude, Latitude. My North American geohashes all appear to be in the Pacific Ocean and not on land.

The solution seems simple, manipulate the geohash so the values are reversed OR parse them out so I am individually map Latitude and Longitude with the visual. My problem…how? I have been experimenting with the Transformations, but I cannot see how to parse out a field. Am I missing something obvious?

I am using a query source as “Logs” from Elastic, here is a sample of the results set. Thanks for any help!

0:Object
_index:"my_index"
_type:"_doc"
_id:"c4187890-"
_score:null
_source:Object
Destination:"dataABC"
User:"john@acme"
gl2_remote_ip:"192.168.1.1"
gl2_remote_port:1234
source:"ABCD:"
gl2_source_input:"6586"
Source:"40.347.154.11"
LogTimeEST:"2023-12-17 11:10:16"
gl2_source_node:"35cf5dccc20fd"
timestamp:"2023-12-17 16:10:16.089"
Agent:"MyAgent"
gl2_accounted_message_size:896
level:5
Priority:"5"
streams:Array[2]
0:"000000000000000000000001"
1:"65774c82fa88f33bf8eb482c"
gl2_message_id:"01HHW8NQCVV1A"
message:"YadaYadaYada""
Source_geolocation:"34.6618,-116.532"
Source_country_code:"US"
FirewallWANIP:"192.168.1.1"
LogMessage:"WireGuard connected"
facility_num:22
Source_city_name:"The Desert"
facility:"local6"
sort:Array[1702829416089,0]
2

Welcome to forum @eradtke

Can you please provide a properly fornatted json text? Also please point us to the geohash piece

4

Hi Yosiasz,

Thank you for the reply, I provided another piece of JSON text that is better formatted. The geohash piece is my Elastic data has this supplied to me as location data:

            "Source_geolocation": "39.123,-85.3325",

And I believe the data needs to be like this:

            "Source_geolocation": "-85.3325,39.123",

My hope is that I can be advised the best way to convert the first supplied value into the second one OR parse out the two numeric values into their own fields. I have explored the Transformation section of the Geomap visual, but I have not been able to achieve the desired result. Thanks again for the help!

         {
              "_index": "IndexABC",
              "_type": "_doc",
              "_id": "2020cbb0-9ce8",
              "_score": null,
              "_source": {
                "Destination": "PlaceABC",
                "User": "JOHN@Acme",
                "gl2_remote_ip": "192.168.1.1",
                "gl2_remote_port": 1234,
                "source": "ABC123:",
                "gl2_source_input": "6577477886",
                "Source": "322.8.12.123",
                "LogTimeEST": "2023-12-17 09:25:28",
                "gl2_source_node": "35cf5dc6-62cc-4f1a-9e41-69c19ecc20fd",
                "timestamp": "2023-12-17 14:25:28.043",
                "Agent": "AgentABC",
                "gl2_accounted_message_size": 894,
                "level": 4,
                "Priority": "4",
                "streams": [
                  "000000000000000001",
                  "65774c82fa8eb482c"
                ],
                "gl2_message_id": "01HHW2N23Y3PRJ87",
                "message": "YadaYadaYada",
                "Source_geolocation": "39.123,-85.3325",
                "Source_country_code": "US",
                "FirewallWANIP": "192.168.1.1",
                "LogMessage": "LOGS connected",
                "facility_num": 22,
                "Source_city_name": "Osgood",
                "facility": "local5"
              },
              "sort": [
                1702878728043,
                0
              ]
            },
1 Like
5

Not a valid json data. please vet it using json validator?

6

The JSON I pasted is just one object in the array returned. It is valid if you remove the last comma in the text. My issue has not been with the JSON data itself, but how to do string manipulations on specific field:

"Source_geolocation": "39.123,-85.3325",

I am hoping to parse out the two comma-separated values into separate fields or reverse them. How can that be done with Grafana with an Elastic datasource?

Thanks again, here is the JSON repasted in a manner that will test as valid.

{
    "_index": "IndexABC",
    "_type": "_doc",
    "_id": "2020cbb0-9ce8",
    "_score": null,
    "_source": {
        "Destination": "PlaceABC",
        "User": "JOHN@Acme",
        "gl2_remote_ip": "192.168.1.1",
        "gl2_remote_port": 1234,
        "source": "ABC123:",
        "gl2_source_input": "6577477886",
        "Source": "322.8.12.123",
        "LogTimeEST": "2023-12-17 09:25:28",
        "gl2_source_node": "35cf5dc6-62cc-4f1a-9e41-69c19ecc20fd",
        "timestamp": "2023-12-17 14:25:28.043",
        "Agent": "AgentABC",
        "gl2_accounted_message_size": 894,
        "level": 4,
        "Priority": "4",
        "streams": [
            "000000000000000001",
            "65774c82fa8eb482c"
        ],
        "gl2_message_id": "01HHW2N23Y3PRJ87",
        "message": "YadaYadaYada",
        "Source_geolocation": "39.123,-85.3325",
        "Source_country_code": "US",
        "FirewallWANIP": "192.168.1.1",
        "LogMessage": "LOGS connected",
        "facility_num": 22,
        "Source_city_name": "Osgood",
        "facility": "local5"
    },
    "sort": [
        1702878728043,
        0
    ]
}
1 Like
7

I cant think of any other way of doing it other than using infinity plugin with UQL ( check this out → https://try.jsonata.org/IEAWJWerx)
) query language by hitting the ES endpoint directly at 9200 and use Orchestra city mapping plugin to map things.

image
image1102Ă—611 110 KB

8

I think this is the way, I will try it. Many thanks Yosiasz!

1 Like