Thanks a lot for your response.
I was able to create the report using the 'Query Designer in SQL'
This is my new Query. Works fine.
SELECT DISTINCT v_R_User.User_Name0 AS [User Name], v_R_User.department0
AS [Department Name], v_R_System.Netbios_Name0 AS [Computer Name],
v_GS_COMPUTER_SYSTEM.Manufacturer0 AS
[Manufacturer], v_GS_COMPUTER_SYSTEM.Model0 AS [Model],
v_GS_PC_BIOS.SerialNumber0 AS [Serial Number],
v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System]
FROM v_R_System INNER JOIN
v_R_User ON v_R_System.User_Name0 =
v_R_User.User_Name0 INNER JOIN
v_GS_COMPUTER_SYSTEM ON v_R_System.ResourceID =
v_GS_COMPUTER_SYSTEM.ResourceID INNER JOIN
v_GS_PC_BIOS ON v_GS_COMPUTER_SYSTEM.ResourceID =
v_GS_PC_BIOS.ResourceID INNER JOIN
v_GS_OPERATING_SYSTEM ON v_R_System.ResourceID =
v_GS_OPERATING_SYSTEM.ResourceID LEFT OUTER JOIN
v_ClientCollectionMembers ON v_ClientCollectionMembers.ResourceID =
V_R_SYSTEM.ResourceID
WHERE v_ClientCollectionMembers.CollectionID= @CollID
From: [email protected] [mailto:[email protected]]
On Behalf Of Eswar Koneti
Sent: Thursday, October 17, 2013 3:43 PM
To: [email protected]
Subject: RE: [mssms] Please Help modifying an existing Custom Report Query
Importance: High
first of all ,I dont use v_r_system for user name,to know more how it
works,read http://smsug.ca/blogs/garth_jones/archive/2007/06/30/482.aspx
by default department value is not added by AD User Discovery .did u add
this value ?If already added,do you see it from v_R_User ?
if the value appear in DB then try the following one. Bold letters are
added.
SELECT DISTINCT
v_GS_COMPUTER_SYSTEM.Name0, v_GS_PC_BIOS.SerialNumber0,
v_GS_COMPUTER_SYSTEM.Manufacturer0, v_GS_COMPUTER_SYSTEM.Model0,
v_GS_OPERATING_SYSTEM.Caption0 AS [OS Type],
v_GS_OPERATING_SYSTEM.CSDVersion0 AS [OS Version],
v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0,
v_R_System.User_Name0 AS [Last Logged], v_R_User.department0,
v_GS_PC_BIOS.ReleaseDate0 AS [BIOS Date],
v_GS_NETWORK_ADAPTER_CONFIGUR.IPAddress0,
v_GS_NETWORK_ADAPTER_CONFIGUR.DefaultIPGateway0, CASE WHEN
v_GS_SYSTEM_ENCLOSURE.ChASsisTypes0 LIKE '1' THEN 'Virtual' WHEN
v_GS_SYSTEM_ENCLOSURE.ChASsisTypes0 LIKE '2' THEN 'Blade Server' WHEN
v_GS_SYSTEM_ENCLOSURE.ChASsisTypes0 LIKE '3' THEN 'Desktop' WHEN
v_GS_SYSTEM_ENCLOSURE.ChASsisTypes0 LIKE '4' THEN 'Low-Profile Desktop' WHEN
v_GS_SYSTEM_ENCLOSURE.ChASsisTypes0 LIKE '5' THEN 'Pizza-Box' WHEN
v_GS_SYSTEM_ENCLOSURE.ChASsisTypes0 LIKE '6' THEN 'Mini Tower' WHEN
v_GS_SYSTEM_ENCLOSURE.ChASsisTypes0
LIKE '7' THEN 'Tower' WHEN v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 LIKE '8' THEN
'Portable' WHEN v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 LIKE '9' THEN
'Laptop' WHEN v_GS_SYSTEM_ENCLOSURE.ChASsisTypes0 LIKE '10' THEN 'Notebook'
WHEN v_GS_SYSTEM_ENCLOSURE.ChASsisTypes0 LIKE '11' THEN 'Hand-Held'
WHEN v_GS_SYSTEM_ENCLOSURE.ChASsisTypes0 LIKE '12' THEN 'Mobile Device in
Docking Station' WHEN v_GS_SYSTEM_ENCLOSURE.ChASsisTypes0 LIKE '13'
THEN 'All-in-One' WHEN v_GS_SYSTEM_ENCLOSURE.ChASsisTypes0 LIKE '14' THEN
'Sub-Notebook' WHEN v_GS_SYSTEM_ENCLOSURE.ChASsisTypes0 LIKE '15' THEN
'Space Saving Chassis' WHEN v_GS_SYSTEM_ENCLOSURE.ChASsisTypes0 LIKE '16'
THEN 'Ultra Small Form Factor' WHEN v_GS_SYSTEM_ENCLOSURE.ChASsisTypes0
LIKE '17' THEN 'Server Tower ChASsis' WHEN
v_GS_SYSTEM_ENCLOSURE.ChASsisTypes0 LIKE '18' THEN 'Mobile Device in Docking
Station' WHEN v_GS_SYSTEM_ENCLOSURE.ChASsisTypes0
LIKE '19' THEN 'Sub-ChASsis' WHEN v_GS_SYSTEM_ENCLOSURE.ChASsisTypes0 LIKE
'20' THEN 'Bus-Expansion chASsis' WHEN v_GS_SYSTEM_ENCLOSURE.ChASsisTypes0
LIKE '21' THEN 'Peripheral ChASsis' WHEN v_GS_SYSTEM_ENCLOSURE.ChASsisTypes0
LIKE '22' THEN 'Storage ChASsis' WHEN v_GS_SYSTEM_ENCLOSURE.ChASsisTypes0
LIKE '23' THEN 'Rack-Mounted ChASsis' WHEN
v_GS_SYSTEM_ENCLOSURE.ChASsisTypes0 LIKE '24' THEN 'Sealed-Case PC' ELSE
'Unknown' END AS 'Chassis Type'
FROM v_GS_PC_BIOS INNER JOIN
v_GS_COMPUTER_SYSTEM ON v_GS_PC_BIOS.ResourceID =
v_GS_COMPUTER_SYSTEM.ResourceID INNER JOIN
v_GS_NETWORK_ADAPTER_CONFIGUR ON
v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_NETWORK_ADAPTER_CONFIGUR.ResourceID
INNER JOIN
v_GS_OPERATING_SYSTEM ON
v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID INNER
JOIN
v_R_System ON v_GS_COMPUTER_SYSTEM.ResourceID =
v_R_System.ResourceID LEFT OUTER JOIN
v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP ON
v_GS_COMPUTER_SYSTEM.ResourceID =
v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID INNER JOIN
v_GS_SYSTEM_ENCLOSURE ON v_R_System.ResourceID =
v_GS_SYSTEM_ENCLOSURE.ResourceID
inner join v_R_User on v_R_User.User_Name0=v_R_System.User_Name0
WHERE (v_GS_NETWORK_ADAPTER_CONFIGUR.IPEnabled0 = 1) AND (NOT
(v_GS_NETWORK_ADAPTER_CONFIGUR.DefaultIPGateway0 IS NULL)) AND
(v_R_System.Operating_System_Name_and0 LIKE
'%Workstation%')
ORDER BY v_GS_PC_BIOS.ReleaseDate0
Eswar Koneti
www.eskonr.com
_____
From: [email protected]
To: [email protected]
Subject: [mssms] Please Help modifying an existing Custom Report Query
Date: Thu, 17 Oct 2013 14:37:34 +0800
The Below Query (Report) works perfectly fine in My SCCM 2012 Environment.
I need to add one more column in this report department0 from v_R_User.
I am not good at SQL so need help in modifying the below query.
Kindly help me in adding this JOIN "JOIN v_R_System ON v_R_System.User_Name0
= v_R_User.User_Name0" in the appropriate manner in the below query.
(This report will show
Name Serial Number Manufacturer Model OS Type OS Version TopConsoleUser0
Last Logged BIOS Date IP Address Default IP Gateway Chassis Type)
SELECT DISTINCT
v_GS_COMPUTER_SYSTEM.Name0, v_GS_PC_BIOS.SerialNumber0,
v_GS_COMPUTER_SYSTEM.Manufacturer0, v_GS_COMPUTER_SYSTEM.Model0,
v_GS_OPERATING_SYSTEM.Caption0 AS [OS Type],
v_GS_OPERATING_SYSTEM.CSDVersion0 AS [OS Version],
v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0,
v_R_System.User_Name0 AS [Last Logged],
v_GS_PC_BIOS.ReleaseDate0 AS [BIOS Date],
v_GS_NETWORK_ADAPTER_CONFIGUR.IPAddress0,
v_GS_NETWORK_ADAPTER_CONFIGUR.DefaultIPGateway0, CASE WHEN
v_GS_SYSTEM_ENCLOSURE.ChASsisTypes0 LIKE '1' THEN 'Virtual' WHEN
v_GS_SYSTEM_ENCLOSURE.ChASsisTypes0 LIKE '2' THEN 'Blade Server' WHEN
v_GS_SYSTEM_ENCLOSURE.ChASsisTypes0 LIKE '3' THEN 'Desktop' WHEN
v_GS_SYSTEM_ENCLOSURE.ChASsisTypes0 LIKE '4' THEN 'Low-Profile Desktop' WHEN
v_GS_SYSTEM_ENCLOSURE.ChASsisTypes0 LIKE '5' THEN 'Pizza-Box' WHEN
v_GS_SYSTEM_ENCLOSURE.ChASsisTypes0 LIKE '6' THEN 'Mini Tower' WHEN
v_GS_SYSTEM_ENCLOSURE.ChASsisTypes0
LIKE '7' THEN 'Tower' WHEN v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 LIKE '8' THEN
'Portable' WHEN v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 LIKE '9' THEN
'Laptop' WHEN v_GS_SYSTEM_ENCLOSURE.ChASsisTypes0 LIKE '10' THEN 'Notebook'
WHEN v_GS_SYSTEM_ENCLOSURE.ChASsisTypes0 LIKE '11' THEN 'Hand-Held'
WHEN v_GS_SYSTEM_ENCLOSURE.ChASsisTypes0 LIKE '12' THEN 'Mobile Device in
Docking Station' WHEN v_GS_SYSTEM_ENCLOSURE.ChASsisTypes0 LIKE '13'
THEN 'All-in-One' WHEN v_GS_SYSTEM_ENCLOSURE.ChASsisTypes0 LIKE '14' THEN
'Sub-Notebook' WHEN v_GS_SYSTEM_ENCLOSURE.ChASsisTypes0 LIKE '15' THEN
'Space Saving Chassis' WHEN v_GS_SYSTEM_ENCLOSURE.ChASsisTypes0 LIKE '16'
THEN 'Ultra Small Form Factor' WHEN v_GS_SYSTEM_ENCLOSURE.ChASsisTypes0
LIKE '17' THEN 'Server Tower ChASsis' WHEN
v_GS_SYSTEM_ENCLOSURE.ChASsisTypes0 LIKE '18' THEN 'Mobile Device in Docking
Station' WHEN v_GS_SYSTEM_ENCLOSURE.ChASsisTypes0
LIKE '19' THEN 'Sub-ChASsis' WHEN v_GS_SYSTEM_ENCLOSURE.ChASsisTypes0 LIKE
'20' THEN 'Bus-Expansion chASsis' WHEN v_GS_SYSTEM_ENCLOSURE.ChASsisTypes0
LIKE '21' THEN 'Peripheral ChASsis' WHEN v_GS_SYSTEM_ENCLOSURE.ChASsisTypes0
LIKE '22' THEN 'Storage ChASsis' WHEN v_GS_SYSTEM_ENCLOSURE.ChASsisTypes0
LIKE '23' THEN 'Rack-Mounted ChASsis' WHEN
v_GS_SYSTEM_ENCLOSURE.ChASsisTypes0 LIKE '24' THEN 'Sealed-Case PC' ELSE
'Unknown' END AS 'Chassis Type'
FROM v_GS_PC_BIOS INNER JOIN
v_GS_COMPUTER_SYSTEM ON v_GS_PC_BIOS.ResourceID =
v_GS_COMPUTER_SYSTEM.ResourceID INNER JOIN
v_GS_NETWORK_ADAPTER_CONFIGUR ON
v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_NETWORK_ADAPTER_CONFIGUR.ResourceID
INNER JOIN
v_GS_OPERATING_SYSTEM ON
v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID INNER
JOIN
v_R_System ON v_GS_COMPUTER_SYSTEM.ResourceID =
v_R_System.ResourceID LEFT OUTER JOIN
v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP ON
v_GS_COMPUTER_SYSTEM.ResourceID =
v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID INNER JOIN
v_GS_SYSTEM_ENCLOSURE ON v_R_System.ResourceID =
v_GS_SYSTEM_ENCLOSURE.ResourceID
WHERE (v_GS_NETWORK_ADAPTER_CONFIGUR.IPEnabled0 = 1) AND (NOT
(v_GS_NETWORK_ADAPTER_CONFIGUR.DefaultIPGateway0 IS NULL)) AND
(v_R_System.Operating_System_Name_and0 LIKE
'%Workstation%') and
v_R_System.User_Name0 = v_R_User.User_Name0
ORDER BY v_GS_PC_BIOS.ReleaseDate0
