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 help? 

azure monitor
Query Language

3 Replies

  • CliveWatson's avatar
    CliveWatson
    Former Employee
    Mar 12, 2020
    Can you give us a clue as to the Table they are in, AzureActivity, WireData etc...

    Are internal 10.10.10.10 for example, compared to 1.1.1.1? Or are you looking to see which are outbound to inbound?
    • 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