SQL query, counting IP-Adress (Grafana, DMARC)

Lveriq 0
Viewed 26

Maybe this question exists, but I couldt find it so you can also just link me to a similar question.

My problem: I have a database and wand to visualize it with Grafana. In Grafana you have the option to create a query in SQL notation. In my case I want to count the similar IP addresses I have in my DB and also order them desc. The only problem is that I have IP addresses with different host-parts, which i want to ignore, like: 183.34.111.188; 183.34.111.172; 183.34.111.42 should be counted as three times "183.34.111".

You guys have any Idea how I can solve this? This is the SQL (standard) Code in Grafana to count:

SELECT 
  $__unixEpochGroupAlias(begin,$__interval)
  source_ip AS metric,
  count(count) AS "count"
FROM DMARC
GROUP BY 1, source_ip,2
ORDER BY $__unixEpochGroup(begin,$__interval)

Thank you

dmarc ip-address ip sql
2 Answers

you can use Substring to cut the strings after the numbers.

Select
    substring(source_ip,1,10) as IP
FROM DMARC
Stigi0 
SELECT 
case 
    when source_ip like '%.%' THEN CONCAT(SUBSTRING_INDEX(SUBSTRING_INDEX(source_ip, '.',3), ' ', -1),".0/24") 

when source_ip like '%%' THEN CONCAT(SUBSTRING_INDEX(SUBSTRING_INDEX(source_ip, '.',3), ' ', -1),".0/24") 

    when source_ip like '%:%' THEN SUBSTRING_INDEX(SUBSTRING_INDEX(source_ip, ':',4), ' ', -1)
ELSE source_ip end as source_ip_MOD
FROM DMARC

This is the Answer of my question, you can remove the last part with ".0/24" if you want. It only makes sense on IPv4 addresses, because you look at a IP-Address Range. IDK how you Group a Range for IPv6.

Lveriq0
Related