Complete OS Version/Build of Client

[Greg Thomas]

Hi all, trying to create a report that shows the Complete OS Version as described here: Complete OS Version/Build of Client

I need some other information that is not included Inv_AeX_AC_Identification and can't figure out how to add the fnGetComparableVersionAsString ([OS Comparable Version]) AS [OS Version Detailed] to my report. I am not an SQL guy and only make reports from the builder or steal them from awesome people like you that post them. Here is my basic query, what I really must have is the [OU Membership].[Distinguished Name]

SELECT DISTINCT
[vri1_Computer].[Guid] AS [GUID],
[vri1_Computer].[Name] AS [Name],
[dca3_AeX AC Identification].[Domain] AS [Domain],
[dca3_AeX AC Identification].[Last Logon User] AS [Last Logon User],
[dca3_AeX AC Identification].[OS Comparable Version] AS [OS Comparable Version],
[dca2_AeX AC TCPIP].[IP Address] AS [IP Address],
[dca4_OU Membership].[Distinguished Name] AS [Distinguished Name]
FROM
[vRM_Computer_Item] AS [vri1_Computer]
LEFT OUTER JOIN [Inv_AeX_AC_TCPIP] AS [dca2_AeX AC TCPIP]
ON ([vri1_Computer].[Guid] = [dca2_AeX AC TCPIP].[_ResourceGuid])
LEFT OUTER JOIN [Inv_AeX_AC_Identification] AS [dca3_AeX AC Identification]
ON ([vri1_Computer].[Guid] = [dca3_AeX AC Identification].[_ResourceGuid])
LEFT OUTER JOIN [Inv_OU_Membership] AS [dca4_OU Membership]
ON ([vri1_Computer].[Guid] = [dca4_OU Membership].[_ResourceGuid])
LEFT OUTER JOIN ([ScopeMembership] AS [ajs5_ScopeMembership]
LEFT OUTER JOIN [FolderBaseFolder] AS [ajs6_FolderBaseFolder]
ON ([ajs5_ScopeMembership].[ScopeCollectionGuid] = [ajs6_FolderBaseFolder].[FolderGuid]))
ON ([vri1_Computer].[Guid] = [ajs5_ScopeMembership].[ResourceGuid])
WHERE [vri1_Computer].[Guid] IN (

SELECT computer.[Guid] FROM
(
SELECT
[vri1_Computer].[Guid]
FROM
[vRM_Computer_Item] AS [vri1_Computer]
LEFT OUTER JOIN ([ScopeMembership] AS [ajs2_ScopeMembership]
LEFT OUTER JOIN [FolderBaseFolder] AS [ajs3_FolderBaseFolder]
ON ([ajs2_ScopeMembership].[ScopeCollectionGuid] = [ajs3_FolderBaseFolder].[FolderGuid]))
ON ([vri1_Computer].[Guid] = [ajs2_ScopeMembership].[ResourceGuid])
WHERE
(
(
([ajs3_FolderBaseFolder].[ParentFolderGuid] = '91c68fcb-1822-e793-b59c-2684e99a64cd')
)
)

) AS computer
INNER JOIN fnGetTrusteeScopedResourcesByType('493435f7-3b17-4c4c-b07f-c23e7ab7781f','%TrusteeScope%',1) AS [fnGTSR_3] ON computer.[Guid] = [fnGTSR_3].[ResourceGuid]
INNER JOIN vComputer AS [vCMP_4] ON computer.[Guid] = [vCMP_4].[Guid]

)
GROUP BY
[dca2_AeX AC TCPIP].[IP Address],
[dca3_AeX AC Identification].[Domain],
[dca3_AeX AC Identification].[Last Logon User],
[dca3_AeX AC Identification].[OS Comparable Version],
[dca4_OU Membership].[Distinguished Name],
[vri1_Computer].[Guid],
[vri1_Computer].[Name]
ORDER BY
[IP Address],
[Domain],
[Last Logon User],
[OS Comparable Version],
[Distinguished Name]

Any help would be much appreciated.

Greg

[NORBERT WERKSTETTER]

