Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to write a report where I grab data showing all server collections with maintenance windows. How I want to do this is to show only those server collections where those computers are also members of my "All Server" collection as well. So as to identify them as servers. I cannot use the OS caption like '%Server%' because some of these computers are also Windows 7, but are being used as a server role.

Here is the code I came up with, but the output is blank. Any help would be very much appreiated. Thank you very much

Select Distinct
    v_Collection.CollectionID as [Collection ID],
    v_Collection.Name as [Collection Name],
    v_ServiceWindow.Description as [Maintenance Window Description], 
    v_ServiceWindow.StartTime as [Maintenance Window Start Time], 
    v_ServiceWindow.Duration as [Maintenance Window Duration]
From v_Collection Join v_ServiceWindow on 
        v_ServiceWindow.CollectionID = v_Collection.CollectionID
Join v_FullCollectionMembership on 
        v_FullCollectionMembership.CollectionID = v_ServiceWindow.CollectionID 
Where v_FullCollectionMembership.Name In
        (
         Select Distinct v_Collection.CollectionID
         From v_Collection
         Where v_Collection.CollectionID = 'XYZ'
        )
ORDER BY [Collection ID];
share|improve this question
add comment

2 Answers 2

If you run the following, do you see your servers?

Select Distinct
v_Collection.CollectionID as [Collection ID],
v_Collection.Name as [Collection Name],
v_ServiceWindow.Description as [Maintenance Window Description], 
v_ServiceWindow.StartTime as [Maintenance Window Start Time], 
v_ServiceWindow.Duration as [Maintenance Window Duration]
From v_Collection Join v_ServiceWindow on 
        v_ServiceWindow.CollectionID = v_Collection.CollectionID
Join v_FullCollectionMembership on 
        v_FullCollectionMembership.CollectionID = v_ServiceWindow.CollectionID 
ORDER BY [Collection ID];

If so, your WHERE clause is eliminating the desired servers. Run the following to determine the name of the collection to use in your WHERE clause:

         Select Distinct v_Collection.CollectionID, v_Collection.*
         From v_Collection;
share|improve this answer
    
Yeah that is what I figured, I just needed some confirmation on this. Also, thanks for the code above, but this is basically the same code to a report that I already wrote showing all maintenance windows. I wrote another report that seemed to do what I needed, but I am not using the Collection ID, rather I am using standard keywords within the collection naming convention. –  The Ratzenator Oct 2 '13 at 20:54
add comment

Oops, I forgot to put the code I used, sorry about that. Here is the code that worked for me:

SELECT

v_FullCollectionMembership.Name as [Computername],
v_Collection.Name as [Collections With Maintenance Windows], 
v_ServiceWindow.Description as [Maintenance Description], 
v_ServiceWindow.StartTime as [Maintenance Window Start Time], 
v_ServiceWindow.Duration as [Maintenance Window Duration],
v_GS_OPERATING_SYSTEM.Caption0 as [Operating System]

FROM

v_ServiceWindow  
Join 
v_Collection on v_Collection.CollectionID = v_ServiceWindow.CollectionID
Join
v_FullCollectionMembership on v_FullCollectionMembership.CollectionID = v_ServiceWindow.CollectionID
Join
v_GS_OPERATING_SYSTEM on v_GS_OPERATING_SYSTEM.ResourceID = v_FullCollectionMembership.ResourceID
Join
v_R_System on v_R_System.ResourceID = v_FullCollectionMembership.ResourceID

WHERE

(v_GS_OPERATING_SYSTEM.Caption0 Like '%Server%')
or
(v_FullCollectionMembership.Name Like 'WN%')

ORDER BY

[Computername]
share|improve this answer
    
By the way, I used the "WN" for v_FUllCollectionMembership.Name because my servers computernames begin with "WN". –  The Ratzenator Oct 16 '13 at 20:59
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.