SCCM OSD Info

[jperry]

I smashed this together pretty quickly, but thought I'd share.
As part of SCCM OSD I have a vbs script that writes environment variables to the registry.
Then I collect those with LS.

Add scanning HKLM registry entries to LS
SOFTWARE\_IT\SCCM\OSD
'Installed Date', 'TaskSequenceName', 'AdvertisementID', 'Organization', 'TaskSequence ID', 'TSVersion', 'SMSClientGUID', 'OSDComputerName'

Add scanning file to LS
C:\windows\ccm\ccmexec.exe

The vbs script is as follows.
Called before the application installs with %WINDIR%\system32\cmd.exe /c %WINDIR%\system32\cscript.exe AddImagingDatesToRegistry.vbs

On error resume next
dim osd 
dim strkeypath
set env = CreateObject("Microsoft.SMS.TSEnvironment")

const HKEY_LOCAL_MACHINE = &H80000002


'variables
TaskSequenceName = env("_SMSTSPackageName")
AdvertisementID = env("_SMSTSAdvertID") 
Organization = env("_SMSTSOrgName")
TaskSequenceID = env("_SMSTSPackageID")
Packagename = env("_SMSTSPackageName") 
MediaType = env("_SMSTSMediaType")
TSVersion = env("TSVersion")
SMSClientGUID = env("_SMSTSClientGUID")
OSDComputerName = env("OSDComputerName")


Set oReg=GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\default:StdRegProv")


'----------- Write to registry ---------------

strKeyPath = "SOFTWARE\_IT\SCCM\OSD"
oReg.CreateKey HKEY_LOCAL_MACHINE,strKeyPath

strValueName ="Installed Date"
strValue = formatdatetime(date,2) & " " & formatdatetime(time,3)
oReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue

strValueName = "TaskSequenceName"
strvalue = TaskSequenceName
oReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,TaskSequenceName

strValueName = "AdvertisementID"
strvalue = AdvertisementID
oReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,AdvertisementID

strValueName = "Organization"
oReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,Organization

strValueName = "TaskSequence ID"
oReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,TaskSequenceID

strValueName = "TSVersion"
oReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,TSVersion

strValueName = "SMSClientGUID"
oReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,SMSClientGUID

strValueName = "OSDComputerName"
oReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,OSDComputerName

The report

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Firstseen,
  SQLSCCMOSD."Installed Date" AS [OSD Installed Date],
  SQLSCCMOSD.TaskSequenceName AS [OSD TaskSequenceName], 
  SQLSCCMOSD.AdvertisementID AS [OSD AdvertisementID], 
  SQLSCCMOSD.Organization AS [OSD Organization], 
  SQLSCCMOSD."TaskSequence ID" AS [OSD TaskSequence ID], 
  SQLSCCMOSD.TSVersion AS [OSD TSVersion], 
  SQLSCCMOSD.SMSClientGUID AS [OSD SMSClientGUID], 
  SQLSCCMOSD.OSDComputerName AS [OSDComputerName],
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssetCustom.Location,
  tblAssets.Lastseen,
  tblFileVersions.FileVersion As CCMEXEC,
  Convert(Decimal(7,2),tblAssets.Uptime / 60 / 60 / 24) As [Days up],
  Left(tblADComputers.OU, CharIndex(',', tblADComputers.OU) - 1) As OU,
  tblADusers.Displayname,
  tblADusers.Username,
  tblADusers.Userdomain,
  tblAssetCustom.Model,
  tblComputersystem.SystemType,
  tblOperatingsystem.Version As [OS Version],
  tblOperatingsystem.Caption As [OS Name],
  tsysIPLocations.IPLocation,
  tblAssets.IPAddress,
  tblADComputers.OU As [OU Full],
  tblAssets.Description As [LS Description],
  tblADComputers.Description As [AD Description]
From tblAssets
  Left Outer Join tblADusers On tblAssets.Username = tblADusers.Username
  Left Outer Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Left Outer Join tblState On tblAssetCustom.State = tblState.State
  Left Outer Join tsysAssetTypes On tsysAssetTypes.AssetType =
    tblAssets.Assettype
  Left Outer Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Left Outer Join tblOperatingsystem On
    tblAssets.AssetID = tblOperatingsystem.AssetID
  Left Outer Join tsysIPLocations On tsysIPLocations.LocationID =
    tblAssets.LocationID
  Left Outer Join tblComputersystem On
    tblAssets.AssetID = tblComputersystem.AssetID
  Left Outer Join tblFileVersions On tblAssets.AssetID = tblFileVersions.AssetID
    And tblFileVersions.FilePathfull Like '%ccmexec.exe'
  Left Outer Join (
Select 
	AssetID,
	Max("Installed Date") AS [Installed Date],
	MAX("TaskSequenceName") AS [TaskSequenceName], 
	MAX("AdvertisementID") AS [AdvertisementID], 
	MAX("Organization") AS [Organization], 
	MAX("TaskSequence ID") AS [TaskSequence ID], 
	MAX("TSVersion") AS [TSVersion], 
	MAX("SMSClientGUID") AS [SMSClientGUID], 
	MAX("OSDComputerName") AS [OSDComputerName]
from
	(SELECT TOP (1000) [RegistryID]
		  ,[AssetID]
		  ,[Regkey]
		  ,[Valuename]
		  ,[Value]
		  ,[Lastchanged]
	  FROM [lansweeperdb].[dbo].[tblRegistry]
	  Where tblRegistry.Regkey = 'HKEY_LOCAL_MACHINE\SOFTWARE\_IT\SCCM\OSD'
	) AS SourceTable
PIVOT
	(
	max(value)
	For Valuename in ([Installed Date], [TaskSequenceName], [AdvertisementID], [Organization], [TaskSequence ID], [TSVersion], [SMSClientGUID], [OSDComputerName])
	) AS RegTable
Group By 
AssetID
) AS SQLSCCMOSD ON SQLSCCMOSD.AssetID = tblAssets.AssetID

Where 
  tblComputersystem.Domainrole < 2 And tblState.Statename Like 'Active%'
Order By 
  tblAssets.Firstseen Desc,
  SQLSCCMOSD.Organization Desc, 
  SQLSCCMOSD.TaskSequenceName Desc, 
  SQLSCCMOSD.AdvertisementID Desc, 
  SQLSCCMOSD."Installed Date" Desc,
  tblAssets.Lastseen Desc,
  tsysIPLocations.IPLocation,
  tblAssets.AssetName