I have expanded the script for you:
SELECT DISTINCT
[vri1_Computer].[Guid] AS [GUID],
[vri1_Computer].[Name] AS [Name],
[dca3_AeX AC Identification].[Domain] AS [Domain],
[dca3_AeX AC Identification].[Last Logon User] AS [Last Logon User],
CASE WHEN [dca3_AeX AC Identification].[OS Comparable Version] IS NULL THEN '' ELSE dbo.fnGetComparableVersionAsString([dca3_AeX AC Identification].[OS Comparable Version]) END AS 'OS Comparable Version',
[dca2_AeX AC TCPIP].[IP Address] AS [IP Address],
[dca4_OU Membership].[Distinguished Name] AS [Distinguished Name]
FROM
[vRM_Computer_Item] AS [vri1_Computer]
LEFT OUTER JOIN [Inv_AeX_AC_TCPIP] AS [dca2_AeX AC TCPIP]
ON ([vri1_Computer].[Guid] = [dca2_AeX AC TCPIP].[_ResourceGuid])
LEFT OUTER JOIN [Inv_AeX_AC_Identification] AS [dca3_AeX AC Identification]
ON ([vri1_Computer].[Guid] = [dca3_AeX AC Identification].[_ResourceGuid])
LEFT OUTER JOIN [Inv_OU_Membership] AS [dca4_OU Membership]
ON ([vri1_Computer].[Guid] = [dca4_OU Membership].[_ResourceGuid])
LEFT OUTER JOIN ([ScopeMembership] AS [ajs5_ScopeMembership]
LEFT OUTER JOIN [FolderBaseFolder] AS [ajs6_FolderBaseFolder]
ON ([ajs5_ScopeMembership].[ScopeCollectionGuid] = [ajs6_FolderBaseFolder].[FolderGuid]))
ON ([vri1_Computer].[Guid] = [ajs5_ScopeMembership].[ResourceGuid])
WHERE [vri1_Computer].[Guid] IN (

SELECT computer.[Guid] FROM
(
SELECT
[vri1_Computer].[Guid]
FROM
[vRM_Computer_Item] AS [vri1_Computer]
LEFT OUTER JOIN ([ScopeMembership] AS [ajs2_ScopeMembership]
LEFT OUTER JOIN [FolderBaseFolder] AS [ajs3_FolderBaseFolder]
ON ([ajs2_ScopeMembership].[ScopeCollectionGuid] = [ajs3_FolderBaseFolder].[FolderGuid]))
ON ([vri1_Computer].[Guid] = [ajs2_ScopeMembership].[ResourceGuid])
WHERE
(
(
([ajs3_FolderBaseFolder].[ParentFolderGuid] = '91c68fcb-1822-e793-b59c-2684e99a64cd')
)
)

) AS computer
INNER JOIN fnGetTrusteeScopedResourcesByType('493435f7-3b17-4c4c-b07f-c23e7ab7781f','%TrusteeScope%',1) AS [fnGTSR_3] ON computer.[Guid] = [fnGTSR_3].[ResourceGuid]
INNER JOIN vComputer AS [vCMP_4] ON computer.[Guid] = [vCMP_4].[Guid]

)
GROUP BY
[dca2_AeX AC TCPIP].[IP Address],
[dca3_AeX AC Identification].[Domain],
[dca3_AeX AC Identification].[Last Logon User],
[dca3_AeX AC Identification].[OS Comparable Version],
[dca4_OU Membership].[Distinguished Name],
[vri1_Computer].[Guid],
[vri1_Computer].[Name]
ORDER BY
[IP Address],
[Domain],
[Last Logon User],
[OS Comparable Version],
[Distinguished Name]

[Pablo Llorente]

Hello,

I am using this query, but from time to time, whenever MS releases new
versions, it is needed to include them in the CASE clause.



