Before Github absorved CodeQL the UI of the project was quite intuitive, you could see immediately where your project was failing the CodeQL test, i.e., which file, line of code and which type of warning/error.
Now the project is run by Github through a Github Action. Everything was running smoothly till the last commit which failed the CodeQL test. When I try to see the log of the failed action I can't see any useful information. I research and I just see long explanations and cumbersome advice on something that is supposed to be straight and simple: which file, line of code and warning/error is triggering the failure.
How do I know which file, line of code and type of error/warning is triggering the failure of CodeQL Github action?
The log of the failed action run linked in your question suggests the run might have been terminated due to high CPU usage (see this discussion and this issue). The actual CodeQL analysis (step "Perform CodeQL Analysis") was not performed, so that was not the reason why the run failed.
Where you see the code scanning alerts depends on which permissions you have for the repository and how you have configured CodeQL code scanning, see the GitHub documentation for more information. As shown in your answer, if you have the needed permissions you can see the alerts in the "Security" tab of the repository, but you have to choose the correct branch.