I guess this is more SQL query help than DCM. I have a report that lets you
input a computer name and it displays a bunch of information like targeted
deployments, last scans, etc. I am adding DCM information to the report to
list what baselines and bundled items are targeting the machine.
Here is the current query:
SELECT v_R_System.Name0, v_LocalizedCIProperties.DisplayName,
v_LocalizedCIProperties.Description,
[v_BundledConfigurationItems_All.BundledLevel] = CASE
when v_BundledConfigurationItems_All.BundledLevel = '0' then 'Baseline'
else 'Configuration Item'
End
FROM v_BundledConfigurationItems_All INNER JOIN
v_LocalizedCIProperties ON
v_BundledConfigurationItems_All.BundledCI_ID = v_LocalizedCIProperties.CI_ID
INNER JOIN
v_R_System INNER JOIN
v_BaselineTargetedComputers ON v_R_System.ResourceID =
v_BaselineTargetedComputers.ResourceID ON
v_BundledConfigurationItems_All.CI_ID =
v_BaselineTargetedComputers.CI_ID
group by v_R_System.Name0, v_BundledConfigurationItems_All.CI_ID,
v_BundledConfigurationItems_All.BundledLevel,
v_BundledConfigurationItems_All.BundledCI_ID,
v_LocalizedCIProperties.DisplayName, v_LocalizedCIProperties.Description
order by v_R_System.Name0, v_BundledConfigurationItems_All.CI_ID
And the results come out likes this:
Name0
DisplayName
Description
v_BundledConfigurationItems_All.BundledLevel
computer
.Net Framework 4.5.2
Baseline
computer
.Net Framework 4.5.2
Configuration Item
computer
Force Full Hardware Inventory
Force full HW inventory on clients with a last hardware scan date older than 14
days.
Baseline
computer
Force Full Inventory On Clients
Configuration Item
computer
App-V 5 Client Service
Configuration Item
This is fine, but I want to be able to easily group the configuration items
within the baselines for prettier and easier to read reports. Something like
this:
Name0
DisplayName Baseline
DisplayName Configuration Item
Description
computer
.Net Framework 4.5.2
.Net Framework 4.5.2
computer
Force Full Hardware Inventory
Force Full Inventory On Clients
computer
Force Full Hardware Inventory
App-V 5 Client Service
Can anyone assist? I am sure it is a simple solution but just not seeing it
right now.
Eventually, I want to add the compliance state for the DCM, but I am happy I
got this far.
I am also working on a "clean" (removing of custom HW inventory items from
report) version of the report that others can use. Though it is very rough in
the queries side.
Michael Schultz
SCCM Engineer - Client Systems Engineering
Information Systems
Providence Health & Services
[email protected]<mailto:[email protected]>
________________________________
This message is intended for the sole use of the addressee, and may contain
information that is privileged, confidential and exempt from disclosure under
applicable law. If you are not the addressee you are hereby notified that you
may not use, copy, disclose, or distribute to anyone the message or any
information contained in the message. If you have received this message in
error, please immediately advise the sender by reply email and delete this
message.
