Custom Report: The CIO's Software Compliance Report

myITforum.com Community Forum

Home Forums Blogs Live Support chat Search Articles Wiki FAQ Email Lists Register Login My Profile Inbox Address Book My Subscription My Forums

Photo Gallery Member List Search Calendars FAQ Ticket List Log Out

All Forums RSS Feed Subscription:

Custom Report: The CIO's Software Compliance Report

View related threads: (in this forum | in all forums)

Logged in as: Guest

Printable Version

All Forums >> [Management Products] >> System Center Products >> System Center Configuration Manager >> Custom Report: The CIO's Software Compliance Report

Page: [1]

Message

<< Older Topic Newer Topic >>

Custom Report: The CIO's Software Compliance Report - 9/18/2008 11:42:44 AM

Winston Smith

Posts: 53
Score: 0
Joined: 12/18/2007
Status: offline

One of the expectations upper management has is that SCCM will be the primary tool for enterprise software tracking and auditing. This seems like a no-brainer, right? Well, how do you track what needs to be tracked? I've posted several times regarding the CIO's software report I'm expected to produce. He wants to see 'all software' installed on 'all systems', but wants me to exclude all Microsoft products (we've already got a seperate MS ledger report they like and we used for true-up a few months ago), he doesn't want to see codecs and drivers, he doesn't want to see patches and updates or free software like iTunes and so on and so forth. He wants to see stuff like Business Objects, McAfee and Adobe products - the stuff we have to pay for to use.

Easier said than done.

So I started out with the Add/Remove Programs report. 18,000 lines 90% of which we don't want to see. No good. So management dropped it for a while, but a couple months ago it came back.

My second attempt at this was customizing the Software 01A report, filtering stuff out. I started by adding lines to the SQL statement to exclude items. Then I hit the limit on SQL statement size, and had to add a custom table to the SCCM database to store all the software titles I don't want to see. That's in addition to what I filtered out in the statement already. To make it clean, I'd need to add additional tables to exclude specific Publishers (Microsoft, in this case), Family and Category types, and so on and so forth. So now we're talking about making some pretty significant changes to the SCCM database, to make it do what we think it's supposed to do anyways. So I've spent hours researching all this, and more hours implementing it, and then more hours filtering out software titles to pare down this giant list of software. It's at 1,700 titles, and every time I re-run it I find more junk titles in there (because folks are always installing new stuff). WTF?

So this morning, I look into the Asset Intelligence branch of the SCCM Console Tree. I discover I can add custom labels to desired software titles ("CIO's Software Report") then run a 'canned' report against software with the desired label(s). Cool. This should help, but it's still going to be a tough sell. One of the desired characteristics of the report is that software titles will be consolidated in the event of multiple versions (in other words, one line that says 'Adobe Reader' that you can drill down into and see version 4, 5, etc). That's another whole set of massive customizations that will have to be done on every title we want condensed. That's just not going to work - it's all too complicated and time-consuming, I think.

