Valve today released a video showing off how the new Steam Controller will work with different types of games. In the video Valve shows off how games such as Portal 2, Counter-Strike: Global Offensive, Papers, Please; and Civilization V work with the new controller - including some games that use the legacy mode (games that have not been modified specifically to support the controller). Valve promises more of these videos in the future.
The controller is part of a new initiative by Valve to create a software and hardware environment for PC games on high definition televisions. Check out the video to your left. If you'd like to see them show a specific game using the controller, you can mention it in the comments here.