Declare @MyOUDomain as VarChar(50) Set @MyOUDomain = ',DC=Domain,DC=Com' -- used in separation out OU data select distinct VRS.ResourceID --APP stuff --apps.DisplayName [SCCM_AppName] --count(SF.FileName) [Count], --,SF.FileName,sf.filepath,sf.FileSize,sf.Fileversion --mail.Largest_OST_File --ie.ExecutableName0 --,ie.FileVersion0-- ,vrs.Netbios_Name0 --,s.ARPDisplayName0, s.productversion0, s.InstalledLocation0,s.packagecode0,s.softwarecode0,InstallSource0 --,REPLACE(SF.FileName,',',' ') [FileName],REPLACE(sf.filepath,',',' ') [PAth],sf.FileSize,REPLACE(sf.Fileversion,',',' ') [Version] --qfe.HotFixID0 --, qfe.InstallDate0 --User stuff ,REPLACE(REPLACE(vru.displayname0,' ,','|'),',','|') [User_Name] , vru.mail0 , vru.employeeID --, vru.EmployeeType0 , REPLACE(SUBSTRING(vru.manager,4,CHARINDEX(',OU',vru.manager,3)-4),'\,','|') [Manager] --, vru.telephoneNumber0 --, vru.ipphone , Replace(vru.physicalDeliveryOfficeNam,',','.') as User_Physical_Office , REPLACE(replace(vru.Full_User_Name0,' ,','|'),',','|') [Full Name] ,vru.User_name0 , CASE WHEN U.TopConsoleUser0 = '-1' OR U.TopConsoleUser0 IS NULL THEN 'N/A' ELSE U.TopConsoleUser0 END as TopUser --, vru.businessCategory0 [Category] , REPLACE(replace(vru.department0,' ,','|'),',','|') [Department] , vru.title0 ,parsename(Replace(Replace(Replace(Replace(Replace(substring(VRS.Distinguished_Name0,charindex(',OU',VRS.Distinguished_Name0)+1,len(VRS.Distinguished_Name0)-charindex(',OU',VRS.Distinguished_Name0)),',OU=Locations',''),@MyOUDomain,''),',OU','.OU'),'OU=Machines.',''),'OU=',''),4) as [ou 4] ,parsename(Replace(Replace(Replace(Replace(Replace(substring(VRS.Distinguished_Name0,charindex(',OU',VRS.Distinguished_Name0)+1,len(VRS.Distinguished_Name0)-charindex(',OU',VRS.Distinguished_Name0)),',OU=Locations',''),@MyOUDomain,''),',OU','.OU'),'OU=Machines.',''),'OU=',''),3) as [ou 3] ,parsename(Replace(Replace(Replace(Replace(Replace(substring(VRS.Distinguished_Name0,charindex(',OU',VRS.Distinguished_Name0)+1,len(VRS.Distinguished_Name0)-charindex(',OU',VRS.Distinguished_Name0)),',OU=Locations',''),@MyOUDomain,''),',OU','.OU'),'OU=Machines.',''),'OU=',''),2) as [ou 2] ,parsename(Replace(Replace(Replace(Replace(Replace(substring(VRS.Distinguished_Name0,charindex(',OU',VRS.Distinguished_Name0)+1,len(VRS.Distinguished_Name0)-charindex(',OU',VRS.Distinguished_Name0)),',OU=Locations',''),@MyOUDomain,''),',OU','.OU'),'OU=Machines.',''),'OU=',''),1) as [ou 1] --computer Stuff ,bios.SerialNumber0 , year(CONVERT(DATE,BIOS.ReleaseDate0)) AS S_BIOSDate , vrs.Netbios_Name0 --, nlp.LastLogon0 --, nlp.TimeStamp --, nlp.Name0 as ['last user logged on'] --,vrs.User_Name0 , os.Caption0 , os.BuildNumber0 ,'OS Build Number' = CASE WHEN (OS.Caption0 = 'Microsoft Windows 10 Enterprise' or OS.Caption0 = 'Microsoft Windows 10 Pro') and (OS.BuildNumber0 = '10240') THEN '1507' WHEN (OS.Caption0 = 'Microsoft Windows 10 Enterprise' or OS.Caption0 = 'Microsoft Windows 10 Pro') and (OS.BuildNumber0 = '10586') THEN '1511' WHEN (OS.Caption0 = 'Microsoft Windows 10 Enterprise' or OS.Caption0 = 'Microsoft Windows 10 Pro') and (OS.BuildNumber0 = '14393') THEN '1607' WHEN (OS.Caption0 = 'Microsoft Windows 10 Enterprise' or OS.Caption0 = 'Microsoft Windows 10 Pro') and (OS.BuildNumber0 = '15063') THEN '1703' WHEN (OS.Caption0 = 'Microsoft Windows 10 Enterprise' or OS.Caption0 = 'Microsoft Windows 10 Pro') and (OS.BuildNumber0 = '16299') THEN '1709' WHEN (OS.Caption0 = 'Microsoft Windows 10 Enterprise' or OS.Caption0 = 'Microsoft Windows 10 Pro') and (OS.BuildNumber0 = '17134') THEN '1803' WHEN (OS.Caption0 = 'Microsoft Windows 10 Enterprise' or OS.Caption0 = 'Microsoft Windows 10 Pro') and (OS.BuildNumber0 = '17763') THEN '1809' WHEN (OS.Caption0 = 'Microsoft Windows 10 Enterprise' or OS.Caption0 = 'Microsoft Windows 10 Pro') and (OS.BuildNumber0 = '18362') THEN '1903' WHEN (OS.Caption0 = 'Microsoft Windows 10 Enterprise' or OS.Caption0 = 'Microsoft Windows 10 Pro') and (OS.BuildNumber0 = '18363') THEN '1909' WHEN (OS.Caption0 = 'Microsoft Windows 10 Enterprise' or OS.Caption0 = 'Microsoft Windows 10 Pro') and (OS.BuildNumber0 = '18912') THEN '1909' WHEN (OS.Caption0 = 'Microsoft Windows 10 Enterprise' or OS.Caption0 = 'Microsoft Windows 10 Pro') and (OS.BuildNumber0 = '19042') THEN '20H2' WHEN (OS.Caption0 = 'Microsoft Windows 10 Enterprise' or OS.Caption0 = 'Microsoft Windows 10 Education') and (OS.BuildNumber0 = '19042') THEN '20H2' WHEN (OS.Caption0 = 'Microsoft Windows 10 Enterprise' or OS.Caption0 = 'Microsoft Windows 10 Pro') and (OS.BuildNumber0 = '19043') THEN '21H1' WHEN (OS.Caption0 = 'Microsoft Windows 10 Enterprise' or OS.Caption0 = 'Microsoft Windows 10 Pro') and (OS.BuildNumber0 = '19044') THEN '21H2' WHEN (OS.Caption0 = 'Microsoft Windows 10 Enterprise' or OS.Caption0 = 'Microsoft Windows 10 Pro') and (OS.BuildNumber0 = '19045') THEN '22H2' WHEN (OS.Caption0 = '?????????? Windows 10 ?????????????' ) and (OS.BuildNumber0 = '18362') THEN '1903' WHEN (OS.Caption0 = 'Microsoft Windows 7 Enterprise' or OS.Caption0 = 'Microsoft Windows 7 Professional') and (OS.BuildNumber0 = '7601') THEN 'With SP1' WHEN (OS.Caption0 = 'Microsoft Windows 7 Enterprise' or OS.Caption0 = 'Microsoft Windows 7 Professional') and (OS.BuildNumber0 = '7600') THEN 'Without SP1' ELSE OS.BuildNumber0 END ,CASE WHEN OS.Locale0 = '041e' THEN 'Thai/Thailand/th-TH' WHEN OS.Locale0 = '2c0a' THEN 'Spanish/Argentina/es-AR' WHEN OS.Locale0 = '0409' THEN 'English/United States/en-US' WHEN OS.Locale0 = '1009' THEN 'English/Canada/en-CA' WHEN OS.Locale0 = '4409' THEN 'English/Malaysia/en-MY' WHEN OS.Locale0 = '0c09' THEN 'English/Australia/en-AU' WHEN OS.Locale0 = '3809' THEN 'English/Indonesia/en-ID' WHEN OS.Locale0 = '0416' THEN 'Portuguese/Brazil/pt-BR' WHEN OS.Locale0 = '0419' THEN 'Russian/Russia/ru-RU' WHEN OS.Locale0 = '0816' THEN 'Portuguese/Portugal/pt-PT' WHEN OS.Locale0 = '280a' THEN 'Spanish/Peru/es-PE' WHEN OS.Locale0 = '0804' THEN 'Chinese (Simplified)/Peoples Republic of China/zh-CN' WHEN OS.Locale0 = '240a' THEN 'Spanish/Colombia/es-CO' WHEN OS.Locale0 = '0c0a' THEN 'Spanish/Spain/es-ES' WHEN OS.Locale0 = '080A' THEN 'Spanish/Mexico/es-MX' WHEN OS.Locale0 = '400a' THEN 'Spanish/Bolivia/es-BO' ELSE OS.Locale0 END AS [locale] ,OS.Locale0 [LCID HEX] ,OS.OSLanguage0 [Language] , vrs.AD_Site_Name0 ,'Chassis'=CASE WHEN enc.ChassisTypes0 in ('3','4','6','7','15') THEN 'Desktop' WHEN enc.ChassisTypes0 in ('8','9','10','21') THEN 'Laptop' END ,CASE When cs.Manufacturer0 = 'LENOVO' Then ltrim(rtrim(CSP.Version0)) Else ltrim(rtrim(cS.Model0)) End As [CorrectedModel] , vrs.Last_Logon_Timestamp0 ,cs.Manufacturer0 ,enc.SMBIOSAssetTag0 --, vid.CurrentHorizontalResolution0 --, vid.CurrentVerticalResolution0 --, cast(vid.CurrentHorizontalResolution0 as varchar) + 'x' + cast(vid.CurrentVerticalResolution0 as varchar) as ResolutionFinal --,ipaddress = STUFF((SELECT ', ' + b.IPAddress0 FROM v_GS_NETWORK_ADAPTER_CONFIGUR b WHERE b.ResourceID = vrs.ResourceID FOR XML PATH('')), 1, 2, '') from v_r_system vrs --from dbo.v_R_System_Valid RV left Join v_GS_INSTALLED_SOFTWARE S on S.ResourceID = VRS.ResourceID left join v_r_user vru on vrs.User_name0 = vru.User_Name0 LEFT outer join v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP U ON vrs.ResourceID = U.ResourceID left join v_Gs_SoftwareFile Sf on vrs.resourceid = sf.resourceid --left join V_GS_INSTALLED_EXECUTABLE IE on vrs.resourceid = IE.resourceid left join v_GS_COMPUTER_SYSTEM cs on vrs.resourceid = cs.resourceid left join v_GS_OPERATING_SYSTEM os on vrs.resourceid = os.resourceid --Left Join (Select ResourceID, User_Group_Name0 From v_RA_User_UserGroupName Where User_Group_Name0 = 'Domain\GSG.USA.BT.AllUsers') GN on GN.ResourceID = vrU.ResourceID --inner Join v_GS_QUICK_FIX_ENGINEERING QFE  on QFE.ResourceID = VRS.ResourceID  left join V_GS_NETWORK_LOGIN_PROFILE nlp on nlp.ResourceID = vrs.ResourceID Left Join v_GS_COMPUTER_SYSTEM_PRODUCT CSP on CSP.ResourceID = vrs.ResourceID left join v_GS_SYSTEM_ENCLOSURE enc on vrs.ResourceID = enc.ResourceID left join v_Add_Remove_Programs arp on vrs.ResourceID = arp.ResourceID --join v_GS_VIDEO_CONTROLLER as vid on vrs.resourceid = vid.ResourceID left join v_GS_PC_BIOS BIOS on vrs.ResourceID=bios.ResourceID --Full join fn_ListDeploymentTypeCIs(1033) DT on ISNULL(dt.SDMPackageDigest.value('(/AppMgmtDigest/DeploymentType/Installer/CustomData/SourceUpdateProductCode)[1]','nvarchar(MAX)'), 'None') = s.SoftwareCode0 --Full join fn_ListApplicationCIs(1033) Apps on DT.AppModelName = Apps.ModelName --left join v_RA_SYSTEM_SystemOUName ou on (ou.ResourceID = vRS.ResourceID) --left outer join dbo.v_RA_System_SystemContainerName CN on vrs.Resourceid = cn.resourceID Where 1=1 --and csp.Version0 like '%7010%' or cS.Model0 like '%7010%' --and cS.Model0 like '%5440%' --and vid.CurrentVerticalResolution0 is not null --and nlp.Name0 like 'X%' --and vru.mail0 like '%bah%' --and os.Caption0 not like '%server%' --and vrs.name0 like '%dp0%' --and vrs.name0 in ('computername1','computername2') --and VRS.Distinguished_Name0 like '%bang%' --and vru.user_name0 in ('userid1','userid2') --and User_Group_Name0 like '%UDL.IT.allstaff%' --and s.ARPDisplayName0 like '%autocad%' or s.ARPDisplayName0 like '%aspen%' --and s.InstalledLocation0 like '%protreat%' --and arp.ProdID0 = '{6A4B782B-A676-481F-92B9-DF10D4B517BB}' --and vru.displayname0 in ('Lname, Fname','Lname, Fname') --and vru.mail0 in ('fname.lname@domain.com','fname.lname2@domain.com') --and sf.FileName like '%.exe' --and SF.FileName like '%.pst%' /* and arp.DisplayName0 like '%microsoft office%' and arp.DisplayName0 not like '%update%' and arp.DisplayName0 not like '%kb%' and arp.DisplayName0 not like '%tool%' and arp.DisplayName0 not like '%mui%' and arp.DisplayName0 not like '%server%' and arp.DisplayName0 not like '%web%' and arp.DisplayName0 not like '%proof%' and arp.DisplayName0 not like '%comp%' and arp.DisplayName0 not like '%servi%' */ --order by vrs.Netbios_Name0