gimme a sec,
On Wed, Aug 10, 2016 at 2:45 PM, Mote, Todd <mo...@austin.utexas.edu> wrote: > Well it looks like it uses a function that I can’t figure out how to call > correctly in SSMS. SMSProv.log has these: > > > > Execute SQL =select all __ymentCompliantAssetDetails0.AssetID,__ > ymentCompliantAssetDetails0.AssetName,__ymentCompliantAssetDetails0. > AssetType,__ymentCompliantAssetDetails0.AssignmentID,__ > ymentCompliantAssetDetails0.AssignmentUniqueID,__ > ymentCompliantAssetDetails0.BL_ID,__ymentCompliantAssetDetails0.BLName,__ > ymentCompliantAssetDetails0.BLRevision,__ymentCompliantAssetDetails0. > CI_ID,__ymentCompliantAssetDetails0.CIName,__ymentCompliantAssetDetails0. > ClientType,__ymentCompliantAssetDetails0.IsMachineAssignedToUser,__ > ymentCompliantAssetDetails0.IsMachineChangesPersisted,__ > ymentCompliantAssetDetails0.IsVM,__ymentCompliantAssetDetails0.Revision,__ > ymentCompliantAssetDetails0.StatusType,__ymentCompliantAssetDetails0. > TargetCollectionID,__ymentCompliantAssetDetails0.VMHostName from fn_ > DCMDeploymentCompliantAssetDetails(1033) AS > __ymentCompliantAssetDetails0 where > __ymentCompliantAssetDetails0.AssignmentID > = 16777877 > > > > The WQL is simpler though > > > > Execute WQL =SELECT * FROM SMS_DCMDeploymentCompliantAssetDetails WHERE > AssignmentID=16777877 > > > > Both fn_DCMDeploymentCompliantAssetDetails and SMS_ > DCMDeploymentCompliantAssetDetails report as invalid object names in SSMS. > > > > > > *From:* listsad...@lists.myitforum.com [mailto:listsadmin@lists. > myitforum.com] *On Behalf Of *Todd Hemsell > *Sent:* Wednesday, August 10, 2016 2:22 PM > *To:* mssms@lists.myitforum.com > *Subject:* Re: [mssms] RE: Compliance Status Query ? > > > > you will have to get the data and then make case statements for the actual > statuses. Most of them are stored as an array in WMI and not actually in > the database at all. > > > > On Wed, Aug 10, 2016 at 2:10 PM, Mote, Todd <mo...@austin.utexas.edu> > wrote: > > That’s a decent idea.. I’ll see what I can get, I forget about doing that… > > > > *From:* listsad...@lists.myitforum.com [mailto:listsadmin@lists. > myitforum.com] *On Behalf Of *Ed Aldrich > *Sent:* Wednesday, August 10, 2016 1:08 PM > *To:* mssms@lists.myitforum.com > *Subject:* RE: [mssms] RE: Compliance Status Query ? > > > > Can you maybe use the old trick of monitoring the SMSPROV thread to > capture the actual SQL being used to render the data you are seeing in the > console? > > > > *[image: Ed]* > > *Ed Aldrich* > > Mobile: (401) 924-2293 > > ed.aldr...@1e.com | www.1e.com > > [image: Description: Description: cid:image011.png@01CAD56A.EFDE3F90] Ent > Cli Mgmt (2003-2016) > > > > > > > > *From:* listsad...@lists.myitforum.com [mailto:listsadmin@lists. > myitforum.com <listsad...@lists.myitforum.com>] *On Behalf Of *Mote, Todd > *Sent:* Wednesday, August 10, 2016 2:00 PM > *To:* mssms@lists.myitforum.com > *Subject:* RE: [mssms] RE: Compliance Status Query ? > > > > Along these lines, anybody got an SSRS report that has the info the > Deployment Summary does in the console for compliance baselines? It has > all the info anybody’d want. I’m playing around with GPO backup from GPMC, > import that into Security Compliance Manager as a Baseline, then export > that out to SCCM 2007 DCM, then import that CAB into SCCM (2012 R2 SP1 > right now). It works pretty well, however there are some issues. This is > my first attempt at something like this, but if I could produce a report > that can be visited by a technician on a webpage rather than the console > and provide all this info, that’d be awesome. Anybody every do anything > like this? > > > > Todd > > > > > > > > > > *From:* listsad...@lists.myitforum.com [mailto:listsadmin@lists. > myitforum.com <listsad...@lists.myitforum.com>] *On Behalf Of *Robert > Spinelli > *Sent:* Tuesday, August 9, 2016 3:54 PM > *To:* mssms@lists.myitforum.com > *Subject:* RE: [mssms] RE: Compliance Status Query ? > > > > This is getting me pretty much what I want. > > > > It lists all the machines that aren’t running because of the OS > requirement not being checked on the item. > > > > select sys.ResourceID, sys.Netbios_Name0, os.Caption0, os.CSDVersion0, dcm > .* > > from v_r_system sys > > join vDCMDeploymentNonCompliantAssetDetails dcm on sys.ResourceID = dcm. > AssetID > > join v_GS_OPERATING_SYSTEM os on sys.ResourceID = os.ResourceID > > where dcm.BL_ID = '140135' --and ci_id = '140134' > > and dcm.IsBaselineRule = '1' > > > > Down and dirty, but gets me what I want. > > > > Thanks for sending that info over, helped me figure it out. > > > > Rob > > > > > > *From:* listsad...@lists.myitforum.com [mailto:listsadmin@lists. > myitforum.com <listsad...@lists.myitforum.com>] *On Behalf Of *Robert > Spinelli > *Sent:* Tuesday, August 9, 2016 2:58 PM > *To:* mssms@lists.myitforum.com > *Subject:* RE: [mssms] RE: Compliance Status Query ? > > > > Thanks, this should point me in right direction. > > > > Weird that view though only seems to have items that are non-compliant. > You would think if it’s called CIComplianceStatusDetail it would have both. > > > > *From:* listsad...@lists.myitforum.com [mailto:listsadmin@lists. > myitforum.com <listsad...@lists.myitforum.com>] *On Behalf Of *Daniel > Ratliff > *Sent:* Tuesday, August 9, 2016 1:39 PM > *To:* mssms@lists.myitforum.com > *Subject:* [mssms] RE: Compliance Status Query ? > > > > Here is a start. Its every device and its compliance state. Easy to filter > on what you want. > > > > select cicsd.Netbios_Name0 [Name], cicsd.ci_id [CI ID], > cicsd.configurationitemname > [CI], CICSD.Criteria, cicsd.CurrentValue, cicsd.LastComplianceMessageTime > [Last Message] > > from v_R_System SYS join > > v_CIComplianceStatusDetail CICSD on sys.resourceid = cicsd. > ResourceID > > > > > > *Daniel Ratliff* > > > > *From:* listsad...@lists.myitforum.com [mailto:listsadmin@lists. > myitforum.com <listsad...@lists.myitforum.com>] *On Behalf Of *Robert > Spinelli > *Sent:* Tuesday, August 09, 2016 1:13 PM > *To:* mssms@lists.myitforum.com > *Subject:* [mssms] Compliance Status Query ? > > > > Anyone have a query they use to report back what CI in a baseline is > causing the baseline to be non-compliant? > > > > If you have a baseline with OS requirements selected that comes back as > non-compliant and then you have CI in that baseline come back as complaint, > the whole baseline is non-complaint (correct). I can’t seem to figure out > how to get a query to show me which of the items in the baseline is causing > the baseline to be non-compliant. > > > > Thanks > > > > Rob > > > > > 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. > > > > > > > > > > > ------------------------------ > > > > 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. > > > > > > > > > >
