Collection based on software metering?

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:

Collection based on software metering?

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

Logged in as: Guest

Printable Version

All Forums >> [Management Products] >> Microsoft Systems Management Server >> SMS 2003 >> Collection based on software metering?

Page: [1]

Message

<< Older Topic Newer Topic >>

Collection based on software metering? - 10/10/2006 10:41:13 AM

lightsout

Posts: 144
Score: 5
Joined: 10/10/2006
Status: offline

Hey folks,

Need some help with an issue... I'd like to create a collection (via a subselection query if required!) based on a software metering rule. Say for a specific software metering rule, list all computers that have not run it within 90 days.

I've looked at the SQL code for the software reports, but I've not been able to create a query using these. I'm not great with SQL coding. :(

Has anyone done this before who could help or give me some pointers?

Thanks!

Post #: 1

RE: Collection based on software metering? - 10/10/2006 5:10:25 PM

cmanley

Posts: 200
Score: 0
Joined: 11/16/2004
From: Madison WI
Status: offline

Its not pretty but try this..

http://myitforum.com/articles/1/view.asp?id=5578

_____________________________

Why do today what you can put off until tomorrow?

(in reply to lightsout)

Post #: 2

RE: Collection based on software metering? - 10/11/2006 8:10:42 AM

lightsout

Posts: 144
Score: 5
Joined: 10/10/2006
Status: offline

Oh, I am sure it is going to come down to that. The problem is, I can't seem to access certain SQL tables.

I looked at the SQL code for the reports on software metering in an attempt to reverse engineer them. I notice it uses a table called "V_R_System". No problem, I run "select * from V_R_System" in SQL Query Analyzer and it outputs fine. If I attempt the same thing in a SMS query, then it fails with "WBEM_E_INVALID_CLASS".

Using the above, I could create a collection to use the query. The problem is getting the query itself to work.

Thanks.

(in reply to cmanley)

Post #: 3

RE: Collection based on software metering? - 10/12/2006 10:39:48 PM

phaustein

Posts: 859
Score: 32
Joined: 3/21/2005
From: Washington, DC
Status: offline

The V_R_System in SQL is actually SMS_R_System in SMS Query.

_____________________________

Hope this helps.
Paul

(in reply to lightsout)

Post #: 4

RE: Collection based on software metering? - 10/13/2006 7:47:41 AM

lightsout

Posts: 144
Score: 5
Joined: 10/10/2006
Status: offline

So it is... I suppose the real question is, why the 2 different names?

(in reply to phaustein)

Post #: 5

RE: Collection based on software metering? - 10/13/2006 8:42:04 AM

edelmotte

Posts: 26
Score: 2
Joined: 9/14/2001
From: Brussels
Status: offline

When you do reporting ; you use SQL queries against SQL views.
When you do SMS Queries ; you use WQL queries against WMI classes.

That's why it is different.

_____________________________

Eric Delmotte
Microsoft SMS Consultant

(in reply to lightsout)

Post #: 6

RE: Collection based on software metering? - 9/20/2007 12:32:31 PM

teubeler

Posts: 86
Score: 0
Joined: 3/3/2005
Status: offline

I know this was awhile ago but do you still have the SQL Query you used for this?

(in reply to lightsout)

Post #: 7

RE: Collection based on software metering? - 9/20/2007 7:31:52 PM

jfunk

Posts: 73
Score: 12
Joined: 4/25/2005
Status: offline

Ahh yes the whole SQL -> WQL connundrum.

One problem 'here' is that there is no direct equivalence between the SQL table 'v_MeterRuleInstallBase' and any WMI class for that data. At the very least there is definately no 'sms_MeterRuleInstallBase' with a 1 to 1 mapping of columns that I could find. The view 'v_MeterRuleInstallBase' appearss to be created from other views / user tables which in turn are created from other views / user tables, and searching WMI it appears to draw from several different places there to build that view instead of just a single class. Please if anyone can confirm this I'd appreciate that.

