SOLVED
Home

Creating new field in logs based on existing one

%3CLINGO-SUB%20id%3D%22lingo-sub-1038448%22%20slang%3D%22en-US%22%3ECreating%20new%20field%20in%20logs%20based%20on%20existing%20one%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1038448%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20as%20the%20logs%20are%20ingested%20in%20Azure%20Sentinel%2C%20i%20want%20to%20add%20a%20new%20key%2Fvalue%20to%20the%20logs%20table%20based%20on%20a%20key%20that%20already%20exists%20in%20the%20logs.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%2C%20the%20new%20key%20is%20%22Country%22%2C%20and%20if%20the%20Tenant-ID%20value%20existing%20in%20the%20logs%20is%20XYZ%20then%20the%20country%20should%20be%20added%20%22United%20Stated%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20i%20add%20such%20new%20key%20and%20value%20to%20Azure%20Sentinel%20Schemas%20%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20other%20SIEM%20Solution%2C%20this%20is%20achieved%20by%20using%20Feeds.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1038448%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EIngestion%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESentinel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1038866%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20new%20field%20in%20logs%20based%20on%20existing%20one%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1038866%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F469462%22%20target%3D%22_blank%22%3E%40majo1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELog%20Analytics%20(and%20therefore%20Sentinel%20as%20it%20uses%20the%20same%20data%20store)%20processes%20raw%20data.%3CBR%20%2F%3E%22%3CSPAN%3E...the%20Log%20Analytics%20service%20processes%20the%20raw%20data%20and%20ingests%20it%20into%20the%20database.%3C%2FSPAN%3E%22%3C%2FP%3E%0A%3CP%3ESource%3A%20section%203%3A%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-monitor%2Fplatform%2Fdata-security%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-monitor%2Fplatform%2Fdata-security%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESo%20you%20have%20to%20add%20or%20amend%20the%20data%20%3CSTRONG%3Ebefore%3C%2FSTRONG%3E%20you%20send%20it%2C%20custom%20logs%20are%20one%20feature%20that%20may%20help.%20see%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-monitor%2Fplatform%2Fdata-sources-custom-logs%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-monitor%2Fplatform%2Fdata-sources-custom-logs%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E---------------------------------------------------------------------%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHowever%20after%20the%20data%20is%20in%20the%20data%20store%20you%20can%20enrich%20it%20(use%20the%20%3CSTRONG%3EExtend%3C%2FSTRONG%3E%20option%20for%20example%20to%20add%20columns)%26nbsp%3B%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fkusto%2Fquery%2Fextendoperator%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fkusto%2Fquery%2Fextendoperator%3C%2FA%3E%3C%2FP%3E%0A%3CP%3EThere%20are%20other%20commands%20as%20well.%3CBR%20%2F%3E%3CBR%20%2F%3EExample%20-%20here%20when%20we%20find%20country%20and%20I%20add%20Column%20for%20its%20country%20code%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EWireData%0A%7C%20where%20isnotempty(RemoteIPCountry)%0A%7C%20extend%20CountryCode%20%3D%20case%20(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20RemoteIPCountry%20%3D%3D%20%22United%20States%22%2C%20%22US%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%20%20%20%20%20%20RemoteIPCountry%20%3D%3D%20%22United%20Kingdom%22%2C%20%22UK%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%20%20%20%20%20%20strcat(%22No%20Country%20Code%20for%22%2C%22%20%3A%20%22%2C%20RemoteIPCountry)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20)%0A%7C%20project%20RemoteIPCountry%20%2C%20CountryCode%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fms.portal.azure.com%23%4072f988bf-86f1-41af-91ab-2d7cd011db47%2Fblade%2FMicrosoft_Azure_Monitoring_Logs%2FDemoLogsBlade%2FresourceId%2F%252FDemo%2Fsource%2FLogsBlade.AnalyticsShareLinkToQuery%2Fq%2FH4sIAAAAAAAAA52QQQrCMBBF94Xe4ZNVhZxA6KpupCDSIq5DM2qEZkoyogUPby0KYqGCsxo%25252Bw2Pe37tAKyMmTe64nigQXPQs1HbSZxW1w7reFnzxEvrF84huQt7iFRVsCTkaEwlZmmBmvmDIc6idd0IWtRihqPQQ1Er%25252FjSmdP1puR075ixMlNEYyteG3C0aZAwelFZYYMFP%25252FOeJYTxf4TI1M3tSfjaXJA%25252BSXIqR3AQAA%2Ftimespan%2F2019-09-01T17%253A50%253A45.000Z%252F2019-11-22T17%253A50%253A45.662Z%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EGo%20to%20Log%20Analytics%20and%20Run%20Query%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%0A%3CTABLE%20cellspacing%3D%221%22%20cellpadding%3D%225%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTH%3ERemoteIPCountry%3C%2FTH%3E%0A%3CTH%3ECountryCode%3C%2FTH%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EUnited%20States%3C%2FTD%3E%0A%3CTD%3EUS%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3ELatvia%3C%2FTD%3E%0A%3CTD%3ENo%20Country%20Code%20for%20%3A%20Latvia%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3ELatvia%3C%2FTD%3E%0A%3CTD%3ENo%20Country%20Code%20for%20%3A%20Latvia%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EUnited%20States%3C%2FTD%3E%0A%3CTD%3EUS%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3EDenmark%3C%2FTD%3E%0A%3CTD%3ENo%20Country%20Code%20for%20%3A%20Denmark%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20could%20bring%20that%20data%20in%20from%20another%20Table%20or%20even%20a%20remote%20file%20as%20well%2C%20see%20this%20more%20real%20example%20for%20adding%20country%20codes%20from%20a%20file%20online%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fcloudblogs.microsoft.com%2Findustry-blog%2Fen-gb%2Fcross-industry%2F2019%2F08%2F13%2Fazure-log-analytics-how-to-read-a-file%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fcloudblogs.microsoft.com%2Findustry-blog%2Fen-gb%2Fcross-industry%2F2019%2F08%2F13%2Fazure-log-analytics-how-to-read-a-file%2F%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1039370%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20new%20field%20in%20logs%20based%20on%20existing%20one%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1039370%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%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20the%20answer.%3C%2FP%3E%3CP%3ECustomizing%20logs%20or%20modifying%20them%20before%20sending%20to%20Azure%20doesn't%20seem%20to%20be%20a%20possible%20option.%3C%2FP%3E%3CP%3EThe%20Extend%20feature%20is%20close%20to%20my%20requirement.%3C%2FP%3E%3CP%3EQuestion%3A%3C%2FP%3E%3CP%3EAfter%20the%20Extend%20function%20is%20done%2C%20like%20the%20example%20you%20provided%2C%20is%20there%20a%20way%20to%20insert%20the%20Extended%20value%20back%20in%20logs%20in%20the%20database%20so%20that%20the%20Extended%20value%20permanently%20becomes%20part%20of%20the%20log%3F%3C%2FP%3E%3CP%3ETo%20clarify%20by%20example%2C%20after%20i%20populate%20the%20value%20of%20CountryCode%20%3D%20'US'%2C%20i%20want%20the%20other%20Analysts%20to%20find%20that%20value%20already%20in%20the%20logs%20whenever%20they%20perform%20queries%20to%20the%20affected%20table.%3C%2FP%3E%3CP%3EThe%20requirement%20can%20be%20probably%20described%20as%20Extend%20at%20the%20ingestion%20time%2C%20rather%20than%20query%20time.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1039657%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20new%20field%20in%20logs%20based%20on%20existing%20one%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1039657%22%20slang%3D%22en-US%22%3ESorry%20but%20its%20the%20Extend%20at%20Query%20time%20option.%20Log%20Analytics%20storage%20is%20WORM%20(Write%20Once%20Read%20Many).%20%3CBR%20%2F%3E%3CBR%20%2F%3ESo%20you%20will%20need%20to%20let%20the%20Analysts%20know%20that%20they%20need%20to%20use%20an%20Extend%20each%20time%20(save%20the%20work%20in%20a%20repeatable%20query%20and%20share%20that%20with%20them).%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1041161%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20new%20field%20in%20logs%20based%20on%20existing%20one%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1041161%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F469462%22%20target%3D%22_blank%22%3E%40majo1%3C%2FA%3E%26nbsp%3B%3A%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20would%20need%20to%20think%20differently%20with%20Sentinel.%20The%20need%20is%20not%20to%20create%20physical%20field%2C%20but%20rather%20the%20enable%20an%20analyst%20to%20access%20the%20field.%20Sentinel's%20query%20time%20parsing%2C%20which%26nbsp%3B%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%3E%26nbsp%3Bdescribed%20enables%20this%20by%20using%20functions.%20A%20function%20encompasses%20the%20field%20extraction%20in%20a%20view%20that%20analysts%20can%20use%20without%20reinventing%20the%20field.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20read%20more%20about%20how%20to%20use%20functions%20for%20this%20purpose%20here%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FAzure-Sentinel%2FUsing-KQL-functions-to-speed-up-analysis-in-Azure-Sentinel%2Fba-p%2F712381%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FAzure-Sentinel%2FUsing-KQL-functions-to-speed-up-analysis-in-Azure-Sentinel%2Fba-p%2F712381%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E~%20Ofer%3C%2FP%3E%3C%2FLINGO-BODY%3E
majo1
New Contributor

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", and if the Tenant-ID value existing in the logs is XYZ then the country should be added "United Stated".

 

How can i add such new key and value to Azure Sentinel Schemas ?

 

In other SIEM Solution, this is achieved by using Feeds.

 

Thanks.

4 Replies
Solution

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


@Clive Watson 

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

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

@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 @Clive Watson 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-Az...

 

~ Ofer

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies