Extracting specific fields from the MMDB database

Docs Database Downloads
,
1

MMDB binary file format allows for efficient lookup, but it doesn’t provide the most versatility

If you are using an MMDB reader library like:

You can extract the specific fields by extracting the specific fields from the response object quite easily.

However, if you are using the mmdbctl tool, you might want to complement that tool with:

  • JQ for JSON
  • Miller or the cut command or awk programming language for the CSV file

Extracting specific fields from the JSON output

For this post, we are going to use our IP to Geolocation database and extract the following fields from the database:

  • city
  • country
  • region
  • postal_code
  • latitude
  • longitude

Single input IP address: 128.143.220.179

Querying the IP location using the MMDBctl tool of a single IP address:

mmdbctl read -f json-pretty  ipinfo_location.mmdb

Response:

{
  "city": "Charlottesville",
  "country": "US",
  "latitude": "38.02931",
  "longitude": "-78.47668",
  "postal_code": "22902",
  "region": "Virginia",
  "timezone": "America/New_York"
}

We can extract the specific fields using jq. In that case, we have to declare the fields we want to extract in the object template declaration.

mmdbctl read -f json-pretty 128.143.220.179 ipinfo_location.mmdb | jq '{"city": .city, "country": .country, "region": .region, "postal_code": .postal_code, "latitude": .latitude, "longitude": .longitude}'

Response:

{
  "city": "Charlottesville",
  "country": "US",
  "region": "Virginia",
  "postal_code": "22902",
  "latitude": "38.02931",
  "longitude": "-78.47668"
}

Bulk IP address lookup: 128.143.220.179/30

The above code using JQ works precisely the same, even with bulk IP address lookup:

 mmdbctl read -f json-pretty 128.143.220.179/30 ipinfo_location.mmdb | jq '{"city": .city, "country": .country, "region": .region, "postal_code": .postal_code, "latitude": .latitude, "longitude": .longitude}'

Response:

{
  "city": "Charlottesville",
  "country": "US",
  "region": "Virginia",
  "postal_code": "22902",
  "latitude": "38.02931",
  "longitude": "-78.47668"
}
{
  "city": "Charlottesville",
  "country": "US",
  "region": "Virginia",
  "postal_code": "22902",
  "latitude": "38.02931",
  "longitude": "-78.47668"
}
{
  "city": "Charlottesville",
  "country": "US",
  "region": "Virginia",
  "postal_code": "22902",
  "latitude": "38.02931",
  "longitude": "-78.47668"
}
{
  "city": "Charlottesville",
  "country": "US",
  "region": "Virginia",
  "postal_code": "22902",
  "latitude": "38.02931",
  "longitude": "-78.47668"
}

Bulk IP address lookup: 133.254.182.207 197.38.153.13

mmdbctl read -f json-pretty 133.254.182.207 197.38.153.13 ipinfo_location.mmdb | jq '{"city": .city, "country": .country, "region": .region, "postal_code": .postal_code, "latitude": .latitude, "longitude": .longitude}'

Response

{
  "city": "Tokyo",
  "country": "JP",
  "region": "Tokyo",
  "postal_code": "101-0047",
  "latitude": "35.691",
  "longitude": "139.7679"
}
{
  "city": "Girga",
  "country": "EG",
  "region": "Sohag",
  "postal_code": "",
  "latitude": "26.33721",
  "longitude": "31.89295"
}

Extracting specific fields from the CSV output

Single IP address lookup: 128.143.220.179

You can use the AWK programming language to extract the columns:

mmdbctl read -f csv 128.143.220.179/30 ipinfo_location.mmdb | awk -F',' -v OFS=, '{print $2,$3,$4,$5,$6}'

The $2,$3,$4,.... refers to column number:

  • ip → $1
  • city → $2
  • country → $3
  • latitude → $4
  • longitude → $5
  • postal_code → $6
  • region → $7
  • timezone → $8

Response:

city,country,latitude,longitude,postal_code
Charlottesville,US,38.02931,-78.47668,22902
Charlottesville,US,38.02931,-78.47668,22902
Charlottesville,US,38.02931,-78.47668,22902
Charlottesville,US,38.02931,-78.47668,22902

You can also use the CSV utility Miller which provides a more intuitive way to extract the columns

mmdbctl read -f csv 4.85.5.133 126.233.162.255 ipinfo_location.mmdb | mlr --csv cut -f city,country,latitude,longitude,postal_code

city,country,latitude,longitude,postal_code
Monroe,US,32.553,-92.0422,71203
Tokyo,JP,35.6895,139.69171,101-8656

Going beyond MMDB…

Although the MMDB database provides an efficient way to look up IP addresses, however considering their limited scope and RAM usage, you might want to look into other solutions to enrich IP addresses with IPinfo’s databases. You should definitely check out our community post on: