Custom Report: The CIO's Software Compliance Report

Author Message
Winston Smith
  • Total Posts : 56
  • Scores: 0
  • Reward points : 11320
  • Joined: 12/18/2007
Custom Report: The CIO's Software Compliance Report - Thursday, September 18, 2008 11:42 AM
0
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 on Wednesday, October 01, 2008 4:13 PM>

Winston Smith
  • Total Posts : 56
  • Scores: 0
  • Reward points : 11320
  • Joined: 12/18/2007
RE: How does SCCM fit into enterprise software license management at your company? - Friday, September 19, 2008 9:56 AM
0
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. :)

brpo
  • Total Posts : 28
  • Scores: 1
  • Reward points : 0
  • Joined: 8/11/2006
RE: How does SCCM fit into enterprise software license management at your company? - Saturday, September 20, 2008 2:31 AM
0
Hi Winston
Thanks for sharing. I will have a look at those labels as I also try to consolidate reports.


Winston Smith
  • Total Posts : 56
  • Scores: 0
  • Reward points : 11320
  • Joined: 12/18/2007
RE: How does SCCM fit into enterprise software license management at your company? - Monday, September 22, 2008 9:58 AM
0
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

Winston Smith
  • Total Posts : 56
  • Scores: 0
  • Reward points : 11320
  • Joined: 12/18/2007
RE: How does SCCM fit into enterprise software license management at your company? - Monday, September 22, 2008 10:01 AM
0
Here's a screen shot of the column integer changes I mentioned...

[image]local://upfiles/25958/33602177C6D0412A99CE777EE0FBEAD1.jpg[/image]
Attached Image(s)

jnelson993
  • Total Posts : 931
  • Scores: 159
  • Reward points : 8850
  • Joined: 2/18/2005
  • Location: Minneapolis, MN
RE: How does SCCM fit into enterprise software license management at your company? - Monday, September 22, 2008 10:55 AM
0


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:)

Winston Smith
  • Total Posts : 56
  • Scores: 0
  • Reward points : 11320
  • Joined: 12/18/2007
RE: How does SCCM fit into enterprise software license management at your company? - Monday, September 22, 2008 11:44 AM
0


ORIGINAL: jnelson993



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.

tmiller
  • Total Posts : 372
  • Scores: 20
  • Reward points : 2040
  • Joined: 7/29/2003
  • Location: Iowa
RE: How does SCCM fit into enterprise software license management at your company? - Monday, September 22, 2008 12:16 PM
0
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.

jnelson993
  • Total Posts : 931
  • Scores: 159
  • Reward points : 8850
  • Joined: 2/18/2005
  • Location: Minneapolis, MN
RE: How does SCCM fit into enterprise software license management at your company? - Monday, September 22, 2008 12:49 PM
0

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.

jnelson993
  • Total Posts : 931
  • Scores: 159
  • Reward points : 8850
  • Joined: 2/18/2005
  • Location: Minneapolis, MN
RE: How does SCCM fit into enterprise software license management at your company? - Monday, September 22, 2008 12:50 PM
5
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

Winston Smith
  • Total Posts : 56
  • Scores: 0
  • Reward points : 11320
  • Joined: 12/18/2007
RE: How does SCCM fit into enterprise software license management at your company? - Wednesday, October 01, 2008 4:11 PM
0
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.

Winston Smith
  • Total Posts : 56
  • Scores: 0
  • Reward points : 11320
  • Joined: 12/18/2007
RE: How does SCCM fit into enterprise software license management at your company? - Wednesday, October 01, 2008 4:15 PM
0
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

MeenEnta
  • Total Posts : 163
  • Scores: 3
  • Reward points : 0
  • Joined: 9/9/2008
RE: How does SCCM fit into enterprise software license management at your company? - Thursday, October 02, 2008 10:26 AM
0
Is there an equivalent to this for SMS-2003-SP3?

Winston Smith
  • Total Posts : 56
  • Scores: 0
  • Reward points : 11320
  • Joined: 12/18/2007
RE: How does SCCM fit into enterprise software license management at your company? - Thursday, October 02, 2008 10:44 AM
0


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.

MeenEnta
  • Total Posts : 163
  • Scores: 3
  • Reward points : 0
  • Joined: 9/9/2008
RE: How does SCCM fit into enterprise software license management at your company? - Thursday, October 02, 2008 11:47 AM
0
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.

r2000
  • Total Posts : 253
  • Scores: 1
  • Reward points : 16480
  • Joined: 9/8/2005
RE: How does SCCM fit into enterprise software license management at your company? - Friday, October 10, 2008 9:45 AM
0
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.

Winston Smith
  • Total Posts : 56
  • Scores: 0
  • Reward points : 11320
  • Joined: 12/18/2007
RE: How does SCCM fit into enterprise software license management at your company? - Tuesday, October 14, 2008 3:38 PM
0


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.

kingskawn
  • Total Posts : 378
  • Scores: -2
  • Reward points : 16740
  • Joined: 4/25/2007
