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

Hello,

I'm trying to put together a custom SQL report that shows me all of our Windows systems missing a C:\ or \ 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.CaptionFROM [dbo].[NodesData] AS NodeLEFT JOIN [dbo].[Volumes] AS VolON Node.NodeID = Vol.NodeIDWHERE 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 \ volume.

Thanks,

Steven

P.S. :

We are currently running on NPM 11.5.1.

Find more posts tagged with

sql_report

disk_drives

sql query

Windows

report

Accepted answers

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, captionfrom volumeswhere volumetypeid = 4and caption like 'd:\%'

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 vfull outer join(select nodeid, captionfrom volumeswhere volumetypeid = 4and caption like 'd:\%') x on x.nodeid = v.nodeidwhere v.volumetypeid = 4and  v.caption like 'c:\%'

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_volumefrom nodesdata njoin  (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.nodeidwhere n.vendor = 'windows'and (tbl.c_volume is null or tbl.d_volume is null)

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.captionfrom nodesdata njoin  (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.nodeidwhere 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

LinkedIN: Loop1 Systems
Facebook: Loop1 Systems
Twitter: @Loop1Systems

All comments

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

LinkedIN: Loop1 Systems
Facebook: Loop1 Systems
Twitter: @Loop1Systems

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 \ drive already. I need to list any servers that are missing either a C:\ or \ drive. For example, ServerX has a C:\ drive, but no \ 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 \; 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.

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 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, captionfrom volumeswhere volumetypeid = 4and caption like 'd:\%'

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 vfull outer join(select nodeid, captionfrom volumeswhere volumetypeid = 4and caption like 'd:\%') x on x.nodeid = v.nodeidwhere v.volumetypeid = 4and  v.caption like 'c:\%'

select  n.nodeid,  tbl.c_volume,  tbl.d_volumefrom nodesdata njoin  (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.nodeidwhere n.vendor = 'windows'and (tbl.c_volume is null or tbl.d_volume is null)

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.captionfrom nodesdata njoin  (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.nodeidwhere 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

LinkedIN: Loop1 Systems
Facebook: Loop1 Systems
Twitter: @Loop1Systems

bluefunelemental

I don't know Zack - you had me through step 3 but then I figured this could be done as a single step join. This is just some thought without trying the actual query...

Select distinct nodeid, caption from nodes

Left join volumes v1 on v1.nodeid=nodes.nodeid and v1.volumetype = 'fixed disk' and v1.volumename like 'C:/%'

Left join volumes v2 on v2.nodeid=nodes.nodeid and v2.volumetype = 'fixed disk' and v2.volumename like 'D:/%'

Where v1.volumeid is null

And v2.volumeid is null

This join once cleaned up should result in nodes which do not have either a C or D drive. No?