SELECT distinct
[vri1_Computer].Name
,vc.[Domain]
,[ajs2_vAC_OverallAgentHealth].[Agent Health]
,t.[Distinguished Name]
, "OS Name" =
CASE
WHEN IAAI.[OS build Number] like '22%' THEN 'Windows 11'
ELSE IAAI.[OS Name]
END
,IAAI.[OS Build Number]
, "OS Build Name" = CASE
WHEN IAAI.[OS Name] LIKE '%Server%' THEN 'Windows Server'
WHEN IAAI.[OS Name] LIKE '%11%' OR IAAI.[OS Build Number] like '22000%'
THEN 'Windows 11'
WHEN IAAI.[OS Name] LIKE '%macOS%' THEN 'Mac OS'
WHEN IAAI.[OS Name] LIKE 'Windows 7%' THEN 'Windows 7'
WHEN IAAI.[OS Name] LIKE 'Windows%8%' OR IAAI.[OS Build Number] LIKE
'9600%' THEN 'Windows 8'
WHEN [OS Build Number] = 10240 THEN '1507'
WHEN IAAI.[OS Build Number] = 10586 THEN '1511'
WHEN IAAI.[OS Build Number] = 14393 AND IAAI.[OS Name] NOT LIKE '%Server%'
THEN '1607'
WHEN IAAI.[OS Build Number] = 15063 THEN '1703'
WHEN IAAI.[OS Build Number] = 16299 THEN '1709'
WHEN IAAI.[OS Build Number] in (17134,17133) THEN '1803'
WHEN IAAI.[OS Build Number] in (17661,17692,17763) THEN '1809'
WHEN IAAI.[OS Build Number] in (18282,18362) THEN '1903'
WHEN IAAI.[OS Build Number] in (18363) THEN '1909'
WHEN IAAI.[OS Build Number] in (19041) THEN '2004'
WHEN IAAI.[OS Build Number] in (19042) THEN '20H2'
WHEN IAAI.[OS Build Number] in (19043) THEN '21H1'
WHEN IAAI.[OS Build Number] in (20215,19044) THEN '21H2'

END
,IAAI.[OS Build Number] as "OS Build Number"
,vc.[IP Address]
,vc.[User] as [Last_Logon_User]
,DATEDIFF(day, [ajs5_vAC_AgentCommunicationHealth].[Last Agent
Communication], GETDATE()) as 'Days_from_Last_Altiris_Connection'
FROM [vRM_Computer_Item] AS [vri1_Computer]
LEFT OUTER JOIN [vAC_OverallAgentHealth] AS [ajs2_vAC_OverallAgentHealth]
ON ([vri1_Computer].[Guid] = [ajs2_vAC_OverallAgentHealth].[ResourceGuid])
LEFT OUTER JOIN [vAC_AgentCommunicationHealth] AS
[ajs5_vAC_AgentCommunicationHealth] ON ([vri1_Computer].[Guid] =
[ajs5_vAC_AgentCommunicationHealth].[ResourceGuid])
LEFT OUTER JOIN vComputer as vc ON vc.Guid = [vri1_Computer].[Guid]
LEFT OUTER JOIN ItemResource r ON r.[Guid] = [vri1_Computer].[Guid]
LEFT OUTER JOIN Inv_AeX_AC_Identification IAAI on IAAI._ResourceGuid =
[vri1_Computer].[Guid]
LEFT OUTER JOIN Inv_Global_Active_Directory_Details t on t.[_ResourceGuid]
= r.[Guid]
WHERE
vc.Name IS NOT NULL
AND datalength(vc.[Name]) > 0
AND [ajs2_vAC_OverallAgentHealth].[Agent Health] <> 'unmanaged'
ORDER BY IAAI.[OS Build Number] DESC;

Hope it helps.


Best Regards / Saludos
___________________________

PABLO LLORENTE ABAD
EMEA Workplace Services , Workplace Specialist

Calle Albasanz 14, 4th floor
Madrid , Spain
Mobile [phone]
*[e-mail] <[e-mail]>**
<http: www.holcim.com/="">*
*www.holcim.com <http: www.holcim.com/="">*

Follow us on Facebook <https: www.facebook.com/lafargeholcimitemea/=""> |
Twitter <https: twitter.com/lhitemea=""> | LinkedIn
<https: www.linkedin.com/company/lafargeholcimitemea/="">

*To visit our Workplace Connect site click here
<https: connect.lafargeholcim.com/emea-digital-center/functions/it-security/emea-workplace-services="">*

This email is confidential and intended only for the use of the above named
addressee. If you have received this email in error, please delete it
immediately and notify us by email or telephone.

[Greg Thomas]

Thanks to all for your help.

[Shawn Mayhew]

I would add my version - note I have specified field - 1 - for Direct member. We have a very large enviroment with multiple OU assignments to the same machines . Even then we have duplicates due to how messy our AD enviroment is. 

