oh, you know, I assumed you wanted it as a collection query. if all you want is
a sql report, then...
select s1.netbios_name0, ucs.ci_id, ucs.status,case when ucs.status=3 then
'installed'when ucs.status=2 then 'required/missing'else 'something
different'end as 'result',ui.title, ui.articleid,count(*)from
v_updateComplianceStatus ucsjoin v_r_system s1 on
s1.resourceid=ucs.resourceidjoin v_updateinfo ui on ui.ci_id=ucs.ci_idjoin
v_FullCollectionMembership fcm on fcm.resourceid=ucs.resourceidwhere
ui.ArticleID='3068708'and
fcm.collectionid='ThatcollectionIDYouWantedToLookAt'and ucs.status<>2 --well,
you could leave this out I guessorder by ucs.status
On Wednesday, January 6, 2016 2:35 PM, Sherry Kissinger
<[email protected]> wrote:
Ok... I *WAS* going to save this for MMS May 2016; so if any of you attend,
pretend to be surprised if you see this there.
Several Steps needed, BUT... you already have the data in state messages from
your clients. It's just not simple to get from point a to point c. I'm not a
super-fan of enabling QFE if I don't have to.
step 1) You need to figure out what ci_id YOUR environment has for that
particular ArticleID. AND which one of those Ci_id's for that article actually
reflect the ones that are installed. So first thing's first... make a
collection (or have one already) where you know boxes in that collection have
KB3068708. Take note of the collection ID; and you'll want to insert it in
this SQL query, which you run against your CM_xxx database:
select ucs.ci_id, ucs.status,case when ucs.status=3 then 'installed'when
ucs.status=2 then 'required/missing'else 'something different'end as
'result',ui.title, ui.articleid,count(*)from v_updateComplianceStatus ucsjoin
v_updateinfo ui on ui.ci_id=ucs.ci_idjoin v_FullCollectionMembership fcm on
fcm.resourceid=ucs.resourceidwhere ui.ArticleID='3068708'and
fcm.collectionid='ThatcollectionIDYouWantedToLookAt'and ucs.status<>2 --well,
you could leave this out I guessgroup by ucs.ci_id, ucs.status, ui.title,
ui.articleidorder by ucs.status
Step 2: Confirm the Title, ArticleID and make sure you know EXACTLY the ci_id
you want to make the collection query for. This is unique to your environment,
for that update.
Step 3: Back in your Console, make a collection query, and you want it to look
sorta like this. i.e., if for you, your CI_ID that you care about is 113322,
then it would be this:
Select SMS_R_System.ResourceID from SMS_R_SystemWhere resourceid in ( Select
MachineID from SMS_UpdateComplianceStatus Where CI_ID=113322 and Status=3)
Which would be machines with status of 3 (installed), for that very specific
update. So now you have a collection of machines with that update installed.
Tip: because this is based on "state message" type information DO NOT MAKE IT
AN INCREMENTAL COLLECTION. You will likely make all collection processing
backlog and just tip over and kill your site. Do NOT check that box about
incremental. Only let the collection update daily or something even more
infrequent. Also, make yourself a note to remind yourself to delete this
collection in a month or something. Again, it's kind of "cool" that we "can"
do this--but it's really not that awesome to have too many of them out there.
Don't be crazy.
On Wednesday, January 6, 2016 1:22 PM, the codepoets
<[email protected]> wrote:
Does the report "Compliance 2 - Specific software update" not meet your needs?
I use it to hunt down patches.
-e
On Wed, Jan 6, 2016 at 9:37 AM, Michael Timm <[email protected]> wrote:
All, I have been tasked with hunting down workstations that have certain
unwanted windows KB’s installed. I have tried creating a collection based on a
WQL query but it doesn’t return any results. If I recall windows updates aren’t
listed in programs and features any longer. So that may be why there aren’t any
results. 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_64 on
SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceId = SMS_R_System.ResourceId where
SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName like "%KB3068708%" or
SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName like "%KB3022345%" or
SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName like "%KB3075249%" or
SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName like "%KB3080149%" I’ve also
tried a configuration baseline based off of info listed
in:https://www.windows-noob.com/forums/topic/8640-sccm-2012-search-for-installed-hotfixes/
I am applying that against a test collection that contains both machine that
have them installed, and machines that don’t have them installed. The
deployment results show 100% compliant, which aren’t the results I am
expecting. Can anyone offer up any advice on what I’m doing wrong, or how I
could go about finding them? Thanks!Mike TimmSystems EngineerOverstock.com
CONFIDENTIALITY NOTICE: This message is intended only for the use and review of
the individual or entity to which it is addressed and may contain information
that is privileged and confidential. If the reader of this message is not the
intended recipient, or the employee or agent responsible for delivering the
message solely to the intended recipient, you are hereby notified that any
dissemination, distribution or copying of this communication is strictly
prohibited. If you have received this communication in error, please notify
sender immediately by telephone or return email. Thank you.
