Help With Modified Asset Intelligence Report (Full Version)

All Forums >> [Management Products] >> Microsoft Systems Management Server >> SMS 2003



Message

mattc -> Help With Modified Asset Intelligence Report (10/13/2008 2:46:23 PM)

I am hoping somebody could shed some light on an issue I am having to tweak one of the asset intelligence reports provided by SP3.  I am trying to modify one of the linked Hardware Asset reports to include AD OU information.  By adding the v_RA_System_SystemOUNAME table into the query I am getting more data than I need.  Instead of just giving me the final location within the OU's where a system resides, it lists the system multiple times in the report for each layer in the OU's.  Example....the PC resides in WORKSTSTATION\OU1\OU2\OU3 the report displays multiple records as follows for each PC

PCName WORKSTATION
PCName WORSTTATION\OU1
PCName WORKSTATION\OU1\OU2
PCName WORKSTATION\OU1\OU2\OU3

Here is the report I am trying to tweak:
if(@SoftwareID='')
begin
select @SoftwareID=NULL
end
if (@SoftwarePropertiesHash = '')
begin
select @SoftwarePropertiesHash = NULL
end
if(@CollectionID = '')
begin
select @CollectionID = NULL
end
SELECT DISTINCT
MEMB.Netbios_Name0 AS [Computer Name],
v_R_System.Resource_Domain_OR_Workgr0 AS [Domain],
v_R_System.User_Name0 AS [User NT Account],
v_R_User.Full_User_Name0 AS [Full Name],
v_R_User.Title0 AS [Title],
v_R_User.Department0 AS [Department],
v_GS_COMPUTER_SYSTEM.Manufacturer0 AS [Computer Manufacturer],
v_GS_COMPUTER_SYSTEM.Model0 AS [Computer Model],
v_RA_System_SystemOUName.System_OU_Name0 AS [Computer AD OU],
v_GS_x86_PC_MEMORY.TotalPhysicalMemory0/1024 AS [Total Physical Memory (MB)],
v_GS_INSTALLED_SOFTWARE_CATEGORIZED.Publisher0 AS [Publisher],
v_GS_INSTALLED_SOFTWARE_CATEGORIZED.ProductName0 AS [Product Name],
v_GS_INSTALLED_SOFTWARE_CATEGORIZED.ProductVersion0 AS [Version],
v_GS_INSTALLED_SOFTWARE_CATEGORIZED.ProductID0 AS [Product ID],
v_GS_INSTALLED_SOFTWARE_CATEGORIZED.CM_DSLID0 as [DSL ID],
v_GS_INSTALLED_SOFTWARE_CATEGORIZED.InstallType0 as [Installation Type]
FROM v_GS_INSTALLED_SOFTWARE_CATEGORIZED
INNER JOIN v_R_System ON v_R_System.ResourceID = v_GS_INSTALLED_SOFTWARE_CATEGORIZED.ResourceID
LEFT JOIN v_R_User ON v_R_User.User_Name0 = v_R_System.User_Name0 
INNER JOIN v_RA_System_SystemOUName ON V_RA_SystemOUName.ResourceID = v_R_System.ResourceID
INNER JOIN v_GS_X86_PC_MEMORY on v_GS_X86_PC_MEMORY.ResourceID = v_R_System.ResourceID
INNER JOIN
(
  select v_R_System_Valid.ResourceID, v_R_System_Valid.Netbios_Name0 from v_R_System_Valid
INNER JOIN v_FullCollectionMembership ON v_FullCollectionMembership.ResourceID = v_R_System_Valid.ResourceID
where v_FullCollectionMembership.CollectionID = @CollectionID
)MEMB on MEMB.ResourceID = v_GS_INSTALLED_SOFTWARE_CATEGORIZED.ResourceID
INNER JOIN v_GS_COMPUTER_SYSTEM on v_GS_COMPUTER_SYSTEM.ResourceID = MEMB.ResourceID
where
(@SoftwareID IS NOT NULL OR @SoftwarePropertiesHash IS NOT NULL) AND
(@SoftwareID IS NULL OR v_GS_INSTALLED_SOFTWARE_CATEGORIZED.SoftwareID  = @SoftwareID) AND
(@SoftwarePropertiesHash IS NULL OR v_GS_INSTALLED_SOFTWARE_CATEGORIZED.SoftwarePropertiesHash0 = @SoftwarePropertiesHash)
Order by MEMB.Netbios_Name0 ASC
 
