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
Advertisement
Answer
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.