Correct, you shouldn’t use this view to determine if the software is installed
or not.
Should be easy to build the query, something like…
Select displayname, version, last used date, last used user
From v_r_system sys join
V_add_remove_programs arp on sys.resourceid = arp.resourceid join
V_gs_ccm_recently_used_apps rua on sys.resourceid = rua.resourceid
Where arp.displayname = ‘Google Chrome’ and rua.lastuseddate <
datediff(getdate(),-30)
That’s shorthand and probably has some syntax errors, but it would show chrome
installs that have been used in last 30 days.
Daniel Ratliff
From: [email protected] [mailto:[email protected]] On
Behalf Of Augustine, Greg
Sent: Friday, November 20, 2015 4:28 PM
To: '[email protected]'
Subject: RE: [mssms] Software Metering and software removal
Well then I guess you would need to also reference either installed software or
some other HW inventoried data. Or could use what some would refer to as the
dreaded software inventory to determine if it was installed??
From: [email protected]<mailto:[email protected]>
[mailto:[email protected]] On Behalf Of Daniel Ratliff
Sent: Friday, November 20, 2015 2:49 PM
To: [email protected]<mailto:[email protected]>
Subject: RE: [mssms] Software Metering and software removal
No, this is last time used. If the software gets removed, it was still used on
X date and time by X user and will still show in the view.
Daniel Ratliff
From: [email protected]<mailto:[email protected]>
[mailto:[email protected]] On Behalf Of Augustine, Greg
Sent: Friday, November 20, 2015 3:38 PM
To: '[email protected]'
Subject: RE: [mssms] Software Metering and software removal
Ok, if the software is removed does it remove it from this view?
From: [email protected]<mailto:[email protected]>
[mailto:[email protected]] On Behalf Of Daniel Ratliff
Sent: Friday, November 20, 2015 2:23 PM
To: [email protected]<mailto:[email protected]>
Subject: RE: [mssms] Software Metering and software removal
Yes, I think natively its part of asset intelligence.
Daniel Ratliff
From: [email protected]<mailto:[email protected]>
[mailto:[email protected]] On Behalf Of Augustine, Greg
Sent: Friday, November 20, 2015 3:20 PM
To: '[email protected]'
Subject: RE: [mssms] Software Metering and software removal
Is this being pulled in with HW inventory?
From: [email protected]<mailto:[email protected]>
[mailto:[email protected]] On Behalf Of Daniel Ratliff
Sent: Friday, November 20, 2015 1:55 PM
To: [email protected]<mailto:[email protected]>
Subject: RE: [mssms] Software Metering and software removal
Personally I recommend v_gs_ccm_recentlyusedapps. Its simple, and not a pain in
the butt to write a report on.
Daniel Ratliff
From: [email protected]<mailto:[email protected]>
[mailto:[email protected]] On Behalf Of Marcum, John
Sent: Friday, November 20, 2015 2:47 PM
To: [email protected]<mailto:[email protected]>
Subject: RE: [mssms] Software Metering and software removal
He’s gonna say metering sucks and there are plenty of ways to do this without
metering.
Not that there aren’t valid reasons for metering but simply determining if
something is used is not one of them.
________________________________
John Marcum
MCITP, MCTS, MCSA
Desktop Architect
Bradley Arant Boult Cummings LLP
________________________________
[H_Logo]
From: [email protected]<mailto:[email protected]>
[mailto:[email protected]] On Behalf Of Augustine, Greg
Sent: Friday, November 20, 2015 1:38 PM
To: '[email protected]'
<[email protected]<mailto:[email protected]>>
Subject: RE: [mssms] Software Metering and software removal
Go for it, looking for input.
From: [email protected]<mailto:[email protected]>
[mailto:[email protected]] On Behalf Of Ed Aldrich
Sent: Friday, November 20, 2015 1:30 PM
To: [email protected]<mailto:[email protected]>
Subject: RE: [mssms] Software Metering and software removal
Oooohhhhh…
Can I SAY it??? ☺
Ed Aldrich
[email protected]<mailto:[email protected]> | www.1e.com<http://www.1e.com/>
[Description: Description: cid:[email protected]] Ent Cli Mgmt
(2003-2015)
Sent from my Microsoft Surface
From: [email protected]<mailto:[email protected]>
[mailto:[email protected]] On Behalf Of Augustine, Greg
Sent: Friday, November 20, 2015 11:55 AM
To: '[email protected]'
<[email protected]<mailto:[email protected]>>
Subject: [mssms] Software Metering and software removal
I would like all of your thoughts on software metering and collections to
automatically remove software. We have built collections like the below that
give us the information but when we run the removal it does not seem to update
the information. I am assuming that it is related to the software inventory
update cycle.
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_SoftwareFile ON SMS_G_System_SoftwareFile.ResourceID =
SMS_R_SYSTEM.ResourceID
INNER JOIN SMS_MeteredFiles ON SMS_G_System_SoftwareFile.FileID =
SMS_MeteredFiles.MeteredFileID
INNER JOIN SMS_MeteredProductRule ON SMS_MeteredProductRule.RuleID =
SMS_MeteredFiles.RuleID
WHERE SMS_MeteredFiles.RuleID = 16777399
AND DateDiff(day, SMS_G_System_SoftwareFile.ModifiedDate, GetDate()) > 60
AND SMS_R_SYSTEM.ResourceID NOT IN
(SELECT DISTINCT SMS_MonthlyUsageSummary.ResourceID
FROM SMS_MonthlyUsageSummary INNER JOIN SMS_MeteredFiles ON
SMS_MonthlyUsageSummary.FileID = SMS_MeteredFiles.MeteredFileID
WHERE DateDiff(day, SMS_MonthlyUsageSummary.LastUsage, GetDate()) < 60 AND
SMS_MeteredFiles.RuleID = 16777399)
We have also look at the following query to see if it work out better, which it
does run fast thru SQL.
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_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID =
SMS_R_System.ResourceId
inner join SMS_G_System_COMPUTER_SYSTEM on
SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId
where SMS_G_System_SoftwareFile.FileName = "snagit32.exe"
and SMS_G_System_SoftwareFile.FileVersion like "11.%"
and SMS_R_System.ResourceId not in
(select SMS_R_SYSTEM.ResourceID
from SMS_R_SYSTEM
inner join SMS_MonthlyUsageSummary on SMS_R_SYSTEM.ResourceID =
SMS_MonthlyUsageSummary.ResourceID
INNER JOIN SMS_MeteredFiles ON SMS_MonthlyUsageSummary.FileID =
SMS_MeteredFile.MeteredFileID
WHERE DateDiff(day, SMS_MonthlyUsageSummary.LastUsage, GetDate()) < 90
AND SMS_MeteredFiles.RuleID = 16777399)
and SMS_G_System_COMPUTER_SYSTEM.Name not in
(select SMS_R_System.Name
from SMS_R_System
inner join SMS_G_System_LastSoftwareScan on
SMS_G_System_LastSoftwareScan.ResourceId = SMS_R_System.ResourceId
where Datediff(day, SMS_G_System_LastSoftwareScan.LastScanDate, GetDate()) > 3)
What are you all using to do software removal base on software that is not used?
Greg Augustine
Office of Administration
Information Technology Services Division
301 W. High St.
Jefferson City, MO 65101
Office No: 573-751-4714
E-mail: [email protected]<mailto:[email protected]>
________________________________
Legal Notice: This email is intended only for the person(s) to whom it is
addressed. If you are not an intended recipient and have received this message
in error, please notify the sender immediately by replying to this email or
calling +44(0) 2083269015 (UK) or +1 866 592 4214 (USA). This email and any
attachments may be privileged and/or confidential. The unauthorized use,
disclosure, copying or printing of any information it contains is strictly
prohibited. The opinions expressed in this email are those of the author and do
not necessarily represent the views of 1E Ltd. Nothing in this email will
operate to bind 1E to any order or other contract.
________________________________
Confidentiality Notice: This e-mail is from a law firm and may be protected by
the attorney-client or work product privileges. If you have received this
message in error, please notify the sender by replying to this e-mail and then
delete it from your computer.
The information transmitted is intended only for the person or entity to which
it is addressed
and may contain CONFIDENTIAL material. If you receive this material/information
in error,
please contact the sender and delete or destroy the material/information.
The information transmitted is intended only for the person or entity to which
it is addressed
and may contain CONFIDENTIAL material. If you receive this material/information
in error,
please contact the sender and delete or destroy the material/information.
The information transmitted is intended only for the person or entity to which
it is addressed
and may contain CONFIDENTIAL material. If you receive this material/information
in error,
please contact the sender and delete or destroy the material/information.
The information transmitted is intended only for the person or entity to which
it is addressed
and may contain CONFIDENTIAL material. If you receive this
material/information in error,
please contact the sender and delete or destroy the material/information.