Is there anyway to have the report only show me the final level of the OU the workstation resides in with out showing me each level and giving me multiple records of the same workstation in the report?  Any help would be appreciated.



mseely -> RE: Help With Modified Asset Intelligence Report (10/13/2008 4:05:35 PM)


The following query will return the 'longest' length OU for each resourceID (which should be the same as what is being looked for, since a system can't be in two different OU trees)

Should be able to have your query JOIN against this query instead of joining directly against the base 'V_RA_SYSTEM_SYSTEMOUNAME'

---
SELECT OU1.* FROM V_RA_SYSTEM_SYSTEMOUNAME OU1 JOIN
(
SELECT resourceid,MAX(LEN(system_ou_name0)) AS System_OU_Name0_Len
FROM V_RA_SYSTEM_SYSTEMOUNAME
GROUP BY resourceid
) OU2
ON (OU1.resourceid=OU2.resourceid) AND (LEN(OU1.system_ou_name0) = system_ou_name0_len)
---

 



jnelson993 -> RE: Help With Modified Asset Intelligence Report (10/14/2008 3:18:38 AM)

Just an FYI, you don't need to do a LEN on that because the MAX() OU will give you the longest OU.

So you only need a subquery inside the FROM clause (called a derived table) that will return the resourceIDs and the MAX OUs.  So something like this:

(I pretty'd it up a little bit and joined everything back to v_FullCollectionMembership since that should be the most selective view and make less work for the rest of the joins, and I removed v_R_System since you've got v_R_System_Valid already.

But I haven't tested it as I don't have all the fields you have, so your mileage may vary)

SELECT DISTINCT
  sys.Netbios_Name0               AS [Computer Name],
  sys.Resource_Domain_OR_Workgr0  AS [Domain],
  sys.User_Name0                  AS [User NT Account],
  usr.Full_User_Name0             AS [Full Name],
  usr.Title0                      AS [Title],
  usr.Department0                 AS [Department],
  cs.Manufacturer0                AS [Computer Manufacturer],
  cs.Model0                       AS [Computer Model],
  ou.System_OU_Name0              AS [Computer AD OU],
  mem.TotalPhysicalMemory0 / 1024 AS [Total Physical Memory (MB)],
  isc.Publisher0                  AS [Publisher],
  isc.ProductName0                AS [Product Name],
  isc.ProductVersion0             AS [Version],
  isc.ProductID0                  AS [Product ID],
  isc.CM_DSLID0                   AS [DSL ID],
  isc.InstallType0                AS [Installation Type]
FROM    
   dbo.v_FullCollectionMembership AS col
  INNER JOIN dbo.v_R_System_Valid AS sys
     ON sys.resourceID = col.resourceID
  INNER JOIN dbo.v_GS_INSTALLED_SOFTWARE_CATEGORIZED AS isc
    ON isc.ResourceID = col.ResourceID       
  LEFT JOIN dbo.v_R_User AS usr
    ON usr.User_Name0 = sys.User_Name0
   INNER JOIN (SELECT ResourceID, MAX(System_OU_Name0) AS System_OU_Name0 FROM dbo.v_RA_System_SystemOUName GROUP BY ResourceID) AS ou
    ON ou.ResourceID = col.ResourceID
   INNER JOIN dbo.v_GS_X86_PC_MEMORY AS mem
    ON mem.resourceID = col.ResourceID
  INNER JOIN dbo.v_GS_COMPUTER_SYSTEM AS cs
    ON cs.ResourceID = col.ResourceID
WHERE
  col.CollectionID = @CollectionID
  AND (@SoftwareID IS NOT NULL
        OR @SoftwarePropertiesHash IS NOT NULL)
  AND (@SoftwareID IS NULL
        OR isc.SoftwareID = @SoftwareID)
  AND (@SoftwarePropertiesHash IS NULL
        OR isc.SoftwarePropertiesHash0 = @SoftwarePropertiesHash)
ORDER BY sys.Netbios_Name0 ASC



MeenEnta -> RE: Help With Modified Asset Intelligence Report (10/14/2008 11:33:40 AM)

Man!! this is heavy on SQL server (mainly because of the software stuff), and it times out if you do all systems, I'm glad you added the prompts.



MeenEnta -> RE: Help With Modified Asset Intelligence Report (10/14/2008 11:35:52 AM)

This is what I use for H/W asset, and I do not include software.
 
Select
SD.Name0 'Machine Name',
SC.SMS_Installed_Sites0 'Site Code',
SD.User_Domain0 'Account Domain',
SD.User_Name0 'Login ID',
UD.Full_User_Name0 'Full Name',
SE.SMBIOSAssetTag0 'Asset Tag',
CS.Manufacturer0 Manufacturer,
CS.Model0 Model,
SN.SerialNumber0 'Serial Number',
ou.System_OU_Name0   AS [Computer AD OU],
mem.TotalPhysicalMemory0 / 1024 AS [Total Physical Memory (MB)],
OS.Caption0 + Space(1) + OS.CsdVersion0 'Operating System',
Case SE.ChassisTypes0
When 1 Then 'Other'
When 2 Then 'Unknown'
When 3 Then 'Desktop'
When 4 Then 'Low Profile Desktop'
When 5 Then 'PizzaBox'
When 6 Then 'Mini-Tower'
When 7 Then 'Tower'
When 8 Then 'Portable'
When 9 Then 'Laptop'
When 10 Then 'Notebook'
When 11 Then 'Handheld Device'
When 12 Then 'Docking Station'
When 13 Then 'All-In-One'
When 14 Then 'Sub-Notebook'
When 15 Then 'Space Saving'
When 16 Then 'Lunch Box'
When 17 Then 'Main System Chassis'
When 18 Then 'Expansion Chassis'
When 19 Then 'Sub-Chassis'
When 20 Then 'Bus Expansion Chassis'
When 21 Then 'Peripheral Chassis'
When 22 Then 'Storage Chassis'
When 23 Then 'Rack-Mount Chassis'
When 24 Then 'Sealed PC'
Else 'Unknown'
End 'Chassis Type',
Convert(VarChar(10), HS.LastHWScan, 101) 'Hardware Scan Date'
From v_R_System SD
Join V_R_User UD on SD.User_Name0 = UD.User_Name0
Join V_Ra_System_SmsInstalledSites SC on SD.ResourceID = SC.ResourceID
Join V_Gs_Workstation_Status HS On SD.ResourceID = HS.ResourceID
Join V_Gs_Computer_System CS On SD.ResourceID = CS.ResourceID
Join V_Gs_System_Enclosure SE On SD.ResourceID = SE.ResourceID
Join V_Gs_Pc_Bios SN On SD.ResourceID = SN.ResourceID
Join V_Gs_Operating_System OS On SD.ResourceID = OS.ResourceID
INNER JOIN (SELECT ResourceID, MAX(System_OU_Name0) AS System_OU_Name0 FROM dbo.v_RA_System_SystemOUName GROUP BY ResourceID) AS ou
   ON ou.ResourceID = SD.ResourceID
  INNER JOIN dbo.v_GS_X86_PC_MEMORY AS mem
   ON mem.resourceID = SD.ResourceID
Where SD.Client0 = 1
And SD.Obsolete0 = 0



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.25