@Pablo Llorente
Also I make a choice here to go off AC Identification for machines with client date within 60 days.  I want only machines that are online and active within 60 days showing in my reports. I know i have 6k machines out there not reporting in but at the moment I don't have any political backing to chase them done.  The difference between vRM_Computer_Item and computers reporting in within 60 days  is 10.5k  

Note what I also do for Prime user - I want fully qualified full time users in this report. Before i did this i had duplicates in my Primary user fields. If your AD import includes GL codes you can do lease monthly billing off that field. I have used that field before. 

Also understand if you are using the Patch method to update your OS that the OS image date will be off. It will only show the last bare metal image date. I run a custom scan for the OS updated by Patch method. I did not include that in this report as you would have different Guids. 

select distinct
ac.name,
coalesce (
case when ac.[OS Build Number] = '18363' then 'Windows 10 (1909)' ELSE NULL end,
case when ac.[OS Build Number] = '7601' then 'Windows 7' ELSE NULL end,
case when ac.[os build number] = '19041' then 'Windows 10 (20H1)' else Null end,
case when ac.[os build number] = '19044' then 'Windows 10 (21H2)' else Null end,
case when ac.[os build number] = '19043' then 'Windows 10 (21H1)' else Null end,
case when ac.[os build number] = '19042' then 'Windows 10 (20H2)' else Null end,
case when ac.[OS Build Number] = '18362' then 'Windows 10 (1903)' ELSE NULL end,
case when ac.[OS Build Number] = '17763' then 'Windows 10 (1809)' ELSE NULL end,
case when ac.[OS Build Number] = '17134' then 'Windows 10 (1803)' ELSE NULL end,
case when ac.[OS Build Number] = '16299' then 'Windows 10 (1709)' ELSE NULL end,
case when ac.[OS Build Number] = '15063' then 'Windows 10 (1703)' ELSE NULL end,
case when ac.[OS Build Number] = '14393' then 'Windows 10 (1607)' ELSE NULL end,
case when ac.[OS Build Number] = '10586' then 'Windows 10 (1511)' ELSE NULL end,
case when ac.[OS Build Number] = '10240' then 'Windows 10 (0)' ELSE NUll end )
as 'OS Buid Name' ,


convert(varchar, ac.[client date], 101) as 'HeartBeat',
-- Software Scan shows machine has completed a full inventory 
convert(varchar,aud.[scan time],101) as 'SW Audit Scan',aud.[files examined],
--Display Auto Log on devices - this finds us the Clinical Devicesf
CASE when (ac.Name = prime.[primary user]) then 'Auto Log' else 'Information Worker' end as 'Machine Type',
vc.[IP Address],
convert(varchar,os.[Last Boot Up Time],101) as 'Last Boot Up',
convert(varchar,os.[Install Date],101) as 'OS Install Date',
Prime.[primary user],
T20.[Given Name] as 'First Name',
ISNULL(T20.[Surname],'') AS 'Last Name',
T20.[office telephone],
t20.[office location],
T20.department,
t20.email,
vcs.[Model],
COALESCE(
case when chas.[Chassis Package Type] = '1' then 'Virtual' ELSE NULL end,
case when chas.[Chassis Package Type] = '3' then 'Desktop' ELSE NULL end,
case when chas.[Chassis Package Type] = '4' Then 'Desktop' ELSE NULL end,
case when chas.[Chassis Package Type] = '6' then 'Desktop' ELSE NULL end,
case when chas.[Chassis Package Type] = '7' then 'Desktop' ELSE NULL end,
case when chas.[Chassis Package Type] = '8' then 'Laptop' ELSE NULL end,
case when chas.[Chassis Package Type] = '9' then 'Laptop' ELSE NULL end,
case when chas.[Chassis Package Type] = '10' then 'Laptop' ELSE NULL end,
case when chas.[Chassis Package Type] = '11' then 'Laptop' ELSE NULL end,
case when chas.[Chassis Package Type] = '12' then 'Laptop' ELSE NULL end,
case when chas.[Chassis Package Type] = '13' Then 'All in one' ELSE NULL end,
case when chas.[Chassis Package Type] = '15' then 'Desktop' ELSE NULL end,
case when chas.[Chassis Package Type] = '30' then 'Laptop' ELSE NULL end,
case when chas.[Chassis Package Type] = '31' then 'Laptop' ELSE NULL end,
case when chas.[Chassis Package Type] = '32' then 'Laptop' ELSE NULL end,
case when chas.[Chassis Package Type] = '35' then 'Desktop' ELSE NULL end ) as ' Machine Type',

