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 <[email protected]> wrote: > That’s a decent idea.. I’ll see what I can get, I forget about doing that… > > > > *From:* [email protected] [mailto:listsadmin@lists. > myitforum.com] *On Behalf Of *Ed Aldrich > *Sent:* Wednesday, August 10, 2016 1:08 PM > *To:* [email protected] > *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 > > [email protected] | www.1e.com > > [image: Description: Description: cid:[email protected]] Ent > Cli Mgmt (2003-2016) > > > > > > > > *From:* [email protected] [mailto:listsadmin@lists. > myitforum.com <[email protected]>] *On Behalf Of *Mote, Todd > *Sent:* Wednesday, August 10, 2016 2:00 PM > *To:* [email protected] > *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:* [email protected] [mailto:listsadmin@lists. > myitforum.com <[email protected]>] *On Behalf Of *Robert > Spinelli > *Sent:* Tuesday, August 9, 2016 3:54 PM > *To:* [email protected] > *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:* [email protected] [mailto:listsadmin@lists. > myitforum.com <[email protected]>] *On Behalf Of *Robert > Spinelli > *Sent:* Tuesday, August 9, 2016 2:58 PM > *To:* [email protected] > *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:* [email protected] [mailto:listsadmin@lists. > myitforum.com <[email protected]>] *On Behalf Of *Daniel > Ratliff > *Sent:* Tuesday, August 9, 2016 1:39 PM > *To:* [email protected] > *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:* [email protected] [mailto:listsadmin@lists. > myitforum.com <[email protected]>] *On Behalf Of *Robert > Spinelli > *Sent:* Tuesday, August 09, 2016 1:13 PM > *To:* [email protected] > *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. > > > >