So, at last giving up on converting to WQL I decided to use this helpful article (http://www.myitforum.com/articles/1/view.asp?id=5578) to add the SQL directly with an update to the 'Collection_Rules_SQL' table and added the below SQL to search for Mcafee Remote Desktop 32 uder it EXACT metering rule name as it appears in 'v_MeterRuleInstallBase' (''Mcafee RD32 - All Sites'')

I use dateadd / getdate to dynamically set 'not used for 3 months'.

Finally I set it to update only collection '500' the new one that was just created.

-------------Cut below me----------------

Update Collection_Rules_SQL set SQL = 'select sys.Netbios_Name0, inst.sms_Installed_Sites0
from v_R_System sys
left join v_RA_System_smsInstalledSites inst on sys.ResourceID=inst.ResourceID
where sys.ResourceID in
(
select ResourceID
from v_MeterRuleInstallBase
where ProductName=''Mcafee RD32 - All Sites''
)
and sys.ResourceID not in
(
select mus.ResourceID
from v_MonthlyUsageSummary mus
join v_MeteredFiles mf on mus.FileID=mf.MeteredFileID

where mf.ProductName=''Mcafee RD32 - All Sites''
and mus.TimeKey>=(100*Year(dateadd(mm,-3,getdate()))+Month(dateadd(mm,-3,getdate())))
and mus.LastUsage>=(DATEADD(ss,18000,(dateadd(mm,-3,getdate()))))
)
and sys.Client_Version0 >= ''2.5'''
where CollectionID = '500'

----------Cut above me------------

Hope this is helpful.

(in reply to teubeler)

Post #: 8

RE: Collection based on software metering? - 9/21/2007 8:54:02 AM

lightsout

Posts: 144
Score: 5
Joined: 10/10/2006
Status: offline

I like jfunk's better due to using dateadd, rather than the hardcoded values I use. Which I actually have a script which runs daily on the SMS server to update it.

The one gotcha with this is every machine is counted as having not run software in the last 90 days even if it isn't installed, or was installed 1 day ago. So you need to deal with these odd-balls also.

select SMS_R_System.ResourceID,SMS_R_System.ResourceType,SMS_R_System.Name,SMS_R_System.SMSUniqueIdentifier,SMS_R_System.ResourceDomainORWorkgroup,
SMS_R_System.Client from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceID 
inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_R_System.ResourceID is not in 
(select mus.ResourceID from SMS_MonthlyUsageSummary mus join SMS_MeteredFiles mf on mus.FileID=mf.MeteredFileID where mf.ProductName like "Adobe Acrobat All" and mus.LastUsage >= '7/27/2006') 
and SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName is like "Adobe Acrobat%"
and SMS_G_System_ADD_REMOVE_PROGRAMS.InstallDate < '20061011'
and SMS_G_System_OPERATING_SYSTEM.InstallDate < '20060910'

< Message edited by lightsout -- 9/21/2007 8:55:03 AM >

(in reply to jfunk)

Post #: 9

RE: Collection based on software metering? - 9/21/2007 9:02:03 AM

teubeler

Posts: 86
Score: 0
Joined: 3/3/2005
Status: offline

Thanks to both of you. I'll give those a try and see how they look.

< Message edited by teubeler -- 9/21/2007 9:12:31 AM >

(in reply to lightsout)

Post #: 10

RE: Collection based on software metering? - 9/21/2007 9:12:10 AM

teubeler

Posts: 86
Score: 0
Joined: 3/3/2005
Status: offline

As you might guess SQL is not one of my strong points so...

So if I am looking for any Visio installations can I change the ProductName catagory to ProductID or is this just the name given to the Metering rule?

quote:

ORIGINAL: jfunk

-------------Cut below me----------------

Update Collection_Rules_SQL set SQL = 'select sys.Netbios_Name0, inst.sms_Installed_Sites0
from v_R_System sys
left join v_RA_System_smsInstalledSites inst on sys.ResourceID=inst.ResourceID
where sys.ResourceID in
(
select ResourceID
from v_MeterRuleInstallBase
where ProductName=''Mcafee RD32 - All Sites''
)
and sys.ResourceID not in
(
select mus.ResourceID
from v_MonthlyUsageSummary mus
join v_MeteredFiles mf on mus.FileID=mf.MeteredFileID

where mf.ProductName=''Mcafee RD32 - All Sites''
and mus.TimeKey>=(100*Year(dateadd(mm,-3,getdate()))+Month(dateadd(mm,-3,getdate())))
and mus.LastUsage>=(DATEADD(ss,18000,(dateadd(mm,-3,getdate()))))
)
and sys.Client_Version0 >= ''2.5'''
where CollectionID = '500'

----------Cut above me------------

Hope this is helpful.

(in reply to jfunk)

Post #: 11

RE: Collection based on software metering? - 9/21/2007 11:10:12 AM

jfunk

Posts: 73
Score: 12
Joined: 4/25/2005
Status: offline

Sigh, I am not getting the effect desired here with the suggestion in the article.

Collection evaluation seems to hang and I am not sure yet what is the exact cause. The query it updates is fine so I am thinking things have changed since the article was written in 2003. I will keep looking at this.

To answer your question however, you would have had to change it to the metering rule name for your Visio metering. You could do some join on the v_GS_ADD_REMOVE_PROGRAMS table or something similar but why bother, for something static like a collection there is really no need for that extra work, same data will be returned.

You would have needed to edit these lines only:

where ProductName=''Mcafee RD32 - All Sites''    - should be -   where ProductName="<Your exact metering rule for Visio>"

where mf.ProductName=''Mcafee RD32 - All Sites''   - should be -   where mf.ProductName="<Your exact metering rule for Visio>"

where CollectionID = '500'    - should be -   where CollectionID = '<collection ID in decimal for the empty collection that you are editing the SQL directly using update>'    (Create a collection and add a query rule but give it no

I'll let you know what else I find.

(in reply to teubeler)

Post #: 12

RE: Collection based on software metering? - 9/21/2007 12:04:53 PM

teubeler

Posts: 86
Score: 0
Joined: 3/3/2005
Status: offline

Thanks I figured that part out but am getting the same problem as you with the collection stuck with an hour glass. I thought I would wait it out but it has been over 2 hrs and there isn't any abnormal SQL activity on the server. Let me know if you fix and thanks again.

(in reply to jfunk)

Post #: 13

RE: Collection based on software metering? - 9/21/2007 1:52:04 PM

jfunk

Posts: 73
Score: 12
Joined: 4/25/2005
Status: offline

Well I created a WQL query with a static timekey of 3 mos old that you can apply directly to the collection.

select sms_R_System.ResourceID,sms_R_System.ResourceType,sms_R_System.Name,sms_R_System.SMSUniqueIdentifier,sms_R_System.ResourceDomainORWorkgroup,sms_R_System.Client from sms_R_System
where sms_R_System.ResourceID in
(
SELECT resourceID
FROM sms_G_System_ADD_REMOVE_PROGRAMS
where sms_G_System_ADD_REMOVE_PROGRAMS.displayname='Remote Desktop 32'
)
and sms_R_System.ResourceID not in
(
select sms_MonthlyUsageSummary.ResourceID
from sms_MonthlyUsageSummary
join sms_MeteredFiles on sms_MonthlyUsageSummary.FileID=sms_MeteredFiles.MeteredFileID
where sms_MeteredFiles.ProductName='Mcafee RD32 - All Sites' and sms_MonthlyUsageSummary.TimeKey>=200706
)
and sms_R_System.ClientVersion >= '2.5'
and sms_R_System.Client = 1

I really would like to get the date functions working, can anyone suggest what I am doing wrong on this line:
sms_MonthlyUsageSummary.TimeKey>=(100*datepart(Year,(dateadd(mm,-3,getdate()))))+ (datepart(Month,(dateadd(mm,-3,getdate()))))

instead of

sms_MonthlyUsageSummary.TimeKey=200706

That line using the time functions works fine in SQL and I understood these all to be supported in WQL when used with the WHERE clause so I am at a loss what is happening here.

< Message edited by jfunk -- 9/21/2007 1:54:35 PM >

(in reply to teubeler)

Post #: 14

RE: Collection based on software metering? - 9/21/2007 2:57:37 PM

teubeler

Posts: 86
Score: 0
Joined: 3/3/2005
Status: offline

Well you don't have the greater than sign in there but that should still give you something.

< Message edited by teubeler -- 9/21/2007 3:17:57 PM >

(in reply to jfunk)

Post #: 15

RE: Collection based on software metering? - 9/21/2007 4:05:27 PM

teubeler

Posts: 86
Score: 0
Joined: 3/3/2005
Status: offline

This one didn't wotk for me either. I don't know. When I put it in a collection it started to come back with All add remove programs for all systems. I tried some changes but kept getting the same thing.

(in reply to jfunk)

Post #: 16

RE: Collection based on software metering? - 9/21/2007 4:07:25 PM

jfunk

Posts: 73
Score: 12
Joined: 4/25/2005
Status: offline

Roger that, sms_MonthlyUsageSummary.TimeKey=200706 should be sms_MonthlyUsageSummary.TimeKey>=200706

And that works fine of course being a static value, the issue seems to be the date function replacement for the static value of timekey that chokes.

(100*datepart(Year,(dateadd(mm,-3,getdate()))))+ (datepart(Month,(dateadd(mm,-3,getdate()))))

Which comes out to 200706 using select in SQL to check it.

(in reply to teubeler)

Post #: 17

RE: Collection based on software metering? - 9/21/2007 8:08:36 PM

jfunk

Posts: 73
Score: 12
Joined: 4/25/2005
Status: offline

Yes, I am seeing that the data returned is not what I intended. Apologies I dd not see this myself right away.

I'm going to come back to this one tomorrow and see what I can do with a fresh start. I tried starting with "Computers that have a metered program installed, but have not run the program since a specified date." originally and got off track somewhere in there while coverting between WQL / SQL.

< Message edited by jfunk -- 9/21/2007 8:09:08 PM >

(in reply to jfunk)

Post #: 18

RE: Collection based on software metering? - 9/24/2007 11:04:59 AM

teubeler

Posts: 86
Score: 0
Joined: 3/3/2005
Status: offline

So I have this one giving me reasonable numbers using it in a Query but I need output I can verify. Is there anyway to have it show the last time the file was accessed in the output? If I add any Add/Remove Program information it it comes back with all users Add remove programs not just Visio.

select distinct Name, LastLogonUserName, ResourceDomainORWorkgroup, ResourceId from SMS_R_System where ResourceId in (SELECT resourceID FROM sms_G_System_ADD_REMOVE_PROGRAMS where sms_G_System_ADD_REMOVE_PROGRAMS.ProdID = "{90510409-6000-11D3-8CFE-0150048383C9}") and ResourceId not in (select sms_MonthlyUsageSummary.ResourceID from sms_MonthlyUsageSummary join sms_MeteredFiles on sms_MonthlyUsageSummary.FileID=sms_MeteredFiles.MeteredFileID
where sms_MeteredFiles.ProductName="Visio" and sms_MonthlyUsageSummary.TimeKey>=200706) and ClientVersion >= "2.5" and Client = 1

And by the way thanks for your help on this. This forum has been a savior for me on many occasions. I had no Idea on how to do these subsellected values.

< Message edited by teubeler -- 9/24/2007 12:24:31 PM >

(in reply to jfunk)

Post #: 19

RE: Collection based on software metering? - 9/27/2007 11:04:59 AM

teubeler

Posts: 86
Score: 0
Joined: 3/3/2005
Status: offline

We had some hours from our EA so we got som assistance from MS. Working together we came up with this Query. Matches very well with the reporting data and has the date diff function.

SELECT DISTINCT Name FROM SMS_R_System WHERE ResourceID NOT IN (SELECT DISTINCT SMS_R_System.ResourceID FROM SMS_MonthlyUsageSummary, SMS_MeteredFiles, SMS_R_System
WHERE SMS_MonthlyUsageSummary.ResourceID = SMS_R_System.ResourceID AND SMS_MonthlyUsageSummary.FileID IN (
SELECT Distinct MeteredFileID from SMS_MeteredFiles WHERE FileName = 'visio.exe') AND SMS_R_System.Client = 1 AND DATEDIFF(Day, SMS_MonthlyUsageSummary.LastUsage, Getdate()) < 90) AND SMS_R_System.Client = 1 AND ResourceID IN (select DISTINCT SMS_R_System.ResourceID from SMS_R_System inner join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SoftwareFile.FileName = "visio.exe" AND SMS_R_System.Client = 1)

(in reply to jfunk)

Post #: 20

RE: Collection based on software metering? - 9/28/2007 8:49:15 AM

lightsout

Posts: 144
Score: 5
Joined: 10/10/2006
Status: offline

Thanks for the heads-up! So youdidn't need the TimeKey field in the end?

(in reply to teubeler)

Post #: 21

RE: Collection based on software metering? - 9/28/2007 9:40:31 AM

teubeler

Posts: 86
Score: 0
Joined: 3/3/2005
Status: offline

The only problem is you have to limit the reporting to just the Machine name. As soon as you add additional columns it changes the results. If you could some how make the Report information exclusive from the query you could have much better reports.

A big thanks to jfunk. His info got us a really good start.

< Message edited by teubeler -- 9/28/2007 9:42:16 AM >

(in reply to lightsout)

Post #: 22

RE: Collection based on software metering? - 6/26/2008 7:39:08 AM

r2000

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

Hi.
I am looking exactly for this solution, for some of my expensive programs.

But the thread is little make me little confused, it would be very great if someone that running this solution explained it in one article ore documentation.
BR
R2000

(in reply to teubeler)

Post #: 23

RE: Collection based on software metering? - 6/26/2008 9:52:46 AM

teubeler

Posts: 86
Score: 0
Joined: 3/3/2005
Status: offline

Sadly there is no actual documentation. The best way I can explain it is:
1. Create a Metering Rule based on the program you are interested in
2. Wait 3 months for Metering Data to build up so the query has data to work off.
3. Create the query but replace visio.exe with your metered file.

I went back and did alot of physical checking when I first ran this but the data is good.

Tim

(in reply to r2000)

Post #: 24