OUcomputer.[Distinguished Name], OUcomputer.IsDirectMember




FROM dbo.Inv_AeX_AC_Identification ac
left JOIN vcomputer i ON i.Guid = ac._ResourceGuid
left join [vAC_AgentCommunicationHealth] agencom on agencom.ResourceGuid = ac._ResourceGuid
left join dbo.Inv_AeX_AC_TCPIP tc on tc._ResourceGuid = ac._ResourceGuid
left join vComputer vc on vc.Guid =ac._ResourceGuid
left join ( select MAX (Left(prog.DisplayVersion,2))as 'Display Version', i.[guid]
FROM dbo.vComputer i INNER JOIN dbo.Inv_AddRemoveProgram prog
ON i.[Guid]= prog._ResourceGuid
where ( prog.DisplayName ='Microsoft Office Professional Plus 2007'
or prog.DisplayName ='Microsoft Office Professional Plus 2010'or prog.DisplayName ='Microsoft Office Professional Edition 2003'
or prog.DisplayName ='Microsoft Office 2010'or prog.DisplayName ='Microsoft Office Standard 2010'
or prog.DisplayName ='Microsoft Office 2000 SR-1 Professional'or prog.DisplayName ='2007 Microsoft Office system'
or prog.DisplayName ='Microsoft Office Enterprise 2007'or prog.DisplayName ='Microsoft Office Basic Edition 2003'
or prog.DisplayName ='Microsoft Office 2000 SR-1 Disc 2' or prog.DisplayName ='Microsoft Office Professional Plus 2013'
or prog.DisplayName ='Microsoft Office Professional Plus 2016'or prog.DisplayName ='Microsoft Office 365 ProPlus - en-us' )
and prog.InstallFlag =1
group by i.guid ) office1
on i.Guid =office1.[guid]

left join (SELECT _ResourceGuid, MAX([user]) AS [primary user]
FROM dbo.Inv_AeX_AC_Primary_User
WHERE ([User] IS NOT NULL) AND ([User] <> '') GROUP BY _ResourceGuid ) Prime
on ac._resourceguid = Prime._ResourceGuid
-- Did this query to remove User records that were incomplete
LEFT JOIN (select distinct vuser.* from vuser where [Given Name] !='' )t20
ON Prime.[primary user] = T20.name
left join vHWProcessor vhp on vhp._ResourceGuid = i.Guid
left JOIN dbo.vHWComputerSystem hpm
ON hpm._ResourceGuid = i.Guid
left join Inv_OS_Operating_System os on vc.Guid = os._ResourceGuid
left JOIN (SELECT CAST (SUM ([Max Media Size (Kilobytes)] / (1024.0 * 1024.0))
AS DECIMAL (10,2)) [Hard DISK Size],_ResourceGuid FROM vHWStorage
WHERE [Media Type] = 29 GROUP BY _ResourceGuid) a
ON a._ResourceGuid = i.Guid
left JOIN (SELECT MAX (t.[Hard Disk Free Space]) [Hard Disk Free Space], t.Guid
FROM (SELECT ld._ResourceGuid AS Guid, CAST (SUM (ld.[Free Space (Bytes)] / (1024.0 * 1024.0 * 1024.0))
AS DECIMAL (10,2)) AS 'Hard Disk Free Space', ld.[Device ID]
FROM dbo.vHWLogicalDisk ld
JOIN dbo.vHWLogicalDiskBasedOnPartition ld1
ON ld._ResourceGuid = ld1._ResourceGuid
AND ld.[Device ID] = ld1.Dependent
JOIN dbo.vHWStorageToDiskPartition s1
ON s1._ResourceGuid = ld._ResourceGuid
AND (s1.Dependent = ld1.Antecedent OR s1.Dependent = ld.[Device ID])
JOIN dbo.vHWStorage sh
ON sh._ResourceGuid = s1._ResourceGuid
AND sh.[Device ID] = s1.Antecedent
AND sh.[Media Type] = 29
GROUP BY ld._ResourceGuid, ld.[Device ID])t
GROUP BY t.Guid) b
ON b.Guid = i.Guid
left join dbo.Inv_SW_BIOS_Element bi on bi._ResourceGuid = i.Guid

