Solved

SCCM Report Builder Query - Display Information on one row

Posted on 2013-06-25
11
1,387 Views
Last Modified: 2013-06-28
I have a custom report in SCCM 2012 created with Report Builder 2.0. The report pulls out all of the information I want, but it doesn't display the information for each machine the way I would like.

The report loops through the drives on machines in a specific collection, and returns the drive names (typically C: and D: in our environment with D: being a logical partition, and then return the size of the drive, free space and used space. The query below does all of that, except that it separates the C: and D: drive data into two rows for each pc. I tried 'inner join', but that didn't help. I'm not great with SQL.

=============================
SELECT SYS.Name, SYS.SiteCode, LDISK.DeviceID0, LDISK.Description0,  
LDISK.FreeSpace0, LDISK.Size0,
FROM v_FullCollectionMembership SYS
join v_GS_LOGICAL_DISK LDISK on SYS.ResourceID = LDISK.ResourceID
WHERE  
     LDISK.DriveType0 =3  AND
     LDISK.Size0 > 0  
     AND SYS.CollectionID = 'xxx00127'
ORDER BY SYS.Name, LDISK.DeviceID0
=============================

The main reason I would like this information on one row is that I am trying to do a simple calculation. I need to be able to verify if there is enough free space on C: to copy all of the used space or data on D: to C:? I think if I can get the information on one row I could make this calculation, but maybe there's an easier way? Regardless, displaying the information on one row would make the report look cleaner. I will take a simple query, anything to have this calculate rather than exporting to Excel and calculating it there.

Any help would be appreciated!
0
Comment
Question by:mnat1201
  • 7
  • 4
11 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39277089
I'm not familiar with the specific tools you are using, but what you want can be achieved in SQL - to do this I would ask for some sample data (from each table please) - just a small amount from each is fine. (csv or excel or paste it into a code block as text)

whilst you have included a query above, is this the one you actually use now? if not please add it too.

I may mock-up something if time permits, but it will be simpler for you if I use real table and field names (which is what I get by sample data by the way).
0
 

Author Comment

by:mnat1201
ID: 39277973
With the exception of the CollectionID which I am hardcoding into the query, the rest are all valid fields and calls to the OOB SCCM database, and the report returns the appropriate information correctly. Again, I would just like the data for one pc to be displayed on one line, rather than separate lines for each DeviceID0, and I would like to be able to do the calculation of subtracting used space on D:\ (2nd device) from free space on C:\ (first device). I have to produce a report or query in SCCM. I'm very close I think.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39278012
here is a mock-up
it is based on some wild assumptions (which you can correct via sample data)
essentially the "trick" is to have some method to distinguish between C & D drives
then,
C stuff goes into some columns
D stuff into other columns
group by machine (or whatever)
bingo, one row, with C & D columns

it may look something like this:
SELECT
       SYS.NAME
     , SYS.SiteCode
     , LDISK.DeviceID0
     , LDISK.Description0
     
-- assume C is LDISK.DriveType0 = 3
     , max(case when LDISK.DriveType0 = 3 then 'C:'              end) as c_letter
     , max(case when LDISK.DriveType0 = 3 then LDISK.FreeSpace0  end) as c_freespace
     , max(case when LDISK.DriveType0 = 3 then LDISK.Size0       end) as c_size

     
-- assume D is LDISK.DriveType0 = 4
     , max(case when LDISK.DriveType0 = 4 then 'D:'              end) as d_letter
     , max(case when LDISK.DriveType0 = 4 then LDISK.FreeSpace0  end) as d_freespace
     , max(case when LDISK.DriveType0 = 4 then LDISK.Size0       end) as d_size
     , max(case when LDISK.DriveType0 = 4 then LDISK.Size0       end)
       -
       max(case when LDISK.DriveType0 = 4 then LDISK.FreeSpace0  end) as d_used_space

     , (
       max(case when LDISK.DriveType0 = 4 then LDISK.Size0       end)
       -
       max(case when LDISK.DriveType0 = 4 then LDISK.FreeSpace0  end) 
       )
       /
       max(case when LDISK.DriveType0 = 3 then LDISK.FreeSpace0  end)  as d_into_c_freespace
       
FROM v_FullCollectionMembership SYS
INNER JOIN v_GS_LOGICAL_DISK LDISK ON SYS.ResourceID = LDISK.ResourceID
WHERE LDISK.Size0 > 0
     AND SYS.CollectionID = 'xxx00127'
GROUP BY
       SYS.NAME
     , SYS.SiteCode
     , LDISK.DeviceID0
     , LDISK.Description0
ORDER BY
       SYS.NAME
     , SYS.SiteCode
     , LDISK.DeviceID0
     , LDISK.Description0

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39278056
I can manipulate SQL to achieve what you want - but I don't know the "OOB SCCM database"

even if it is out of the box - "I don't got that box" :(

... a small sample of the data and the result can be produced
0
 

Author Comment

by:mnat1201
ID: 39279470
Thanks for the input. I'm beginning to wonder if my issue is more related to Report Builder than the SQL query. The field that seems pivotal to me isn't the LDISK.DriveType0, it's the LDISK.DeviceID0. I'm a programmer so I can speak to this in programming terms, but I don't know how to translate it into SQL.

It appears that either the query or Report Builder (or both) is doing a :

For Each DeviceID0 '(or drive letter)
     'collect the drive size and drive space data
     'populate the data into a single row in the table
Next

So what I'm ending up with is a report that shows the C: and D: drive space information on separate rows, when I really want lets say "DeviceID0(0)" (C:) AND "DeviceID0(1)" (D:) to display on the same row.

What I don't understand is how or where either the SQL query, or Report Builder, is deciding to enter these two sets of data in two rows, and how I can display the drive data for a given machine on one row? In other words, I can't figure out how, where or when the variables are set, named etc... to be able to distinguish them in Report Builder, or to run calculations against the different drive letters from the same pc.

To take this a step further (again in programming speak), I need to be able to subtract:

DeviceID0(0).FreeSpace0 - (DeviceID0(1).Size0 - DeviceID0(1).FreeSpace0)

This calculation should tell me if there is enough Free Space on C:, to copy or hold all of the data from D:.

Let me know if this makes sense? I'm definitely lost in Report Builder. I can admit that!
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 48

Expert Comment

by:PortletPaul
ID: 39280007
your for/next description is pretty accurate, just a slight alteration if I may:

For Each DeviceID0 '(or drive letter)
     'collect the drive size and drive space data
     'populate the data into a single row in the "resultset"
Next

think of the source tables as (quite big) "arrays" and when those "arrays" are "joined" it is like a matrix multiplication which produces a "resultset", something along these lines:

arrayM =
[blade1000,"name of this"]

arrayD =
[blade1000,"c",sizeC,usedC]
[blade1000,"d",sizeD,usedD]

arrayM * arrayD =
[blade1000,"name of this","c",sizeC,usedC]
[blade1000,"name of this","d",sizeD,usedD]

i.e. it is the way the arrays are defined that produces the multiple lines, what you are seeking is a method to alter those arrays, so we have to move some values, and reduce the rows, to produce the following:

[blade1000,"name of this","c",sizeC,usedC,"d",sizeD,usedD]

this is a 2 stage process

1. 'move' into new columns ("IF c then do this, else do that"), in SQL an "IF/ELSE" construct is achieved by using "CASE WHEN c then do this ELSE do that END"

this stage still has multiple rows, but gets new columns from the case expressions and at this stage the "array" would look like this:

[blade1000,"name of this","c",sizeC,usedC,     ,         ,          ]
[blade1000,"name of this",    ,        ,          ,"d",sizeD,usedD]

2. now reduce the rows. In sql this is achieved by using "group by", we take the elements that are common to those rows ( blade1000,"name of this" ) and then simplify for each of the other columns to arrive at a single row. For this need basically it will discard the null values seen above to produce this result:

[blade1000,"name of this","c",sizeC,usedC,"d",sizeD,usedD]

I do wish I had some relevant data to work with - it is so much simpler to show you than it is to describe.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39280045
please visit this URL: http://sqlfiddle.com/#!3/27963/7

There I have invented some sample data to demonstrate the explanation above, using the table and field names from your original query.

For the record here, this is the DDL/SQL
CREATE TABLE v_FullCollectionMembership
	([ResourceID] int, [NAME] varchar(13), [CollectionID] varchar(12), [SiteCode] varchar(10))
;
	
INSERT INTO v_FullCollectionMembership
	([ResourceID], [NAME], [CollectionID],[SiteCode])
VALUES
	(1001, 'A machine', 'xxx00127','Your Site')
;


CREATE TABLE v_GS_LOGICAL_DISK
	([ResourceID] int, [DeviceID0] varchar(5), [Description0] varchar(11), [FreeSpace0] int, [Size0] int, [DriveType0] int)
;
	
INSERT INTO v_GS_LOGICAL_DISK
	([ResourceID], [DeviceID0], [Description0], [FreeSpace0], [Size0], [DriveType0])
VALUES
	(1001, 'C', 'C drive', 400, 600, 3),
	(1001, 'D', 'D drive', 150, 300, 3)
;

-- 0. the existing query
SELECT
       SYS.NAME
     , SYS.SiteCode
     , LDISK.DeviceID0
     , LDISK.Description0
     , LDISK.FreeSpace0
     , LDISK.Size0
     , 'orig query' as note
FROM v_FullCollectionMembership SYS
INNER JOIN v_GS_LOGICAL_DISK LDISK ON SYS.ResourceID = LDISK.ResourceID
WHERE LDISK.DriveType0 = 3
     AND LDISK.Size0 > 0
     AND SYS.CollectionID = 'xxx00127'
ORDER BY
       SYS.NAME
     , LDISK.DeviceID0
;

-- 1. "MOVES VALUES TO NEW COLUMNS" (IF/ELSE using CASE expressions) 
SELECT
       SYS.NAME
     , SYS.SiteCode
     , case when LDISK.DeviceID0 = 'C' then LDISK.DeviceID0    end as C_DeviceID0
     , case when LDISK.DeviceID0 = 'C' then LDISK.Description0 end as C_Description0
     , case when LDISK.DeviceID0 = 'C' then LDISK.FreeSpace0   end as C_FreeSpace0
     , case when LDISK.DeviceID0 = 'C' then LDISK.Size0        end as C_Size0
     , case when LDISK.DeviceID0 = 'D' then LDISK.DeviceID0    end as D_DeviceID0
     , case when LDISK.DeviceID0 = 'D' then LDISK.Description0 end as D_Description0
     , case when LDISK.DeviceID0 = 'D' then LDISK.FreeSpace0   end as D_FreeSpace0
     , case when LDISK.DeviceID0 = 'D' then LDISK.Size0        end as D_Size0
     , 'more columns' as note
FROM v_FullCollectionMembership SYS
INNER JOIN v_GS_LOGICAL_DISK LDISK ON SYS.ResourceID = LDISK.ResourceID
WHERE LDISK.DriveType0 = 3
     AND LDISK.Size0 > 0
     AND SYS.CollectionID = 'xxx00127'
ORDER BY
       SYS.NAME
     , LDISK.DeviceID0
;

-- 2. "REDUCE ROWS" using GROUP BY (also requires "MAX( ... )" )
SELECT
       SYS.NAME
     , SYS.SiteCode
     , MAX( case when LDISK.DeviceID0 = 'C' then LDISK.DeviceID0    end ) as C_DeviceID0
     , MAX( case when LDISK.DeviceID0 = 'C' then LDISK.Description0 end ) as C_Description0
     , MAX( case when LDISK.DeviceID0 = 'C' then LDISK.FreeSpace0   end ) as C_FreeSpace0
     , MAX( case when LDISK.DeviceID0 = 'C' then LDISK.Size0        end ) as C_Size0
     , MAX( case when LDISK.DeviceID0 = 'D' then LDISK.DeviceID0    end ) as D_DeviceID0
     , MAX( case when LDISK.DeviceID0 = 'D' then LDISK.Description0 end ) as D_Description0
     , MAX( case when LDISK.DeviceID0 = 'D' then LDISK.FreeSpace0   end ) as D_FreeSpace0
     , MAX( case when LDISK.DeviceID0 = 'D' then LDISK.Size0        end ) as D_Size0
     , 'reduce rows' as note
FROM v_FullCollectionMembership SYS
INNER JOIN v_GS_LOGICAL_DISK LDISK ON SYS.ResourceID = LDISK.ResourceID
WHERE LDISK.DriveType0 = 3
     AND LDISK.Size0 > 0
     AND SYS.CollectionID = 'xxx00127'
GROUP BY
       SYS.NAME
     , SYS.SiteCode
ORDER BY
       SYS.NAME
     -- , LDISK.DeviceID0
;

Open in new window

0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39280063
and, a proposed solution (choice of 2 methods), these may be seen at: http://sqlfiddle.com/#!3/e0d50/2
-- subtracting used space on D:\ (2nd device) from free space on C:\ (first device)
-- method 1 using a nested query
select
      NAME
    , SiteCode
    , C_DeviceID0
    , C_Description0
    , C_FreeSpace0
    , C_Size0
    , D_DeviceID0
    , D_Description0
    , D_FreeSpace0
    , D_Size0

    , C_FreeSpace0 - (D_Size0 - D_FreeSpace0) as D_into_C

from (
        SELECT
               SYS.NAME
             , SYS.SiteCode
             , MAX( case when LDISK.DeviceID0 = 'C' then LDISK.DeviceID0    end ) as C_DeviceID0
             , MAX( case when LDISK.DeviceID0 = 'C' then LDISK.Description0 end ) as C_Description0
             , MAX( case when LDISK.DeviceID0 = 'C' then LDISK.FreeSpace0   end ) as C_FreeSpace0
             , MAX( case when LDISK.DeviceID0 = 'C' then LDISK.Size0        end ) as C_Size0
             , MAX( case when LDISK.DeviceID0 = 'D' then LDISK.DeviceID0    end ) as D_DeviceID0
             , MAX( case when LDISK.DeviceID0 = 'D' then LDISK.Description0 end ) as D_Description0
             , MAX( case when LDISK.DeviceID0 = 'D' then LDISK.FreeSpace0   end ) as D_FreeSpace0
             , MAX( case when LDISK.DeviceID0 = 'D' then LDISK.Size0        end ) as D_Size0
        FROM v_FullCollectionMembership SYS
        INNER JOIN v_GS_LOGICAL_DISK LDISK ON SYS.ResourceID = LDISK.ResourceID
        WHERE LDISK.DriveType0 = 3
             AND LDISK.Size0 > 0
             AND SYS.CollectionID = 'xxx00127'
        GROUP BY
               SYS.NAME
             , SYS.SiteCode
     ) as derived
ORDER BY
       NAME
;

Open in new window

-- method 2, without nested query
SELECT
        SYS.NAME
      , SYS.SiteCode
      , MAX( case when LDISK.DeviceID0 = 'C' then LDISK.DeviceID0    end ) as C_DeviceID0
      , MAX( case when LDISK.DeviceID0 = 'C' then LDISK.Description0 end ) as C_Description0
      , MAX( case when LDISK.DeviceID0 = 'C' then LDISK.FreeSpace0   end ) as C_FreeSpace0
      , MAX( case when LDISK.DeviceID0 = 'C' then LDISK.Size0        end ) as C_Size0
      , MAX( case when LDISK.DeviceID0 = 'D' then LDISK.DeviceID0    end ) as D_DeviceID0
      , MAX( case when LDISK.DeviceID0 = 'D' then LDISK.Description0 end ) as D_Description0
      , MAX( case when LDISK.DeviceID0 = 'D' then LDISK.FreeSpace0   end ) as D_FreeSpace0
      , MAX( case when LDISK.DeviceID0 = 'D' then LDISK.Size0        end ) as D_Size0

      , MAX( case when LDISK.DeviceID0 = 'C' then LDISK.FreeSpace0   end )
        - (
             MAX( case when LDISK.DeviceID0 = 'D' then LDISK.Size0 end )
             -
             MAX( case when LDISK.DeviceID0 = 'D' then LDISK.FreeSpace0 end )
          )
        as D_into_C

FROM v_FullCollectionMembership SYS
INNER JOIN v_GS_LOGICAL_DISK LDISK ON SYS.ResourceID = LDISK.ResourceID
WHERE LDISK.DriveType0 = 3
     AND LDISK.Size0 > 0
     AND SYS.CollectionID = 'xxx00127'
GROUP BY
        SYS.NAME
      , SYS.SiteCode

Open in new window

0
 

Author Comment

by:mnat1201
ID: 39284343
BINGO! Thanks so much for the explanation as I'm sure it's fairly basic SQL, I just didn't have the background. I use (similar) Case statements in programming though so I fully understand how to do this now.

The only edit I had to make to have the queries return the data was to change the

when LDISK.DeviceID0 = 'C' (or 'D') to

when LDISK.DeviceID0 = 'C:' (or 'D:')

I'm sure I could have trimmed it or something, but I have to learn how to do that sometime too!

Thanks again for your time and effort. The queries worked perfectly on first run. I'm using the second one for the report.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39284376
second one, good choice. the upper one is mostly for legibility really

changing 'C' to 'C:' etc. is the right thing to do, don't employ functions if you don't need to.

just one other thing, as devices might have more drives than just C: & D: you might want to include

WHERE LDISK.DriveType0 = 3
     AND LDISK.Size0 > 0
     AND SYS.CollectionID = 'xxx00127'
    AND LDISK.DeviceID0 in ('C:','D:')

happy reporting!

Cheers, Paul
0
 

Author Comment

by:mnat1201
ID: 39285692
Got it! Good tip.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now