Kirx' Blog - kirxblog.wordpress.com

Server-free Reporting with App-V 5 (Part 3): Finally some Reports! | May 22, 2014


Document_ReportingUse SQL Reporting Services is the common answer to the question of how to present all the application launch data that App-V’s SQL based reporting collected. In this article you’ll not only see how to use just Excel for creating graphical reports, but you also see that you can use the pure XML based information from the previous parts of this mini-series to feed that.

 

So, you have nicely set up your full-blown App-V Native infrastructure including the Reporting Server and Management Console, but didn’t find any way to view your collected data. Probably you are smart enough to do some research in the internet, so you know that you can use Microsoft’s SQL Reporting Services to create some nice charts (an upcoming post will point you to some resources). When you think further you surely come to the conclusion that other solutions (like good old Crystal Reports, which was acquired by Seagate, then Business Objects quite a while ago that for itself now is a major division of SAP). I might have the wrong peers, but when I say to IT Pros ‘Hey, then you just use SQL Reporting to build a nice representation of your data!’ (not only for App-V, but much for custom reports in Citrix EdgeSight), the common reactions are Uhs and Ohs and Hums and the question: Do you know somebody who can help us? And, well, no. I can’t.

Getting the some Data

The first thing you need is of course some usage or reporting data. In the first part of this series you could read how to set up the App-V Client to upload usage data in semi-XML format to nothing more than a simple file share, which actually inspired that article’s title, as you do not need an App-V specific component called App-V Reporting Server nor do you need a SQL database for that.
When you have a productive environment or even a pilot, it should be no problem to collect a bunch of data for reporting – but what should I do: show you reports about some 5 users and clients, generated by me and some demo accounts? Luckily I knew that Project Virtual Reality Check was analyzing the performance of App-V 5 that time and I think this is a project you also should know about:

ProjectVRC started a while back ago to answer the question whether running Terminal Server workloads on virtual machines was a good or at least acceptable approach. The folks from PQR and Login Consultants, who actually established VRC, acknowledged that the user experience or user acceptance is the most critical measurement to answer that 2-V-or-Not question. So VRC’s approach is to connect more and more users to the environment, each running a predefined (but partially randomized) workload. Sooner or later the application’s response time get so bad that natural users would complain – the machine gets saturated. This is the point-of-interest. ProjectVRC then performed a lot of other tests and extended their scope to VDI workloads, too. Besides common comparisons (like vSphere vs. XenServer vs. Hyper-V) they also inspect sorrounding aspects, like the impact of AntiVirus or Office versions – and guess what, of course they did compare the impact of different application virtualization solutions a while back already. Well, after the release of App-V 5 there was a lot of conversation and discussion about the perceived performance impact that App-V 5 has (compared to App-V 4 and native installations). So ProjectVRC started a lot of performance inspection runs around App-V 5.

While writing this article, the VRC Whitepaper has not yet been released, but you should follow Ryan Bijkerk and Ment van der Plas, presenting their results in a session at TechEd NorthAmerica 2014.
So, as Ryan was the guy who actually prepared and run most of the test-cycles, I asked him if it would be possible to add one ‘off-the-records’ cycle, just to get some usage data for the App-V book’s client reporting section and also for this article. I knew that it wouldn’t be a big issue for him, as he of course knows the technology behind ProjectVRC very good and he also knows App-V for a long time (you should check his blog, namely a later article about measuring the App-V 5 publishing time).
Ryan prepared the VRC environment to create XML based usage date by adjusting some VRC configurations (esp. to send the reporting data to a file share after the test cycles finished). He sent me over a set of about XML 300 files, generated on 120 VDI client machines.
Time to say

                Thank you Ryan and the VRC team.

 

Back to this series’ topic about collecting, preparing and presenting data:

