© 2021 www.richardwalz.com
Richard Walz
All rights reserved.

How to Parse Custom Logs from LogAnalytics/Sentinel into their own fields.

This how to is a continuation of this post. You may need to reference back to it at times. How to ingest Custom Logs into Log Analytics/Sentinel using DCR-Based rules

Step 1:
Make sure you understand your raw data which in my case is the logs being generated at C:\WebApp\*.log . Below is a sample list of logs.

#Software: App Log Custom
#Version: 1.0
#Date: 2024-06-21 17:00:12
#Fields: date time iisSiteName s-computername s-ip Other1 Other2 Other3 Other4 Other5
2024-06-21 16:59:39 W3SVC3 MBX19S01 172.88.147.102 88 99 100 55 44
2024-06-21 16:59:40 W3SVC3 MBX19S01 172.88.147.102 88 99 100 55 44
2024-06-21 16:59:41 W3SVC3 MBX19S01 172.88.147.102 88 99 100 55 44 
2024-06-21 16:59:41 W3SVC3 MBX19S01 127.0.0.1 GET  88 99 100 55 44
2024-06-21 16:59:39 W3SVC3 MBX19S01 172.88.147.102 88 99 100 55 44
2024-06-21 16:59:41 W3SVC3 MBX19S01 172.88.147.102 88 99 100 55 44 
2024-06-21 16:59:41 W3SVC3 MBX19S01 172.88.147.102 88 99 100 55 44 

Step 2:
Go to the Log Analytics/Sentinel workspace Log section and run a query that just includes the Table name like below and make sure the RawData field contains the log data.

Step 3:
I know that these fields are separated by a space ” ” character. By using KQL I am going to split the RawData field on the space.

MyCustomTableName_CL
| extend DataFields  = split(RawData, ' ')

Step 4:
Now I am going to add a few more fields based on data and what those expected headers should be called. I know also that any fields that are blank have a dash in them.

Use todatetime if field needs to be placed in date. Most other things can be set to tostring. For a full list of supported types view this article.

MyCustomTableName_CL
| extend DataFields  = split(RawData, ' ')
| extend dateUTC  = todatetime(DataFields[0])
| extend timeUTC  = tostring(DataFields[1])
| extend LogFileName  = tostring(DataFields[2])
| extend sComputerName  = tostring(DataFields[3])
| extend sIP  = tostring(DataFields[4])
| extend Other1  = tostring(DataFields[5])
| extend Other2  = tostring(DataFields[6])
| extend Other3  = tostring(DataFields[7])
| extend Other4  = tostring(DataFields[8])
| extend Other4  = tostring(DataFields[9])

Step 5:
If you scroll to the right you will notice we now have duplicate data in a sense. We have the fields and we also have “RawData” still. So we will project-away this field in the DCR transform rule down the line.

Step 6:
Once satisfied and you have all the columns accounted for and the match the headers correctly for your application. Convert your query into a single line. The below is a single line, even though it may not look like.

MyCustomTableName_CL | extend DataFields  = split(RawData, ' ') | extend dateUTC  = todatetime(DataFields[0]) | extend timeUTC  = tostring(DataFields[1]) | extend LogFileName  = tostring(DataFields[2]) | extend sComputerName  = tostring(DataFields[3]) | extend sIP  = tostring(DataFields[4]) | extend Other1  = tostring(DataFields[5]) | extend Other2  = tostring(DataFields[6]) | extend Other3  = tostring(DataFields[7]) | extend Other4  = tostring(DataFields[8]) | extend Other4  = tostring(DataFields[9])

Replace MyCustomTableName_CL with source, as shown below. Make sure you save this query as you may not be able to access it from the Log Analytics User Interface.

source | extend DataFields  = split(RawData, ' ') | extend dateUTC  = todatetime(DataFields[0]) | extend timeUTC  = tostring(DataFields[1]) | extend LogFileName  = tostring(DataFields[2]) | extend sComputerName  = tostring(DataFields[3]) | extend sIP  = tostring(DataFields[4]) | extend Other1  = tostring(DataFields[5]) | extend Other2  = tostring(DataFields[6]) | extend Other3  = tostring(DataFields[7]) | extend Other4  = tostring(DataFields[8]) | extend Other4  = tostring(DataFields[9])

Step 7:
Go back to the DCR Rule you created earlier and edit it. Copy this single line and put it in the “Transform” field. Then save your changes.

Step 8:
Go to your Log Analytics Table in my case called “MyCustomTableName_CL” and we need to add the same columns to the table. Right click on the table name and select “Edit Schema“.

On the bottom click “Add a Column” add each column with the respective type. Then click Save.

Step 9:
Wait around 20-30 minutes (sometimes it can take up to 60mins). It will take time for the Columns to be generated and for the DCR rule to update as well with the new configuration. Go to the Log Analytics/Sentinel workspace Log section and run a query that just includes the Table name like below.

If everything worked, then you should just see the columns defined.

Step 10:
Remove RawData/DataFields from the transform rule. By adding the following to the end of the line.

| project-away RawData,DataFields
source | extend DataFields  = split(RawData, ' ') | extend dateUTC  = todatetime(DataFields[0]) | extend timeUTC  = tostring(DataFields[1]) | extend LogFileName  = tostring(DataFields[2]) | extend sComputerName  = tostring(DataFields[3]) | extend sIP  = tostring(DataFields[4]) | extend Other1  = tostring(DataFields[5]) | extend Other2  = tostring(DataFields[6]) | extend Other3  = tostring(DataFields[7]) | extend Other4  = tostring(DataFields[8]) | extend Other4  = tostring(DataFields[9]) | project-away RawData,DataFields

Step 11:
The RawData and DataFields columns will now be dropped. This will ensure you are not duplicating data. It can take up to 60mins for this to take effect on newly ingested data.