![]() ![]() JOIN v_GS_COMPUTER_SYSTEM on SYS.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID JOIN v_RA_System_IPAddresses IPADDR on SYS.ResourceID = IPADDR.ResourceID Join v_GS_OPERATING_SYSTEM OPSYS on SYS.ResourceID=OPSYS.ResourceID Inner join V_GS_INSTALLED_SOFTWARE INSSW on SYS.ResourceID = INSSW.ResourceID Here is a start, i just copied someone elses computer oriented report and joined it to the installed software view SELECT Distinct SYS.User_Name0,SYS.Netbios_Name0,IPADDR.IP_Addresses0,OPSYS.Caption0 as C054,OPSYS.CSDVersion0,INSSW.ProductName0,INSSW.ProductVersion0 Quote by andhey "hmm ill try it later, i accidentally deleted all my tv shows when trying to delete a word document lmao" OR arp.displayname0 LIKE 'Microsoft Office XP%' )ĪND ( arp.displayname0 NOT LIKE '%update%' )ĪND ( arp.displayname0 NOT LIKE '%Microsoft Office XP Web Components' )ĪND ( dbo.v_r_system.operating_system_name_and0 NOT LIKE '%server%' ) OR arp.displayname0 LIKE 'Microsoft Office 2000%' OR arp.displayname0 LIKE '%Microsoft Office Professional%2010%' OR arp.displayname0 LIKE '%Microsoft Office Enterprise 2010%' ![]() OR arp.displayname0 LIKE '%Microsoft Office Standard 2010%' OR arp.displayname0 LIKE '%Microsoft Office Professional%2007%' OR arp.displayname0 LIKE '%Microsoft Office Enterprise 2007%' OR arp.displayname0 LIKE '%Microsoft Office Standard 2007%' WHERE ( arp.displayname0 LIKE '%Microsoft Office%edition%' ![]() ![]() Left JOIN ( select * from dbo.v_gs_network_adapter_configur where (dbo.v_gs_network_adapter_configur.IPAddress0 IS NOT NULL) AND (dbo.v_gs_network_adapter_configur.IPAddress0 NOT LIKE '0%') ) tblnetconf ON dbo.v_r_system.resourceid = ASSG.resourceid INNER JOIN dbo.v_ra_system_smsinstalledsites AS ASSG ON arp.resourceid = dbo.v_r_system.resourceid WHEN arp.version0 LIKE '.1000' THEN 'SP2' WHEN arp.version0 LIKE '.1000' THEN 'SP1' To be honest i dont see the merit of including the ip addresses in the report as by the time you are going to use it am sure some will have changed by DHCP.ĭbo.v_gs_operating_system.caption0 AS ,ĭbo.v_gs_operating_system.csdversion0 AS , I also did the filtering of the ip addresses before the join because some records in our database had Also i would suggest using alias's more often for your queries, makes it easier to read. Try this one, i found out that i was using the wrong view (doh!). ORDER BY dbo.v_R_System.Name0, arp.DisplayName0, arp.Version0 (arp.InstallDate0 NOT LIKE 'NULL') AND (NOT (dbo.v_GS_NETWORK_ADAPTER_CONFIGURATION.IPAddress0 LIKE N'NULL')) (arp.DisplayName0 NOT LIKE '%Microsoft Office XP Web Components') AND (dbo.v_R_System.Operating_System_Name_and0 NOT LIKE '%server%') WHERE (arp.DisplayName0 LIKE '%Microsoft Office%edition%' ORĪrp.DisplayName0 LIKE '%Microsoft Office Standard 2007%' ORĪrp.DisplayName0 LIKE '%Microsoft Office Enterprise 2007%' ORĪrp.DisplayName0 LIKE '%Microsoft Office Professional%2007%' ORĪrp.DisplayName0 LIKE '%Microsoft Office Standard 2010%' ORĪrp.DisplayName0 LIKE '%Microsoft Office Enterprise 2010%' ORĪrp.DisplayName0 LIKE '%Microsoft Office Professional%2010%' ORĪrp.DisplayName0 LIKE 'Microsoft Office 2000%' ORĪrp.DisplayName0 LIKE 'Microsoft Office XP%') AND (arp.DisplayName0 NOT LIKE '%update%') AND TOP (100) PERCENT dbo.v_R_System.Name0, dbo.v_R_System.User_Name0, dbo.v_GS_OPERATING_SYSTEM.Caption0 AS ,ĭbo.v_GS_OPERATING_SYSTEM.CSDVersion0 AS, arp.DisplayName0,ĬASE WHEN arp.version0 LIKE '.0' THEN 'SP1' WHEN arp.version0 LIKE '.0' THEN 'SP2' WHEN arp.version0 LIKE '.0'Īrp.version0 LIKE '.1000' THEN 'SP1' WHEN arp.version0 LIKE '.1000' THEN 'SP2' WHEN arp.version0 LIKE '.1000'ĪS 'Service Pack', arp.Version0, dbo.v_GS_NETWORK_ADAPTER_CONFIGURATION.IPAddress0įROM dbo.v_Add_Remove_Programs AS arp INNER JOINĭbo.v_R_System ON arp.ResourceID = dbo.v_R_System.ResourceID INNER JOINĭbo.v_RA_System_SMSInstalledSites AS ASSG ON dbo.v_R_System.ResourceID = ASSG.ResourceID INNER JOINĭbo.v_GS_OPERATING_SYSTEM ON dbo.v_R_System.ResourceID = dbo.v_GS_OPERATING_SYSTEM.ResourceID INNER JOINĭbo.v_GS_NETWORK_ADAPTER_CONFIGURATION ON dbo.v_R_System.ResourceID = dbo.v_GS_NETWORK_ADAPTER_CONFIGURATION.ResourceID Try this (Note: It is only tested on a SCCM 2012 Site) ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |