locked
SQL query help RRS feed

  • Question

  • Hi,

      I have been trying to find a solution to this reporting issue.

    We have loads of servers and each server has loads of shares.

    I need to get a report that has ONE row for each server where:

    If the server has a share called "WINDIST" then display in the "Path" column the path of the share.

    If the server does not have a share called "WINDIST" then display a blank in the "Path" column.

    The closest my query gets is to return two rows for every server that has a "WINDIST" share. That is by using a select distinct with a case statement to populate the path column. As servers with the "WINDIST" share also have other shares is why i end up with a duplicate row.

    Any hints on what sql query feature i could use to achieve my goal please?

    Thanks

    David Z

    Thursday, February 23, 2012 8:52 PM

Answers

All replies

  • Can you post the query that you have now and we can help you fix it.

    http://www.enhansoft.com/

    Friday, February 24, 2012 12:32 AM
  • Thanks - Here it is:

    SELECT Distinct v_R_System.Name0, v_GS_LOGICAL_DISK.FreeSpace0, v_GS_LOGICAL_DISK.Size0,WINDIST =
    CASE WHEN v_GS_SHARE.Name0 = 'WINDIST' THEN v_GS_SHARE.PATH0 ELSE '' END
    FROM v_R_System inner join v_GS_SERVICE on v_R_System.ResourceID = v_GS_SERVICE.ResourceID
    inner join v_GS_LOGICAL_DISK on  v_GS_LOGICAL_DISK.ResourceID = v_R_System.ResourceID
    inner join v_GS_SHARE on  v_GS_SHARE.ResourceID = v_R_System.ResourceID
    Where v_GS_SERVICE.DisplayName0 LIKE '%Windows Deployment Services%'
    AND v_GS_LOGICAL_DISK.Name0 = 'D:'
    ORDER BY v_R_System.Name0

    Cheers

    David Z

    Friday, February 24, 2012 1:04 AM
  • Try this.. http://tinyurl.com/76wald3

    http://www.enhansoft.com/

    • Marked as answer by David Zemdegs Friday, February 24, 2012 2:54 AM
    Friday, February 24, 2012 2:12 AM
  • Sir - you are a genius. Now all I have to do is understand what you've done!

    Cheers

    David Z

    Friday, February 24, 2012 2:55 AM
  • I wouldn’t say genius but I have done a few queries / reports. I can help you out with report tricks for that. :-) 

    I will be giving a webcast on SSRS Report Writing for CM07/CM12 on Wednesday, March 14, 2012, at  11:00 a.m. to 12:30 pm PST. 

    Episode 9: Garth Jones

    A Deep Dive into SCCM Data! Bring Your Snorkel!

    In this session, I will dive deep into the depths of ConfigMgr, and show you tricks that can be used for both SCCM 2007 and SCCM 2012!  When you swim with the sharks, the tips provided in this session will help you from being eaten alive. You will learn items such as how to create a template for SSRS reports that any manager will want!

    For more details and to register, see: http://bit.ly/yMimvp



    http://www.enhansoft.com/

    Friday, February 24, 2012 3:09 AM
  • Given that we all have to use SRS for 2012 then it would be good to get some tutorials...sadly 11am PST is not the best time in melbourne....
    Friday, February 24, 2012 3:43 AM
  • I'm sure it will be record and with any luck you can download it from iTune too.

    http://www.enhansoft.com/

    Friday, February 24, 2012 4:15 AM