KQL

KQL Fundamentals – Project

Level: Beginner | Reading time: 10 minutes

We are continuing our series on KQL with a focus on Cyber Security. So, tet’s talk today about how to use the Project command. Other posts can be seen in our KQL category.

If you want to project columns from a table to display them in the query results, you can use the Project. You can get the columns you want to include, rename, drop them, or insert new ones. With that, it’s easier to interpret the results, and read and organize the lines.

The syntax is:
T | project ColumnName [= Expression] [, …]

I am going to show you a basic example. If we want to list all the events from the CloudAppEvents (Microsoft Defender for Cloud Apps), I would have something like this:

Note that I have ample space to scroll the screen to the right with all the results from this table.

Let’s project only the columns we want to retrieve to have an optimized view.

CloudAppEvents
| project Timestamp, ActivityID = ReportId, ActionType, Application, AccountDisplayName, AccountObjectId, CountryCode, City, IPAddress, ISP

Here is another example, I am listing the DeviceFileEvents to list all files created during the last hour: 

DeviceFileEvents
| where Timestamp > ago(1h)
| limit 1000

However, it’s much more helpful if I list this with the columns FileName, FolderPath, SHA1, DeviceName, and TimeStamp.

DeviceFileEvents 
| where Timestamp > ago(1h) 
| project FileName, FolderPath, SHA1, DeviceName, Timestamp 
| limit 1000 

I can also use the project-away to exclude some columns from the result. In the example below, I still have column MD5 that I want to remove.

But after running the command, I can see now that the column is gone.

But if I want only to keep the column MD5 to project? I can easily use the project-keep.

If I want to rename the column in the result, I can use the project-rename operator, like in the example below, where I am keeping the MD5 in the results, but now I want to call it HashMD5.

And finally, to reorder, I can use the project-reorder operator.

Before:

After:

References

Project operator – Azure Data Explorer | Microsoft Docs

project-away operator – Azure Data Explorer | Microsoft Docs

project-keep operator – Azure Data Explorer | Microsoft Docs

project-rename operator – Azure Data Explorer | Microsoft Docs

project-reorder operator – Azure Data Explorer | Microsoft Docs

Tiago Souza

Tiago Souza

Security Technical Specialist
Cyber Security Technical Specialist at Microsoft | Cloud Security & Threat Protection | Blog content creator at CyberGeeks.Cloud - https://linktr.ee/tiagovf