8 Replies Latest reply on Jul 24, 2015 3:23 PM by Steven.Mitchell

    Report of all Windows servers without a C: or D: drive being monitored

    Steven.Mitchell

      Hello,

       

      I'm trying to put together a custom SQL report that shows me all of our Windows systems missing a C:\ or D:\ drive. We have over 1,000 Windows systems, and I'd much rather find an efficient way to report this rather than having to check each and every one.

       

      I don't believe this is possible through the web console, but I have narrowed down the tables that store these details in the SQL DB.  My problem now is that I'm no DBA, so would greatly appreciate if anyone could help (or let me know if this is even possible).  Here is a sample query that I have which will tell me all Windows systems without a C:\ drive:

       

      SELECT Node.Caption
      FROM [dbo].[NodesData] AS Node
      LEFT JOIN [dbo].[Volumes] AS Vol
      ON Node.NodeID = Vol.NodeID
      WHERE Node.Vendor = 'Windows'
      AND Vol.VolumeType = 'Fixed Disk'
      AND Vol.VolumeDescription NOT LIKE 'C:\%'
      

       

      Where I'm getting tripped up is writing a query that shows any system that is missing a C:\ or D:\ volume.

       

      Thanks,

      Steven

       

      P.S. :

      We are currently running on NPM 11.5.1.

        • Re: Report of all Windows servers without a C: or D: drive being monitored
          zackm

          You were really close actually:

           

          SELECT Node.Caption 
          FROM [dbo].[NodesData] AS Node 
          LEFT JOIN [dbo].[Volumes] AS Vol 
          ON Node.NodeID = Vol.NodeID 
          WHERE Node.Vendor = 'Windows' 
          AND Vol.VolumeTypeID = '4' 
          AND Vol.VolumeDescription NOT LIKE 'C:\%'
          AND Vol.VolumeDescription NOT LIKE 'D:\%'
          

           

          I think what was throwing you off was that, in English, you want to find things where stuff is not like YYY or ZZZ

           

          In SQL, you are really saying that you want to find things where stuff is not like YYY and where stuff is not like ZZZ.

           

          Also, per this link (Volume Type "Network Disk" versus "NetworkDisk") I changed your Fixed Disk criteria to the VolumeTypeID, to avoid the intermittend spacing issue with the VolumeType string matching.

           

          Hope this helps!

           

          -ZackM

          Loop1 Systems: SolarWinds Training and Professional Services

            • Re: Report of all Windows servers without a C: or D: drive being monitored
              Steven.Mitchell

              Hi zackm, thanks for the reply!

               

              However, the query you provided is going to list any servers that have a C:\ OR a D:\ drive already.  I need to list any servers that are missing either a C:\ or D:\ drive.  For example, ServerX has a C:\ drive, but no D:\ drive being monitored. With your query, it will not provide ServerX in the results, because it matched "Vol.VolumeDescription NOT LIKE 'C:\%' ".

               

              Apologies if I wasn't clear in my initial post, but I need to find a way to list all Windows systems that are missing either C:\ or D:\; if they have both drives monitored, then I don't need to know about them.

               

              Thank you for the tip on using the VolumeTypID though, that's very helpful!

               

              Thank you,

              Steven M.

                • Re: Report of all Windows servers without a C: or D: drive being monitored
                  zackm

                  Roger that. Thanks for the clarification.

                   

                  So what you are asking for is more like this: Show me all the nodes that have either C or D, but not both C and D.

                   

                  So, to get there, what we want to do is create our own table (in memory) that has rows of nodes, and columns showing NodeID, C_Volume, and D_Volume. Then we are going to take that table and finally return only the results where either C or D is missing from the combination.

                   

                  So to start with, we want to create a derived table in our query like so:

                   

                  Step 1: Find me all fixed disk volumes that have a Caption starting with 'D:\%'

                   

                  select nodeid, caption
                  from volumes
                  where volumetypeid = 4
                  and caption like 'd:\%'
                  
                  

                   

                  2015-07-23_20-26-53.jpg

                   

                  Step 2: Join the results of this query with a query looking for all fixed disk volumes that have a Caption starting with 'C:\%' (and KEEP all of the NULL values as well)

                   

                  select
                    v.nodeid,
                    v.caption as 'c_volume',
                    x.caption as 'd_volume'
                  from volumes v
                  full outer join
                  (select nodeid, caption
                  from volumes
                  where volumetypeid = 4
                  and caption like 'd:\%') x on x.nodeid = v.nodeid
                  where v.volumetypeid = 4
                  and  v.caption like 'c:\%'
                  
                  

                   

                  2015-07-23_20-27-49.jpg

                   

                  Step 3: Take the results of this derived table and join it with the NodesData table, returning only Windows nodes that are missing either volume C or D. This should omit the 5 highlighted entries above that have both the C and D drives.

                   

                  select
                    n.nodeid,
                    tbl.c_volume,
                    tbl.d_volume
                  from nodesdata n
                  join
                    (select
                    v.nodeid,
                    v.caption as 'c_volume',
                    x.caption as 'd_volume'
                    from volumes v
                    full outer join
                    (select
                    nodeid,
                    caption
                    from volumes
                    where volumetypeid = 4
                    and caption like 'd:\%') x on x.nodeid = v.nodeid
                    where v.volumetypeid = 4
                    and  v.caption like 'c:\%') tbl on tbl.nodeid = n.nodeid
                  where n.vendor = 'windows'
                  and (tbl.c_volume is null or tbl.d_volume is null)
                  
                  

                   

                  2015-07-23_20-38-41.jpg

                   

                  Note that we now have only the results that did not have both C and D volumes being monitored...

                   

                  Step 4: Clean up the final query to return only the Server Names

                   

                  select
                    n.caption
                  from nodesdata n
                  join
                    (select
                    v.nodeid,
                    v.caption as 'c_volume',
                    x.caption as 'd_volume'
                    from volumes v
                    full outer join
                    (select
                    nodeid,
                    caption
                    from volumes
                    where volumetypeid = 4
                    and caption like 'd:\%') x on x.nodeid = v.nodeid
                    where v.volumetypeid = 4
                    and  v.caption like 'c:\%') tbl on tbl.nodeid = n.nodeid
                  where n.vendor = 'windows'
                  and (tbl.c_volume is null or tbl.d_volume is null)
                  order by n.caption
                  
                  

                   

                   

                  There you go.

                   

                  Hope this one suits your needs.

                   

                   

                  -ZackM

                  Loop1 Systems: SolarWinds Training and Professional Services