Group IPs in Log Analytics workspaces query

%3CLINGO-SUB%20id%3D%22lingo-sub-1225503%22%20slang%3D%22en-US%22%3EGroup%20IPs%20in%20Log%20Analytics%20workspaces%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1225503%22%20slang%3D%22en-US%22%3E%3CP%3EIm%20looking%20for%20the%20right%20query%20langue%20to%20group%20my%20IPs%20in%20my%20log%20analytics%20workspace%20into%20two%20categories%2C%20Internal%20and%20external%20but%20can't%20figure%20out%20how%20to%20group%20the%20ips%20in%20the%20query%20language.%26nbsp%3B%20Any%20help%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1225503%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EQuery%20Language%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1225534%22%20slang%3D%22en-US%22%3ERe%3A%20Group%20IPs%20in%20Log%20Analytics%20workspaces%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1225534%22%20slang%3D%22en-US%22%3ECan%20you%20give%20us%20a%20clue%20as%20to%20the%20Table%20they%20are%20in%2C%20AzureActivity%2C%20WireData%20etc...%3CBR%20%2F%3E%3CBR%20%2F%3EAre%20internal%2010.10.10.10%20for%20example%2C%20compared%20to%201.1.1.1%3F%20Or%20are%20you%20looking%20to%20see%20which%20are%20outbound%20to%20inbound%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1225652%22%20slang%3D%22en-US%22%3ERe%3A%20Group%20IPs%20in%20Log%20Analytics%20workspaces%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1225652%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239477%22%20target%3D%22_blank%22%3E%40Clive%20Watson%3C%2FA%3EIt%20is%20part%20of%26nbsp%3B%20Azure%20Log%20Analytics%20in%20the%20signinlogs%20table.%26nbsp%3B%20I%20know%20my%20internal%20ips%20and%20external%20ips%20I%20just%20want%20to%20group%20them.%26nbsp%3B%20my%20report%20shows%20a%20count%20of%20signin's%20by%20ip's%20but%20I%20can't%20group%20the%20ips%20to%20make%20it%20a%20more%20relevant%20chart.%20%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1225727%22%20slang%3D%22en-US%22%3ERe%3A%20Group%20IPs%20in%20Log%20Analytics%20workspaces%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1225727%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F580483%22%20target%3D%22_blank%22%3E%40frostj02%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20could%20do%20something%20like%20this%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3ESigninLogs%0A%7C%20where%20TimeGenerated%20%26gt%3B%20ago(24h)%0A%7C%20extend%20local%20%3D%20case(parse_ipv4(IPAddress)%20between%20(%20parse_ipv4(%2267.0.0.0%22)%20%20..%20parse_ipv4(%2267.255.255.255%22)%20%20)%2C%22Local%22%2C%20%20%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20parse_ipv4(IPAddress)%20between%20(%20parse_ipv4(%2274.0.0.0%22)%20%20..%20parse_ipv4(%2274.255.255.255%22)%20%20)%2C%22Local%22%2C%20%20%20%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20parse_ipv4(IPAddress)%20between%20(%20parse_ipv4(%22100.0.0.0%22)%20..%20parse_ipv4(%22109.255.255.255%22)%20)%2C%22Local%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%2F%2Felse%20%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%22Remote%22%20%20)%0A%7C%20summarize%20count()%2C%20make_set(IPAddress)%20by%20local%0A%7C%20order%20by%20local%20asc%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20set%20a%20range%20between%20IP%20address%20-%20line%201%20is%2067*%20to%2067*%2C%20the%20same%20for%2074*%2C%20the%20final%20line%20is%20100-109*%3C%2FP%3E%0A%3CP%3EAnything%20outside%20of%20the%20local%20ones%20are%20'remote'.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOr%20you%20can%20swap%20the%20names%20to%20%22Local%22%20and%20%22Remote%22%20and%20the%20%2F%2Felse%20to%20%22Other%22%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Annotation%202020-03-12%20201350.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F176648i5004BED306F3B0AA%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Annotation%202020-03-12%20201350.png%22%20alt%3D%22Annotation%202020-03-12%20201350.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3C%2FDIV%3E%3C%2FLINGO-BODY%3E
New Contributor

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? 

3 Replies
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?

@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.  

@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"

 

Annotation 2020-03-12 201350.png