Steve Lange @ Work

Steve Lange's thoughts on application lifecycle management, Visual Studio, and Team Foundation Server

November, 2009

  • Steve Lange @ Work

    Querying the TFS Database to Check TFS Usage


    Why would you want to know how many users are actually using Team Foundation Server?  Well, for starters:

    • You want to make sure that each user in your environment using TFS is properly licensed with a TFS CAL (Client Access License). 
    • You want to show management just how popular TFS is in your environment.
    • You want to request additional hardware for TFS, and want to show current usage capacity.

    But, what if your users are spread out all over the world, so you can’t just send a simple email asking, “Hey, are you using TFS?”

    One relatively straightforward way is to ask your TFS server’s database.  TFS logs activity in a database ‘TfsActivityLogging’, specifically in a table ‘tbl_Command’.

    NOTE:  It’s not supported to go directly against the database, so take note of 2 things:

    1. Be very careful!
    2. Be clear that this isn’t supported.  This process works, but only in the absence of a supported way to query TFS usage.  Just because I work for Microsoft, doesn’t mean you can get official support from MS on this.

    All that out of the way, the simple way to do this is to use Excel:

    Open Excel.

    Go to the Data tab and select ‘From Other Sources’ in the ‘Get External Data’ group, and select ‘From SQL Server’.


    The Data Connection Wizard will open.  Follow steps to connect to the SQL Server that’s used by TFS, selecting the ‘TfsActivityLogging’ database and the contained ‘tbl_Command’ table.


    Enter the SQL Server name that TFS uses.  For the below, my SQL server is at ‘tfsrtm08’.


    Select the ‘TfsActivityLogging’ database, then select the ‘tbl_Command’ table. Click Next.


    Click Finish.

    Select how you’d like to import the table’s data.  For this example, I’m choosing ‘PivotTable Report’.


    Now you’re ready to get the data you want:

    Listing All Users Who Have Touched TFS

    In the ‘PivotTable Field List’ panel on the right, select the ‘IdentityName’ field.  Your spreadsheet should look something like this:


    If you just want a list of users that have touched TFS, then you’re done (in my example, I really only have 2 accounts, and one is the TFSSERVICE account that actually runs TFS).

    However, if you want a little extra information about your users’ activities, you can do a couple extra things.

    List Users and Their Relative Activity Levels

    Add the ‘ExecutionCount’ field to the ‘Values’ section of the PivotTable, and you’ll see the number of commands each user has run against TFS (some minor, like gets, and other major, like changing ACL’s):


    List Users and Their Specific Activity Levels

    Add first the ‘ExecutionCount’ field to the ‘Values’ section of the PivotTable, then add the ‘Command’ field to the ‘Row Labels’ section:


    (Again, remember that some of these commands are less significant than others, but still indicate user activity.)

    List Users and Their Clients

    Add the ‘UserAgent’ field to the ‘Row Labels’ section of the PivotTable:


    List Users and Their Last Activity Time

    Add ‘IdentityName’ to the ‘Row Labels’ section of the PivotTable and ‘StartTime’ to the ‘Values’ section.  Then click ‘Count of StartTime’ (in the Values section) and select ‘Value Field Settings’.  Change the ‘Summarize the value field by’ value to ‘Max’.


    Click ‘Number Format’ and set the format to ‘Date’.  Click OK.  You’ll now see the last activity date for each user.


    I hope this helps!

    Other Tip:

    • You’ll probably see (like in my example) the built-in accounts and their activities (i.e. TFSSERVICE, perhaps TFSBUILD as well).  You may want to filter those ones out from your report.
    • I’ve heard conflicting reports about how much data the ‘tbl_Commands’ table retains (some say just the preceding week).  In my example, I queried the ‘Min’ start times for logged activities and went back over 5 months.  Just something to think about:  Your mileage may vary greatly.  (Apparently a clean-up job is supposed to run periodically which trims this table.)
  • Steve Lange @ Work

    PDC 2009: Day 2 – silverlight, Free Laptops, and the Underground


    There were several exciting announcements today, but the one that topped the list (at least technology-wise) was the availability of Silverlight 4 Beta.  There’s a lot of new stuff going into Silverlight (I’ll let you read the full “What’s New”), but my favorites:

    • Webcam & microphone support
    • Full print support
    • HTML hosting
    • Full design surface in VS 2010

    While ScottGu wowed the crowd with these new capabilities, the loudest applause was bought.  Bought?  Yup, each PDC attendee was given a new multi-touch tablet laptop.  Wow!  (Imagine the line for that!)

    That evening, it was time for my regional DPE team to shine.  Many folks from all groups within my West Region had been working for months to put together the Underground at PDC – what has quickly become the definitive party for PDC.  The night didn’t disappoint, hosting over 1,200 people for a 6-hour party featuring repeat demos from PDC keynotes, a Bing search challenge, a technology showcase (I did lots of multi-touch demos with HP TouchSmarts) and lots of giveaways.  Oh, did I mention the actual party!?

    HP TouchSmarts - this was my post for the evening..

    HP TouchSmarts - this was my post for the evening..

     Joe Shirey & Erin Jacobs.  Two key parts of the night's sucess!

    Joe Shirey & Erin Jacobs.  Two key parts of the night's success!

     David Chou & Ned Curic.  Brilliant minds.

    David Chou & Ned Curic.  Brilliant minds.

     Is that THE Tim Huckaby?

    Is that THE Tim Huckaby?

     Do you think the PDC geeks were excited to see them arrive?

    Do you think the PDC geeks were excited to see them arrive?

     DSCN1130 DSCN1146

     DSCN1150 DSCN1154

    There were masses of tweets that night on Twitter, either mentioning @UndergroundPDC or hashtagging #UGPDC09.

    If you attended, thanks for coming and we hope you had a blast (it certainly seemed like you did!).  If you worked there that night, pat yourself on the back.  Everything ran incredibly well!

  • Steve Lange @ Work

    Visual Studio for Oracle Developers – “Project Fuze”


    The fine folks at Quest Software are currently in beta with “Project Fuze”, a DSP (Database Schema Provider) that brings Oracle database development into the fold of Visual Studio.  Like you can with SQL Server today, Fuze will give Oracle developers the full benefits that Visual Studio has to offer, such as:

    • Full change management for Oracle database schemas:  Each schema object is treated as its own individually-managed object in Team Foundation Server.
    • Generate test data:  Use data generation plans to create data for testing purposes.
    • Automated builds:  Build your databases just like your code.
    • Database refactoring
    • Schema comparison:  Compare and merge schema models.

    Interested?  You can download the beta HERE.

    Here’s a Project Fuze Demo:

  • Steve Lange @ Work

    Microsoft Acquires Teamprise Assets, Provides Cross-Platform Support for Visual Studio


    Today Microsoft announced (at Tech Ed Europe) the acquisition of Teamprise (press release), a fantastic technology partner that provides client applications for accessing Microsoft’s Visual Studio Team Foundation Server from Eclipse and Eclipse-based IDEs, and from other operating systems, including UNIX, Linux and Mac OS X.

    Teamprise Client Suite currently includes the following:

    • Teamprise Plug-in for Eclipse. The Teamprise Plug-in for Eclipse allows developers to perform all their source control, bug tracking, build and reporting operations from within Eclipse and Eclipse-based integrated development environments (IDEs), such as Rational Application Developer, JBoss, BEA Workshop and Adobe Flex Builder.
    • Teamprise Explorer. Teamprise Explorer combines all the functionality available to Eclipse developers using the Teamprise Plug-in into a stand-alone, cross-platform graphical user interface (GUI) application that is perfect for team members working outside of an IDE, such as graphic designers, quality assurance testers and project managers.
    • Teamprise Command-Line Client. The Teamprise Command-Line Client provides a cross-platform, nongraphical interface to Team Foundation Server, making it perfect for scripting and build scenarios or for developers who prefer a command-line interface.

    Look for a Microsoft-branded, Teamprise-based product sometime around the VS 2010 launch.

    Welcome, Teamprise!

  • Steve Lange @ Work

    PDC 2009: Day 0 – It’s All About Software Quality


    After my 2.5-hour flight from Denver (The entire Denver contingent of Microsoft Developer Platform & Evangelism was on my flight) to Los Angeles at the you-know-what crack of dawn this morning, I was excited to arrive at my first Microsoft Professional Developers Conference.  In my 4+ years at Microsoft, 2 of which were in Sothern California, it’s hard to believe that it took me this long to finally attend.  But I made it!image

    PDC doesn’t officially start until tomorrow, leaving today for “pre-cons” – workshops & team meetings.  For me, it was a deep-dive into the new QA/Testing tools coming out in VS 2010. 

    My highlight topics included:

    • Test Case Management
    • Manual Testing
    • Action Recording
      • Turning an action recording into a Coded UI Test
    • Lab Management
    • SCVMM, TCM, and other fun acronyms
    • Query-based test suites
    • Requirements-based test suites
    • Test Impact Analysis
    • Fast Forward Manual Testing

    It’s all goodness.  I’ll be honest – It’s a heck of a lot to learn as well, but these new capabilities are really going to hit home with a lot of people.  You just wait and see.  For those of your in Phoenix, Las Vegas, Salt Lake City and Denver – just let me know if you’d like a run through from me.

    That’s it for today.  I guarantee today will be the lightest day of the week.  Tues-Thurs will be providing keynotes, tons of geeky sessions, and some great evening events along the way (my team is hosting a dinner tomorrow night, and there’s of course the Underground at PDC event/party on Wednesday)!  I’ll also try to post some thoughts on Twitter as well (or follow all of the PDC tweets here).

  • Steve Lange @ Work

    Watch the Underground @PDC Live!


    LIVE from the The Underground @ PDC 09! People are still talking about last year’s event at the Edison downtown.  Because not everyone can make it to Los Angeles or PDC, The Underground is going LIVE ONLINE! Tune in to see Microsoft’s Scott Guthrie, Corp VP of Microsoft’s .Net Developer Platform and much, much more.

    Wednesday, November 18 at 7:00 PM

    LIVE at

    Twitter: @undergroundpdc

  • Steve Lange @ Work

    Day 1: PDC – Information Overload


    After hopping a shuttle bus from the hotel to the LA Convention Center, the official PDC “Day 1” started off with a flurry.  Ray Ozzie and Bob Muglia (along with several other demo helpers) kicked off the conference with several announcements.

    I won’t go in to full details, but the highlights:

    • Windows Azure is going into production on January 1st, and will start billing on February 1st.
      • There’s a current CTP of a connector which will help Azure applications to connect to databases behind the firewall.
    • Codename “Dallas”:  Exposing public data for purposes of innovation.

    Both can be read about in this eWeek article.

     Just 4,000 of my closest friends, waiting for the keynote to start.

    Waiting for the first keynote to start.

    Joe & Raj in a break area.

    Joe Shirey and Raj taking a break from the sessions.

     A sample container datacenter.

    A sample container from the Microsoft datacenters cropping up around the world.

    A henna tattoo.  It's the new MSDN logo.

    If you came by the VS 2010 area in the “Big Room”, you could get henna of some key logos & icons.  This one is of the new MSDN logo.

    I wrapped up my day with the US DPE customer dinner at The Palm restaurant downtown.  Great customers, friends, colleagues, and steaks!

    I had honestly hoped to write more about sessions and announcements, but it’s frankly too late in the evening.  That’s why you get the link to eWeek article, so you can read about it for yourself!

    More tomorrow!

Page 1 of 1 (7 items)