LEFT JOIN (SELECT CAST (MAX ([Adapter RAM (Bytes)] / (1024.0 * 1024.0))
AS DECIMAL (10,2)) [Video Memory], _ResourceGuid
FROM vHWDisplayController GROUP BY _ResourceGuid)c
ON c._ResourceGuid = i.Guid


left join (select * from Inv_HW_Physical_Memory mem where mem.[Device ID] ='Physical Memory 0') mem0 on mem0._ResourceGuid =vc.Guid
left join (select * from Inv_HW_Physical_Memory mem where mem.[Device ID] ='Physical Memory 1') mem1 on mem1._ResourceGuid = vc.guid
left join (select * from Inv_HW_Physical_Memory mem where mem.[Device ID] ='Physical Memory 2') mem2 on mem2._ResourceGuid = vc.guid
left join (select * from Inv_HW_Physical_Memory mem where mem.[Device ID] ='Physical Memory 3') mem3 on mem3._ResourceGuid = vc.guid
left join (SELECT [_ResourceGuid] ,[Monitor Type] ,[Device ID] ,[Video Input Mode] ,[Serial Number]
,[Manufacturing Date] FROM [dbo].[Inv_HW_Desktop_Monitor]mon
where mon.[Video Input Mode] =1 and mon.[Device ID]='DesktopMonitor1') mon1 on mon1._ResourceGuid = ac._ResourceGuid

--Seperate Office install report to do a unique count of machines with Office installed and what was latest version
left join (select distinct ac._ResourceGuid FROM dbo.Inv_AeX_AC_Identification ac
left JOIN vcomputer i ON i.Guid = ac._ResourceGuid left join dbo.Inv_AddRemoveProgram prog2 on prog2._ResourceGuid = ac._ResourceGuid
where prog2.DisplayName in ('Microsoft Office Excel Viewer','Microsoft Office Excel Viewer 2003',
'Microsoft Office PowerPoint Viewer 2003','Microsoft Office PowerPoint Viewer 2007 (English)','Microsoft Office Word Viewer 2003',
'Microsoft PowerPoint Viewer','Microsoft Visio Viewer 2010','Microsoft Visio Viewer 2013','Compatibility Pack for the 2007 Office system'))
Viewer
on viewer._ResourceGuid =ac._ResourceGuid




left join dbo.Inv_Audit_Results aud on aud._ResourceGuid = ac._ResourceGuid

left join dbo.vHWComputerSystem vcs on ac._ResourceGuid = vcs._ResourceGuid
left join (select prog.DisplayVersion ,prog.DisplayName, i.[guid]
FROM dbo.vComputer i INNER JOIN dbo.Inv_AddRemoveProgram prog
ON i.[Guid]= prog._ResourceGuid
where prog.displayname ='Microsoft Office 365 ProPlus - en-us' and installflag =1) o365ver on o365ver.[guid] = ac._ResourceGuid



left join (SELECT distinct _ResourceGuid, [Chassis Package Type] , [Serial Number] ,[Manufacturer]
FROM [Symantec_CMDB].[dbo].[vHWChassis]) chas on chas._ResourceGuid = ac._ResourceGuid