The fact that all ProjectVRC’s usage data were actually generated during a single hour inspired me to the flexible ‘grouping’ function that we talked about in Part 2 and that significant amount of individual XML files gave me the confidence about the static character of the binary data at the end of the XML files and about that there may be several files per client.
So, as a starting point I assume that you have one single XML file that contains all relevant applications records, whereas each application record element was supplemented by the client name attribute and at least one launch time group (like melted launch date, launch time hour or even finer like launch time groups of 5,15,30 or whatever minutes.

If you don’t have such prepared XML data, get it by following Part 1 and prepare it as described in Part 2.

XML Data Import into Excel

The first step of course is to insert our XML data into Excel. I’ve been using the 2010 and 2013 (O’365) versions, both having almost the same path to achieve the goal.
In Excel, go to the Data tab (ribbon section), then select Get External Data -> From Other Sources -> From XML Data Import.

Excel_XML_01
Confirm the dialog that there is no Schema definition available for that XML file…

Excel_XML_02

and then wait a few seconds or minutes while Excel is importing all the data.

Excel_XML_05

That’s it for the import!

Report Generation with PivotTables

I’ve surely mentioned that I’m not a SQL Reporting master, now it’s time to unveil that this is true for Excel as well. Yes, I can calculate for how long I’ve to save my pocket money to buy a new Surface Pro 3 (given the fact that it would take 10 years or so and that I basically wrote all articles of this series on the first-generation SurfacePro in suburb trains I’ll stick on this first edition). So, no clue about Excel either, but I remember that back in the SoftGrid/App-V 4 days Justin Zarb used PivotTables to analyze App-V errors. So I at least remembered that PivotTables and graphs could be used to condense and present large amounts of data – this sounds like a valid approach for our aim, doesn’t it?

So, in Excel open the Insert tab, then go to PivotChart -> PivotChart and select the data area the you want to include (use the default selection for using all data). Note that the icon to PivotTables or charts also can be placed close to the Diagrams section of the ribbon.

Excel_XML_06

In the PivotChart Fields area, the top box lists all the fields (or columns) that are available.
A first decision is about your reporting period groups. If you want to see the app launches per day, then use the LaunchDate field. If you want a more granular report, just select one of the minute-based artificial groups we added to the XML.

Because my sample launches all happened within one hour I’ve chosen the 5-minutes period. So, drag your <period> field down into the Axis and into the Values areas. Then, drag the App_Name field down into the Legend area.

Excel_XML_08
The result would be a graph, showing all application launches within the given timeframe – however perhaps not in the format you like (I mean, who wants to have bars for that?).
So, right-click on the frame of the diagram, select Change Chart Type and select a diagram type that meets your requirements. You should play around a little bit with different fields in different positions and different diagram types.

Excel_XML_09

So, thanks to the guys from ProjectVRC I do have some nice graphs that I could leverage to sell my manager’s anything I want. From the above graph they can learn that Outlook can’t be replaced by any other email application, because it is used so often.

The next one clearly shows that we need that Microsoft Enterprise License Agreement, as two-third of our launched applications are from Microsoft.

Excel_XML_10

In the next graph you can see a little bit of ProjectVRC’s approach (with a very limited amount of clients): They start connectiong users, who work all the time, and just add more and more users to the environment. And like in our reality – some users end their tasks faster than others.

Excel_XML_11

 

Excel_XML_12

So now, are we finished finally with all that reporting stuff? Well, not really. First I could just add more quantity by showing more reports or slightly improve the quality by more sophisticated (and faster!) pre-processing of our XML data.
But there is one thing that might be interesting for you if you just don’t care about all the XML stuff. The next article will show you how to use Excel to create some reports out of the App-V Reporting SQL database. In fact is quite similar to this post with that PivotChart diagrams – but in the SQL database, info is spread across different tables and we can’t just add some more time grouping tables or columns to our SQL database.

Advertisements

Posted in App-V, Tools
Tags: , , ,

1 Comment »

  1. […] Kirx’ Blog: Server-free Reporting with App-V 5 (Part 3): Finally some Reports! […]

    Pingback by App-V 5 Reporting Resources | Kirx' Blog — June 4, 2014 @ 19:27


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: