Forum Discussion

frostj02's avatar
frostj02
Copper Contributor
Mar 12, 2020

Group IPs in Log Analytics workspaces query

Im looking for the right query langue to group my IPs in my log analytics workspace into two categories, Internal and external but can't figure out how to group the ips in the query language.  Any he...
azure monitor
Query Language
frostj02's avatar
frostj02
Copper Contributor
Mar 12, 2020

CliveWatsonIt is part of  Azure Log Analytics in the signinlogs table.  I know my internal ips and external ips I just want to group them.  my report shows a count of signin's by ip's but I can't group the ips to make it a more relevant chart.  

CliveWatson's avatar
CliveWatson
Former Employee
Mar 12, 2020

frostj02 

 

You could do something like this?

 

SigninLogs
| where TimeGenerated > ago(24h)
| extend local = case(parse_ipv4(IPAddress) between ( parse_ipv4("67.0.0.0")  .. parse_ipv4("67.255.255.255")  ),"Local",  
                      parse_ipv4(IPAddress) between ( parse_ipv4("74.0.0.0")  .. parse_ipv4("74.255.255.255")  ),"Local",   
                      parse_ipv4(IPAddress) between ( parse_ipv4("100.0.0.0") .. parse_ipv4("109.255.255.255") ),"Local",
                      //else 
                      "Remote"  )
| summarize count(), make_set(IPAddress) by local
| order by local asc

 

You can set a range between IP address - line 1 is 67* to 67*, the same for 74*, the final line is 100-109*

Anything outside of the local ones are 'remote'. 

 

Or you can swap the names to "Local" and "Remote" and the //else to "Other"

 

Resources