left join (SELECT distinct r.Guid ,t.[Distinguished Name], t.IsDirectMember FROM ItemResource AS r INNER JOIN
Inv_OU_Membership AS t ON t._ResourceGuid = r.Guid
--and t.[Distinguished Name] like '%win10%'
--and t.[Distinguished Name] !='OU=WIN10,OU=DEVICESPRD,OU=DELEGATEDOUS,DC=HS,DC=UCDHS,DC=UCDAVIS,DC=EDU'
--and t.[Distinguished Name] !='OU=DESKTOPS,OU=WIN10,OU=DEVICESPRD,OU=DELEGATEDOUS,DC=HS,DC=UCDHS,DC=UCDAVIS,DC=EDU'
--and t.[Distinguished Name] !='OU=UCDH,OU=DESKTOPS,OU=WIN10,OU=DEVICESPRD,OU=DELEGATEDOUS,DC=HS,DC=UCDHS,DC=UCDAVIS,DC=EDU'
--and t.[Distinguished Name] !='OU=MOBILE,OU=WIN10,OU=DEVICESPRD,OU=DELEGATEDOUS,DC=HS,DC=UCDHS,DC=UCDAVIS,DC=EDU'
--and t.[Distinguished Name] !='OU=UCDH,OU=MOBILE,OU=WIN10,OU=DEVICESPRD,OU=DELEGATEDOUS,DC=HS,DC=UCDHS,DC=UCDAVIS,DC=EDU'
--and t.[Distinguished Name] !='OU=_STAGE,OU=WIN10,OU=DEVICESPRD,OU=DELEGATEDOUS,DC=HS,DC=UCDHS,DC=UCDAVIS,DC=EDU'
--and t.[Distinguished Name] !='OU=AUTOLOGON,OU=WIN10,OU=DEVICESPRD,OU=DELEGATEDOUS,DC=HS,DC=UCDHS,DC=UCDAVIS,DC=EDU'
and t.[Distinguished Name] is not NUll
and t.IsDirectMember =1
) OUcomputer on OUcomputer.Guid = ac._ResourceGuid



where ac.[Client Date] > GETDATE ()-60







order by ac.[Name]asc







​

------------------------------
Altiris Admin since 2002
------------------------------

[Riggzie]

I bumped into this code somewhere months back so I am not taking credit for it but it works REALLY WELL and we love it.

While the "case" statement works and we were using that, you have to edit all the reports you utilize for each OS change and this method allows you to not have to do that.

in select statement put

vCI.[OS Name] + dbo.fnSysMask_GetOSDisplayVersion(vCI.OSSystemMask, vCI.DisplayVersion) AS [OS Name]

Make sure to join this table in

-- IAAI is Inv_AeX_AC_Identification

JOIN vComputerIdentification vCI ON vCI.[Guid] = IAAI._ResourceGuid 

that is all it takes. here is one of our reports that uses it.

SELECT DISTINCT IAAI._ResourceGuid as '_ItemGuid', IAAI.[Name] as 'Host Name', 
	IAAI.[Client Date] as 'Last Check-in', 
	vC.[CreatedDate], 
	IAAI.[Domain], 
	vC.[User], 
	vC.[IP Address], 
	CASE
		WHEN IAAT.DHCPEnabled = '0' THEN 'Static'
		WHEN IAAT.DHCPEnabled = '1' THEN 'DHCP'
	END as 'IP Type', 
	vC.[MAC Address], 
        REPLACE(CONCAT(SUBSTRING(IAAT.[MAC Address], 1, 6), '.', SUBSTRING(IAAT.[MAC Address], 6, 6), '.', SUBSTRING(IAAT.[MAC Address], 13, 6)), '-', '') as 'Cisco MAC Address', 
	IAAI.[Hardware Serial Number] as 'Serial Number', 
	vCI.[OS Name] + dbo.fnSysMask_GetOSDisplayVersion(vCI.OSSystemMask, vCI.DisplayVersion) AS [OS Name], 
	IAAI.[OS Build Number], IAAI.[System Type], 
	IAAI.[Last Logon User], 
	vHWCS.[Manufacturer], vHWCS.[Model]

FROM Inv_AeX_AC_Identification IAAI
	LEFT JOIN vHWComputerSystem vHWCS ON vHWCS._ResourceGuid = IAAI._ResourceGuid
	JOIN vFixedAssetResourceStatus vFARS ON vFARS.Guid = IAAI._ResourceGuid
	JOIN vComputer vC ON vC.Guid = IAAI._ResourceGuid
	LEFT JOIN Inv_AeX_AC_TCPIP IAAT ON IAAT._ResourceGuid = vC.Guid AND vC.[IP Address] = IAAT.[IP Address]
	JOIN vComputerIdentification vCI ON vCI.[Guid] = IAAI._ResourceGuid

WHERE vFARS.[Status] = 'Active'

ORDER BY IAAI.Name

Hope that gives some other possible choice.