Richards Blog

I recently had to create a report for our security dept. for all computers running a certain program. After thinking about this for a moment I decided that SCCM (Configmgr) was the best way to go. The challenge was as follows.

1. Show all computers running exe.exe
2. Show only computers for the last 2 days
3. Show only computers prefixed with ‘FP’ (our naming convention)

First thing to do was to ensure that Software Metering was enabled on our Central Site (http://technet.microsoft.com/en-us/library/bb680774.aspx) and then create a simple metering rule (http://technet.microsoft.com/en-us/library/bb633043.aspx). This took only a few minutes, but did require me to be added to the SCCM Admin group :/

We have our software metering set to collect daily as not to max out our bandwidth, so testing of this will need to wait until tomorrow.

The next step is to create the T-SQL query for the report; I did some digging around the SCCM site and found some really useful documentation for writing custom reports. From (http://technet.microsoft.com/en-us/configmgr/dd248625.aspx) I grabbed the SQL views which have everything from software distribution to deployment mapped out (very useful).

Armed with this I began playing around, using the views to get all the tables and information I needed. Finally the following query emerged:

SELECT
    d.Name0 as [ComputerName],
    a.[FileName],
    a.[FileVersion],
    b.UsageCount,
    b.UsageTime as [Usage Time],
    substring(cast(b.LastUsage AS Varchar), 0, 12) as [Last Run],
    c.UserName as [User]
FROM v_SoftwareFile a
INNER JOIN v_MonthlyUsageSummary b
    ON b.FileID = a.FileID
INNER JOIN v_MeteredUser c
    ON c.MeteredUserID = b.MeteredUserID
INNER JOIN v_r_system d
    ON d.ResourceID = b.ResourceID
WHERE a.[FileName] = @fileName
    AND b.LastUsage >= Getdate() - 2
    AND d.Name0 like 'FP%'
ORDER BY d.Name0 ASC

As you can see, I am getting the exe name, computer, last run, user, file version and amount of time the application has been run. This met the requirements for the security team, and was one of my first proper SCCM reports.

I have made use of the prompts in SCCM allowing the end user to choose the EXE making this re-usable. I will do a section on adding prompts into a SCCM report in my next post.

Heres something cool that is going around and applies to all windows 7 computers.

Just create a folder on your desktop and name it any of the below names.

LOCATION.{00C6D95F-329C-409a-81D7-C46C66EA7F33}
BIOMETRIC.{0142e4d0-fb7a-11dc-ba4a-000ffe7ab428}
POWER.{025A5937-A6BE-4686-A844-36FE4BEC8B6D}
NOTIFICATION.{05d7b0f4-2121-4eff-bf6b-ed3f69b894d9}
CREDENTIALS.{1206F5F1-0569-412C-8FEC-3204630DFB70}
NETWORKAPPS.{15eae92e-f17a-4431-9f28-805e482dafd4}
DEFAULTS.{17cd9488-1228-4b2f-88ce-4298e93e0966}
ASSEMBLIES.{1D2680C9-0E2A-469d-B787-065558BC7D43}
WIRELESS.{1FA9085F-25A2-489B-85D4-86326EEDCD87}
NETWORK.{208D2C60-3AEA-1069-A2D7-08002B30309D}
DRIVES.{20D04FE0-3AEA-1069-A2D8-08002B30309D}
PRINTERS.{2227A280-3AEA-1069-A2DE-08002B30309D}
REMOTE.{241D7C96-F8BF-4F85-B01F-E2B043341A4B}
FIREWALL.{4026492F-2F69-46B8-B9BF-5654FC07E423}
PERFORMANCE.{78F3955E-3B90-4184-BD14-5397C15F1EFC}

Good evening Ladies and Gentlemen, you are about to witness a unique event in history, and no, we’re not talking about the FIFA World Cup, but rather the first and probably last time my wife is going to let me speak on the behalf of both of us.

Our wedding would not have been nearly as magical as it has been today without the help of many extraordinary and special people. There have been so many people working behind the scenes to ensure today has been truly unforgettable … so many in fact that I do apologize for not thanking on a name by name basis. To everyone who helped with the preparation and execution of our day Rachel and I would like to say a heartfelt thank you.

In addition I would like to now give thanks to a few special people.

Firstly I would like to thank my beautiful wife, who despite all my flaws has been able to put up with me over the past 3 years. Rachel you make my heart skip a beat each time I see you and I could not imagine my life without you in it. You have truly been a blessing to me and I am looking forward to a long and happy marriage with you by my side at all times.

Most of you who know Rachel will know how loving, kind and well mannered she is, and this is thanks to her family. Rob, Gill and Simon I would like to personally thank you for the roles that you played in raising such a wonderful Christian woman. I have no doubt that all of Rachel’s traits are the result of the loving home she has grown up in. I would also like to thank you for welcoming me so warmly into your family, from the first gathering I have truly felt like part of your family.

I would like to thank my sister and mother for everything they have done for me. Mom I would like to especially thank you for your selfless nature in always putting our needs ahead of yours and always going the extra mile so we could have everything we needed. You have always been there for us and often go without so we can have the things we wanted. You have done a fine job in raising me and I would not be the man I am today without the love and attention I got from you while growing up.

I would like to say an extra thank you to everyone here for making today such an unforgettable event. I would like to especially thank everyone who has travelled a long distance to be here today and wish you a safe trip back home.

I would like to present my families with a small gift to say thank you for all you have done for us.

Would everyone please raise your glass with me and toast to my lovely wife, may we live happily ever after.

Welcome to my first post for this blog.

Today marks 2 weeks to go until I am a kept man and could not be happier, I am going to be getting married to Rachel Mary Mc Gibbon on the 2 Jan 2010.

Just though that this was something that I should share with you all.

About this blog

I am currently working for a gaming company based in Durban North and get exposed to a whole range of technologies (most of them being MS products), I have interests in PHP and web development as well as I am trying to teach myself some c++ coding. This blog will be full of all the things I discover during my work days and will have posts relating to the following:

1. PHP
2. Web Development
3. SCOM 2007
4. SCCM
5. C++
6. Powershell
7. Windows Scripting (vbscript, jscript)
8. Much more

So stay tuned for more

Cheers