Extract an IP address out of a string in Log Analytics/Azure Sentinel
In a lot of cases Azure Sentinel and Azure Monitor are reporting IP addresses in a separate column. This makes it quite easy to work with them. You can easily use them in a summarize
statement or use them in a join.
These kind operations are getting a lot more complex when the IP address is used in a string with other text around them. A common example where you can find this is the SSH log:
Oct 31 05:46:35 MyAwesomeComputer sshd[21284]: Failed password for invalid user nelson from 149.202.161.57 port 39567 ssh2
It is possible to extract the IP address out of the above string by using the extract_all
function in Log Analytics. This function will use a regular expression to extract the IP address out of the string. You can use this function to extract an IP Address out of a string:
extract_all(@"((?:[0-9]{1,3}\.){3}[0-9]{1,3})", RawData)
The result of the above function is an array. It might be that multiple IP addresses have been defined in the text on which the extract_all function is applied. Each of the extracted IP addresses will be an entry in this array.
By using this function in combination with an extend statement, you can add a new column to your results with the IP address in it.
SSHLogs
| extend IpAddress = extract_all(@"((?:[0-9]{1,3}\.){3}[0-9]{1,3})", RawData)[0]
Please note that in the above example [0]
is used at the end. The [0]
will make sure the first entry of the array is picked. If you work with data where multiple IP addresses are used in a single string, using [0]
might be a bad idea as only the first IP address is displayed in the IP address colum of you result set.
Pro tip: You could use the
tostring()
function to make your data non-dynamic. By doing so, you can easily apply summarize and orderby statements on your data:
SshLog
| extend IpAddress = tostring(extract_all(@"((?:[0-9]{1,3}\.){3}[0-9]{1,3})", RawData)[0])
| summarize count() by IpAddress
| order by count_ desc
Conclusion
The extract_all
function can be used to extract an IP address out of a string.You can use this function in combination with an extend statement to add an ‘IpAddress’ column to your resultset.
Please have a look at the following links for more information:
- Azure Log Analytics extract_all function
- Azure Log Analytics extend function
- IP Address regular expressions
I hope this story will make your life as an Azure Sentinel analyst or Azure Monitor operator easier. Thanks for reading!
— Jeroen Niesen