But when I was set off on this course many months ago, the question from the CIO was: how do other companies do this? How do other companies get the big picture of installed software across the enterprise? My developing vision is to use this Asset Intelligence report with custom labels to track specific titles, and in cases where we want a deep dive (like Adobe true-up), we break out a custom report or two, and spend some time massaging the data in Excel if necessary (consolidating versions like I mentioned above. A couple of complicating factors are that we're a software company and so have a huge contingent of developers and tech-savvy people that download a lot of diverse products. Oh, and we don't lock down machines, so anyone can install anything. We all know we need to lock machines down, and eventually we will, but for now that's just the way it is.

So how does your company handle this task?

< Message edited by Winston Smith -- 10/1/2008 4:13:52 PM >

Post #: 1

RE: How does SCCM fit into enterprise software license ... - 9/19/2008 9:56:55 AM

Winston Smith

Posts: 53
Score: 0
Joined: 12/18/2007
Status: offline

A little more on the CIO's software Report...

A little brainstorming with my boss, and I think we've identified a means of consolidating software titles. Initially, I was labeling all the software I wanted to report on with a single, generic label, and my list was still 1) 1,000+ lines and 2) not consolidated (I was displaying over 100 rows of Adobe products alone).

So our idea is to use the labels thusly: all Adobe Reader versions get labeled 'Adobe Reader', all Acrobat versions get 'Adobe Acrobat' and so on, then write a summary report on the labels (v_LU_Tags) to display the label name and number of instances, and then management can drill into the lines to reveal greater detail.

In retrospect, it all seems so simple. Like I've said a thousand times in my 10+ years in IT: I wish I'd taken some time to actually look at this product and worked with it in a lab so I'd have known this, instead of having to trial & error my way through it after putting it into production. :)

(in reply to Winston Smith)

Post #: 2

RE: How does SCCM fit into enterprise software license ... - 9/20/2008 2:31:46 AM

brpo

Posts: 35
Score: 1
Joined: 8/11/2006
Status: offline

Hi Winston
Thanks for sharing. I will have a look at those labels as I also try to consolidate reports.

(in reply to Winston Smith)

Post #: 3

RE: How does SCCM fit into enterprise software license ... - 9/22/2008 9:58:48 AM

Winston Smith

Posts: 53
Score: 0
Joined: 12/18/2007
Status: offline

So, here's an updated version of the report that is starting to get into the shape I want it. On the first page, it displays the column headers I'm looking for and I am able to expand each header to see details. The first page displays a column containing the titles of my custom labels - "Adobe Reader (All Versions)", "Adobe Acrobat (All Versions)", "Interwise/AT&T", and so on. Each row can be drilled into to display all versions of the software. The second page is spawned from The Software 11A Report, which summarizes by Software Title. Then, each individual title can be drilled into to display the machines upon which it is installed.

I had some trouble getting the front-end report to pass the right information to the back-end. The first run returned an error with 'SMS00001' populated in all the fields of the Software 11A Report. This was overcome by passing the empty field below ('' in the select portion of the statement below) and changing a couple of the integer values on the columns in the link to the back-end report (Software 11A). I'm hooking Tag 2 because Tag 1 is populated by another label (Inventoried Software). This may change, but it's a minor detail.

My next steps are to add additional custom labels, assign those labels to the applicable software titles. At first I was defining each custom tag that I wanted to report, but I remembered there is a limit to the size of a SQL statement, so instead I added a wildcard to the where clause. I'll just have to be careful that all the titles I want to track get labeled in the Tag2 field.

Here's the very simple statement...

select
'SMS00001' AS CollectionID, Tag2Name AS 'Software Group', Count(1) AS Count, ''
from v_LU_SoftwareList_Editable
where
Tag2Name like '%'
group by Tag2Name
order by Tag2Name

(in reply to brpo)

Post #: 4

RE: How does SCCM fit into enterprise software license ... - 9/22/2008 10:01:16 AM

Winston Smith

Posts: 53
Score: 0
Joined: 12/18/2007
Status: offline

Here's a screen shot of the column integer changes I mentioned...

Thumbnail Image

Attachment (1)

(in reply to Winston Smith)

Post #: 5

RE: How does SCCM fit into enterprise software license ... - 9/22/2008 10:55:05 AM

jnelson993

Posts: 899
Score: 127
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline

quote:

select
'SMS00001' AS CollectionID, Tag2Name AS 'Software Group', Count(1) AS Count, ''
from v_LU_SoftwareList_Editable
where
Tag2Name like '%'
group by Tag2Name
order by Tag2Name

I'm trying to understand why you need the LIKE '%'...all that does is return all rows. Remove the WHERE clause altogether and you'll get the same effect, with a tiny bit less work for SQL to do.

Or am I misunderstanding what you're getting at? (very possible:)

_____________________________

Number2 (John Nelson)
MyITForum - Blog
MyITForum - Forum Posts

(in reply to Winston Smith)

Post #: 6

RE: How does SCCM fit into enterprise software license ... - 9/22/2008 11:44:31 AM

Winston Smith

Posts: 53
Score: 0
Joined: 12/18/2007
Status: offline

quote:

ORIGINAL: jnelson993

quote:

select
'SMS00001' AS CollectionID, Tag2Name AS 'Software Group', Count(1) AS Count, ''
from v_LU_SoftwareList_Editable
where
Tag2Name like '%'
group by Tag2Name
order by Tag2Name

I'm trying to understand why you need the LIKE '%'...all that does is return all rows. Remove the WHERE clause altogether and you'll get the same effect, with a tiny bit less work for SQL to do.

Or am I misunderstanding what you're getting at? (very possible:)

It didn't occur to me that I don't need it (I don't know much about writing SQL statements). I took it out to see what would happen - I still got the rows I want, but also got an additional row with a blank value for 'Software Group' that contains all 17,000+ titles. So I put the where clause back in.

(in reply to jnelson993)

Post #: 7

RE: How does SCCM fit into enterprise software license ... - 9/22/2008 12:16:14 PM

tmiller

Posts: 665
Score: 18
Joined: 7/29/2003
From: Iowa
Status: offline

I think, but do not know for certain, that '%' only matches non-NULL values.

I think you could also put in where Tag2Name is not null, but I don't know if that would be any more or less efficient.

(in reply to Winston Smith)

Post #: 8

RE: How does SCCM fit into enterprise software license ... - 9/22/2008 12:49:14 PM

jnelson993

Posts: 899
Score: 127
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline

Ah, I got ya. If you're using it to remove nulls, then it will be more intuitive to yourself and others who go to edit this query later on if you explicitly excluded the NULLs. Otherwise selecting LIKE '%' indicates to everyone that you want all records. Also, if you get into the habit of using it to eliminate nulls, you will get frustrated when you try it on a field that doesn't allow nulls and instead has empty strings, which is not NULL.

But it works for what you're using it for.

_____________________________

Number2 (John Nelson)
MyITForum - Blog
MyITForum - Forum Posts

(in reply to tmiller)

Post #: 9

RE: How does SCCM fit into enterprise software license ... - 9/22/2008 12:50:14 PM 1 votes

jnelson993

Posts: 899
Score: 127
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline

SELECT
'SMS00001' AS CollectionID,
Tag2Name   AS 'Software Group',
COUNT(1)   AS COUNT,
''
FROM
v_LU_SoftwareList_Editable
WHERE    Tag2Name IS NOT NULL
GROUP BY Tag2Name
ORDER BY Tag2Name

_____________________________

Number2 (John Nelson)
MyITForum - Blog
MyITForum - Forum Posts

(in reply to jnelson993)

Post #: 10

RE: How does SCCM fit into enterprise software license ... - 10/1/2008 4:11:33 PM

Winston Smith

Posts: 53
Score: 0
Joined: 12/18/2007
Status: offline

Here is another, better way to produce the CIO's report...

If you recall, one of the requirements was that the report summarize the total install instances of each software title. In the earlier version of this report, I was summarizing the number of titles associated with the custom tag I had assigned. For example, when I looked at Count for 'Adobe Reader (All Versions)' I saw a count of 60 - that's how many different versions of Adobe Reader I'd grouped in the 'All Versions' tag. Unfortunately, that's not what the boss wanted. He wanted a count of all computers upon which all versions of Adobe Reader is installed. How the bloody hell do I do that???

Here's how...

[report syntax]

select 'SMS00001' AS CollectionID,
[Label 2] As 'Software Group',
sum([Count of Computers]) As Count,
''
from (
select Soft.NormalizedName [Product Name],
TG1.TagName [Label 1],
TG2.TagName [Label 2],
TG3.TagName [Label 3],
count(Soft.ResourceID) [Count of Computers],
Soft.SoftwareID [Software ID]
from
dbo.v_GS_INSTALLED_SOFTWARE_CATEGORIZED Soft
inner join
(select v_R_System_Valid.ResourceID
from v_R_System_Valid
join v_FullCollectionMembership ON v_R_System_Valid.ResourceID = v_FullCollectionMembership.ResourceID
where v_FullCollectionMembership.CollectionID = 'sms00001'
)MEM ON MEM.ResourceID = Soft.ResourceID
inner join v_LU_SoftwareList_Local ls ON ls.SoftwareID = Soft.SoftwareID
left join v_LU_Tags TG1 ON TG1.TagID = ls.Tag1ID
left join v_LU_Tags TG2 ON TG2.TagID = ls.Tag2ID
left join v_LU_Tags TG3 ON TG3.TagID = ls.Tag3ID
where COALESCE (TG1.TagID, TG2.TagID, TG3.TagID) IS NOT NULL
and TG2.TagName IS NOT NULL
group by
Soft.SoftwareID, Soft.NormalizedName , TG1.TagName, TG2.TagName, TG3.TagName
) ccount
group by [Label 2]
order by [Label 2]

[/report syntax]

The report displays the tag names as well as a count of the instances upon which all included titles are installed. I can drill into each tag title to reveal all the individual titles/versions and the counts thereof, and I can drill into each individual software title and reveal each computer upon which it is installed (and I can drill into the individual computers to get the details).

Note: This report relies upon the same custom column integers I mentioned in Post 4.

(in reply to jnelson993)

Post #: 11

RE: How does SCCM fit into enterprise software license ... - 10/1/2008 4:15:36 PM

Winston Smith

Posts: 53
Score: 0
Joined: 12/18/2007
Status: offline

Note to all: I changed the title of this thread, since it sort of veered away from what I originally intended for it.

Cheers,

Winston

(in reply to Winston Smith)

Post #: 12

RE: How does SCCM fit into enterprise software license ... - 10/2/2008 10:26:28 AM

MeenEnta

Posts: 107
Score: 0
Joined: 9/9/2008
Status: offline

Is there an equivalent to this for SMS-2003-SP3?

(in reply to Winston Smith)

Post #: 13

RE: How does SCCM fit into enterprise software license ... - 10/2/2008 10:44:48 AM

Winston Smith

Posts: 53
Score: 0
Joined: 12/18/2007
Status: offline

quote:

ORIGINAL: MeenEnta

Is there an equivalent to this for SMS-2003-SP3?

I don't think SMS 2003 SP3 gives you the ability to apply custom tags, but I could be wrong. I never saw them in SMS 2003, but then again, I was never looking for them.

(in reply to MeenEnta)

Post #: 14

RE: How does SCCM fit into enterprise software license ... - 10/2/2008 11:47:43 AM

MeenEnta

Posts: 107
Score: 0
Joined: 9/9/2008
Status: offline

Definetely no Custom Tags in SMS 2003 SP3, and that's why I asked if your query version can be translated to it.

Thank you Winston.

(in reply to Winston Smith)

Post #: 15

RE: How does SCCM fit into enterprise software license ... - 10/10/2008 9:45:16 AM

r2000

Posts: 282
Score: 0
Joined: 9/8/2005
Status: offline

Hi Winston Smith!
I have the same problem as u describe about a good solution for enterprise software tracking and auditing.
I have been reading your post, and the solution sounds great.
I wonder if you can summary your solution, a guide line how to implement it and how it works.

It’s little confusing to read all the post.

(in reply to MeenEnta)

Post #: 16

RE: How does SCCM fit into enterprise software license ... - 10/14/2008 3:38:36 PM

Winston Smith

Posts: 53
Score: 0
Joined: 12/18/2007
Status: offline

quote:

ORIGINAL: r2000

Hi Winston Smith!
I have the same problem as u describe about a good solution for enterprise software tracking and auditing.
I have been reading your post, and the solution sounds great.
I wonder if you can summary your solution, a guide line how to implement it and how it works.

It’s little confusing to read all the post.

Yes, once I have everything working the way it is supposed to I will summarize and make it more clear so it can be repeated. I'll post it within a week or so.

(in reply to r2000)

Post #: 17

Page: [1]

All Forums >> [Management Products] >> System Center Products >> System Center Configuration Manager >> Custom Report: The CIO's Software Compliance Report

Page: [1]

Jump to:

New Messages	No New Messages
Hot Topic w/ New Messages	Hot Topic w/o New Messages
Locked w/ New Messages	Locked w/o New Messages

Post New Thread

Reply to Message

Post New Poll

Submit Vote

Delete My Own Post

Delete My Own Thread

Rate Posts

0.299