RE: How does SCCM fit into enterprise software license management at your company? - Wednesday, January 21, 2009 8:59 AM
0
I have a list of 30 software titles where I need a list of the computers that have those programs installed. Can this be done?

gjones
  • Total Posts : 2415
  • Scores: 140
  • Reward points : 129130
  • Joined: 6/5/2001
  • Location: Ottawa, Ontario, Canada
RE: How does SCCM fit into enterprise software license management at your company? - Wednesday, January 21, 2009 9:10 AM

kingskawn
  • Total Posts : 378
  • Scores: -2
  • Reward points : 16740
  • Joined: 4/25/2007
RE: How does SCCM fit into enterprise software license management at your company? - Wednesday, January 21, 2009 9:30 AM
0
It's more than the report 'Software 01A - Summary of installed software in a specific collection'.

It must be a list of programs that I set and with wildcards if possible. It musn't be with a prompt so a already with a collection.

For example:

Name                                                 Count
Adobe Reader                                 -     40
Adobe Photoshop                            -     45
Microsoft Office 2003 Standard          -     55
Snagit                                           -     50

And there must be a link to the computers that have those software installed.The 'Software 01A - Summary of installed software in a specific collection' report does this almost perfect but prompt me for a collection and gives me a full list.
<message edited by kingskawn on Wednesday, January 21, 2009 9:37 AM>

gjones
  • Total Posts : 2415
  • Scores: 140
  • Reward points : 129130
  • Joined: 6/5/2001
  • Location: Ottawa, Ontario, Canada
RE: How does SCCM fit into enterprise software license management at your company? - Wednesday, January 21, 2009 9:42 AM
0
It is not possible, you would need to hard code everything

swyck
  • Total Posts : 319
  • Scores: 5
  • Reward points : 32780
  • Joined: 5/30/2008
RE: How does SCCM fit into enterprise software license management at your company? - Wednesday, January 21, 2009 4:05 PM
0

ORIGINAL: Winston Smith

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.


Excellent post, Winston. Yes, been there done that and its a real pain.  Still on 2003 getting ready for SCCM.  Since one of the drivers is managing applications and licensing I've found this post to be very interesting.

I've been asked to list all apps installed on all machines numerous times over the years.  My usual response is to ask for clarification, ignore them if I can, or to discourage them.  The requesters usually don't reallize the vague nature and sheer size of the information.  Many applications do a poor job of registering themselves and its often hard to distinguish versions or even identify installed instances. Not to mention the noise from meaningless components. Just try to do a true up of MS SQL for some fun.  Other apps like Visio are relatively simple.

In general I try to identify proper signatures and methods to get a good install base count.  Then massage in Excel and voila you have application reports!

I'm looking forward to SCCM and some of the features available there.  Do you use any of the license management features?  I have hopes but I expect it will take some work make useful.

rapid
  • Total Posts : 4
  • Scores: 0
  • Reward points : 240
  • Joined: 4/27/2012
Re:Custom Report: The CIO's Software Compliance Report - Friday, April 27, 2012 6:39 AM
0
Hello,

Stumbled on this thread and it sounds exactly what I need.  Winston, is there any way to get a copy or more information on how to go about setting up this report?
 
Thanks in advance,
Ryan

rapid
  • Total Posts : 4
  • Scores: 0
  • Reward points : 240
  • Joined: 4/27/2012
Re:Custom Report: The CIO's Software Compliance Report - Friday, April 27, 2012 6:39 AM
0
double post doh!
<message edited by rapid on Friday, April 27, 2012 6:40 AM>

rapid
  • Total Posts : 4
  • Scores: 0
  • Reward points : 240
  • Joined: 4/27/2012
Re:Custom Report: The CIO's Software Compliance Report - Friday, April 27, 2012 6:39 AM
0
triple post doh!
<message edited by rapid on Friday, April 27, 2012 6:41 AM>

JasonWW
  • Total Posts : 7
  • Scores: 3
  • Reward points : 2680
  • Joined: 3/1/2012
Re:Custom Report: The CIO's Software Compliance Report - Friday, April 27, 2012 8:22 AM
0
Hi there
 
I can imagine that this is a pain.  Can you use the Labels available to you in Asset Intelligence and mark your applications with a label, something like Detail 1, Detail 2, Detail 3
 
You could then flag up the applications and then have only Detail Level =1 for one, <=2 for a similar report etc

rapid
  • Total Posts : 4
  • Scores: 0
  • Reward points : 240
  • Joined: 4/27/2012
Re:Custom Report: The CIO's Software Compliance Report - Tuesday, May 01, 2012 10:24 AM
0
Could do with Winston Smith updating the thread.. but I dont think he's posted since 2009 hehe..
 
Any one else understand enough to explain?

gjones
  • Total Posts : 2415
  • Scores: 140
  • Reward points : 129130
  • Joined: 6/5/2001
  • Location: Ottawa, Ontario, Canada
Re:Custom Report: The CIO's Software Compliance Report - Tuesday, May 01, 2012 6:06 PM
0
So there is already a count of ARP report in CM07. What do you like about Wilson's CIO report? Have you looked at 3rd party add-ons? What queries have you tried?