MeenEnta
Posts: 118
Score: 0 Joined: 9/9/2008 Status: offline
|
Not sure what to say to you anymore Sherry... I'm out of Complements, Adjectives and English words..Will you take foreign language? LOL Worked perfectly... See PIC And here's the final code: --put laptop data into #tmp table SELECT SYS.Netbios_Name0, SYS.User_Name0, 'Laptop' as ComputerType, ENC.SerialNumber0, v_GS_User_Logon_Info0.LogonCount0 as 'Number of Logons', v_GS_User_Logon_Info0.UserRank0, SYS.Client0, SYS.Active0, SYS.Obsolete0, PROC2.Manufacturer0, PROC2.Model0, OPSYS.Caption0 AS "Operating System", OPSYS.CSDVersion0, ENC.ChassisTypes0, OPSYS.LastBootUpTime0 INTO #tmp FROM v_FullCollectionMembership fcm join v_gs_user_logon_info0 on fcm.resourceid = v_GS_User_Logon_Info0.resourceid JOIN v_R_System SYS ON fcm.ResourceID = SYS.ResourceID JOIN v_GS_OPERATING_SYSTEM OPSYS ON SYS.ResourceID = OPSYS.ResourceID JOIN v_GS_COMPUTER_SYSTEM PROC2 ON SYS.ResourceID = PROC2.ResourceID JOIN v_GS_SYSTEM_ENCLOSURE ENC ON SYS.ResourceID = ENC.ResourceId WHERE ENC.ChassisTypes0 IN ('8','9','10','11','14') AND fcm.CollectionID = @CollID --now pull the data from #tmp table... SELECT Netbios_Name0, User_Name0, ComputerType, SerialNumber0, [Number of Logons], UserRank0, Client0, Active0, Obsolete0, Manufacturer0, Model0, [Operating System], CSDVersion0, ChassisTypes0, LastBootUpTime0 FROM #tmp -- add the results above to results below UNION --this removes duplicates, UNION ALL would leave them -- get list of desktops for those same users and add to the results SELECT SYS.Netbios_Name0, SYS.User_Name0, 'Desktop' AS ComputerType, ENC.SerialNumber0, v_GS_User_Logon_Info0.LogonCount0 as 'Number of Logons', v_GS_User_Logon_Info0.UserRank0, SYS.Client0, SYS.Active0, SYS.Obsolete0, PROC2.Manufacturer0, PROC2.Model0, OPSYS.Caption0 AS [Operating System], OPSYS.CSDVersion0, ENC.ChassisTypes0, OPSYS.LastBootUpTime0 FROM v_FullCollectionMembership fcm join v_gs_user_logon_info0 on fcm.resourceid = v_GS_User_Logon_Info0.resourceid JOIN v_R_System SYS ON fcm.ResourceID = SYS.ResourceID JOIN v_GS_OPERATING_SYSTEM OPSYS ON SYS.ResourceID = OPSYS.ResourceID JOIN v_GS_COMPUTER_SYSTEM PROC2 ON SYS.ResourceID = PROC2.ResourceID JOIN v_GS_SYSTEM_ENCLOSURE ENC ON SYS.ResourceID = ENC.ResourceId WHERE ENC.ChassisTypes0 NOT IN ('8','9','10','11','14') AND netbios_name0 NOT IN (SELECT netbios_name0 FROM #tmp) AND user_name0 IN (SELECT user_name0 FROM #tmp) AND fcm.CollectionID = @CollID ORDER BY User_Name0, ComputerType DESC, Netbios_Name0 drop table #tmp and don't forget the prompt: begin if (@__filterwildcard = '') SELECT DISTINCT CollectionID, Name FROM v_Collection ORDER BY Name else SELECT DISTINCT CollectionID, Name FROM v_Collection WHERE CollectionID like @__filterwildcard ORDER BY Name end THANK YOU AGAIN TO YOU AND JOHN. Thumbnail Image
Attachment (1)
|