Server & software query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Server & software query

Author

Topic

milagro
Starting Member

7 Posts

Posted - 2011-05-18 : 08:30:32

looking for some help with a query for all servers and MS software installed. Duplicate entries for each server is making it longer then necessary. I was trying to group the servers by name, so that the initial report is smaller and then be able to click on the server to see all the MS software installed.

SELECT DISTINCT RSYS.Netbios_Name0, RSYS.AD_Site_Name0, OPSYS.Caption0, OPSYS.Version0, OPSYS.CSDVersion0,ISC.ProductName0,
OPSYS.Description0
FROM v_FullCollectionMembership SYS
JOIN v_GS_OPERATING_SYSTEM OPSYS on SYS.ResourceID=OPSYS.ResourceID
JOIN v_R_System RSYS on
SYS.ResourceID=RSYS.ResourceID
JOIN v_GS_INSTALLED_SOFTWARE_CATEGORIZED ISC
on RSYS.ResourceID=ISC.ResourceID
WHERE OPSYS.Caption0 LIKE '%Server%'
AND SYS.CollectionID = 'PDC02288'
AND ISC.ProductName0 like 'Microsoft%'
GROUP BY RSYS.Netbios_Name0, RSYS.AD_Site_Name0
ORDER BY RSYS.Netbios_Name0,OPSYS.Caption0
When I run this, it errors out with a message that column'v_gs_operating_system.caption0' is invalid in the select list because it's not in an aggregate function or the GROUP BY clause.
Any help is appreciated, thanks.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-18 : 08:35:35

select * from
(
SELECT DISTINCT RSYS.Netbios_Name0, RSYS.AD_Site_Name0, OPSYS.Caption0, OPSYS.Version0, OPSYS.CSDVersion0,ISC.ProductName0,
OPSYS.Description0, seq = ROW_NUMBER() over (partition by RSYS.Netbios_Name0,RSYS.AD_Site_Name0 order by OPSYS.Caption0)
FROM v_FullCollectionMembership SYS
JOIN v_GS_OPERATING_SYSTEM OPSYS on SYS.ResourceID=OPSYS.ResourceID
JOIN v_R_System RSYS on
SYS.ResourceID=RSYS.ResourceID
JOIN v_GS_INSTALLED_SOFTWARE_CATEGORIZED ISC
on RSYS.ResourceID=ISC.ResourceID
WHERE OPSYS.Caption0 LIKE '%Server%'
AND SYS.CollectionID = 'PDC02288'
AND ISC.ProductName0 like 'Microsoft%'
) a
where seq = 1
ORDER BY RSYS.Netbios_Name0,OPSYS.Caption0

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

milagro
Starting Member

7 Posts

Posted - 2011-05-18 : 09:16:52

tried this but get "RSYS.Netbios_Name0 could not be bound. I am still working to correct this.

X002548
Not Just a Number

15586 Posts

Posted - 2011-05-18 : 09:28:18

instead of guessing..maybe you could post the DDL and some sample data in DML Form

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-18 : 09:51:36

Sorry - it's just the order by

select * from
(
SELECT DISTINCT RSYS.Netbios_Name0, RSYS.AD_Site_Name0, OPSYS.Caption0, OPSYS.Version0, OPSYS.CSDVersion0,ISC.ProductName0,
OPSYS.Description0, seq = ROW_NUMBER() over (partition by RSYS.Netbios_Name0,RSYS.AD_Site_Name0 order by OPSYS.Caption0)
FROM v_FullCollectionMembership SYS
JOIN v_GS_OPERATING_SYSTEM OPSYS on SYS.ResourceID=OPSYS.ResourceID
JOIN v_R_System RSYS on
SYS.ResourceID=RSYS.ResourceID
JOIN v_GS_INSTALLED_SOFTWARE_CATEGORIZED ISC
on RSYS.ResourceID=ISC.ResourceID
WHERE OPSYS.Caption0 LIKE '%Server%'
AND SYS.CollectionID = 'PDC02288'
AND ISC.ProductName0 like 'Microsoft%'
) a
where seq = 1
ORDER BY Netbios_Name0,Caption0

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

milagro
Starting Member

7 Posts

Posted - 2011-05-18 : 12:56:13

somehow I end up missing have of the servers. Going to start over to get the total number of servers and then maybe repost. appreciate the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources