SQL query to classify and count logged messages
Guessing:


  What do you think is the most CPU intensive part of that query, and
  what are the possible ways to consume less CPU?


We don't have to guess, you test it using SSMS and execution plans.

Capacity:


  The query is very CPU-heavy for the database server (according to the
  stats provided by Azure SQL Server), and eats 15% of the available CPU
  capacity of the server.


15% of 0 is still 0, how many CPUs do you have for this instance, if you only have 1 CPU 15% could be considered significant, however the issue is most likely not this query, it is the ration of CPU you've given it. 

Fundamentals:

When you have a table and you are TRYING to use a surrogate key as a primary key, you still have to have a primary key on the object for it to be considered a table. You are currently querying a heap.

Please start by adding a primary key to the table, identity(1,1) is not a primary key, it is a candidate for a primary key, you still have to tell SQL you want it to be the primary key.

Predicate:

How large is this table, it could be the case that you have 4 billion records in the table and you're asking this question with out a filter on date range. Do you intent for this to be a question for all time?  Is there a process that truncates this table, or starts the process over in some fashion?

Indexes:

Indexes, I would create a non cluster index on intent, you could even try a filtered index on the column. 

Performance tuning

You will want to compare the execution plans, find the equivalent of dropcleanbuffers and freeproccache for Azure. Subtreecost is what you're looking to improve, time it takes to return is a non-sequitur. You might need to provide table hints to force it to use the indexes to validate if the index is useful or not.

Tempdb:

What does your tempdb look like, I only ask because of the multiple DISTINCT you use in the queries. If there is a lot of data in the table its dumping to tempdb and disk, what does the I/O look like for tempdb, what does the space look like? 

Code Changes

I would also consider changing the code @Malachi offers sound suggestion. I would also consider SQL Server Window Functions.