Forum Discussion

majo1's avatar
majo1
Copper Contributor
Nov 28, 2019
Solved

Creating new field in logs based on existing one

Hello,   So as the logs are ingested in Azure Sentinel, i want to add a new key/value to the logs table based on a key that already exists in the logs.   For example, the new key is "Country", an...
data collection
CliveWatson's avatar
CliveWatson
Former Employee
Nov 28, 2019

majo1 

 

Log Analytics (and therefore Sentinel as it uses the same data store) processes raw data.
"...the Log Analytics service processes the raw data and ingests it into the database."

Source: section 3:
https://docs.microsoft.com/en-us/azure/azure-monitor/platform/data-security

 

So you have to add or amend the data before you send it, custom logs are one feature that may help. see https://docs.microsoft.com/en-us/azure/azure-monitor/platform/data-sources-custom-logs

 

---------------------------------------------------------------------

 

However after the data is in the data store you can enrich it (use the Extend option for example to add columns)  https://docs.microsoft.com/en-us/azure/kusto/query/extendoperator

There are other commands as well.

Example - here when we find country and I add Column for its country code:

WireData
| where isnotempty(RemoteIPCountry)
| extend CountryCode = case (
                            RemoteIPCountry == "United States", "US",
                            RemoteIPCountry == "United Kingdom", "UK",
                            strcat("No Country Code for"," : ", RemoteIPCountry)
                            )
| project RemoteIPCountry , CountryCode

Go to Log Analytics and Run Query

 

RemoteIPCountry CountryCode
United States US
Latvia No Country Code for : Latvia
Latvia No Country Code for : Latvia
United States US
Denmark No Country Code for : Denmark

 

 

You could bring that data in from another Table or even a remote file as well, see this more real example for adding country codes from a file online: https://cloudblogs.microsoft.com/industry-blog/en-gb/cross-industry/2019/08/13/azure-log-analytics-how-to-read-a-file/


  • majo1's avatar
    majo1
    Copper Contributor
    Nov 29, 2019

    CliveWatson 

    Thank you for the answer.

    Customizing logs or modifying them before sending to Azure doesn't seem to be a possible option.

    The Extend feature is close to my requirement.

    Question:

    After the Extend function is done, like the example you provided, is there a way to insert the Extended value back in logs in the database so that the Extended value permanently becomes part of the log?

    To clarify by example, after i populate the value of CountryCode = 'US', i want the other Analysts to find that value already in the logs whenever they perform queries to the affected table.

    The requirement can be probably described as Extend at the ingestion time, rather than query time.

     

    Thanks in advance

    • Ofer_Shezaf's avatar
      Ofer_Shezaf
      Icon for Microsoft rankMicrosoft
      Dec 01, 2019

      majo1 : 

       

      You would need to think differently with Sentinel. The need is not to create physical field, but rather the enable an analyst to access the field. Sentinel's query time parsing, which CliveWatson described enables this by using functions. A function encompasses the field extraction in a view that analysts can use without reinventing the field. 

       

      You can read more about how to use functions for this purpose here: https://techcommunity.microsoft.com/t5/Azure-Sentinel/Using-KQL-functions-to-speed-up-analysis-in-Azure-Sentinel/ba-p/712381

       

      ~ Ofer

    • CliveWatson's avatar
      CliveWatson
      Former Employee
      Nov 29, 2019
      Sorry but its the Extend at Query time option. Log Analytics storage is WORM (Write Once Read Many).

      So you will need to let the Analysts know that they need to use an Extend each time (save the work in a repeatable query and share that with them).

Resources