Collection based on software metering? (Full Version)

All Forums >> [Management Products] >> Microsoft Systems Management Server >> SMS 2003



Message

lightsout -> Collection based on software metering? (10/10/2006 10:41:13 AM)

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!



cmanley -> RE: Collection based on software metering? (10/10/2006 5:10:25 PM)

Its not pretty but try this..

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



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

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.



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

The V_R_System in SQL is actually  SMS_R_System in SMS Query.



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

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



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

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.



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

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



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

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.



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

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'



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

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



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

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.



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

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.



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

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.



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

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.



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

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



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

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. 



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

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.




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

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.



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

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.



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

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)



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

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



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

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. 



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

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



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

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



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.28125