|
-
For full details please see http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/c42ba8cf-02a5-4e2e-8d20-39b05a4a5740 select distinct
R.Netbios_Name0 as 'Name',
R.User_Name0 as 'User',
CS.Manufacturer0 as 'Manufacturer',
CS.Model0 as 'Model',
BIOS.SerialNumber0 as 'Serial',
SD.Name0 as 'Processor',
RAM.TotalPhysicalMemory0 as 'Memory',
VC.Name0 as 'Video Card',
VC.VCRam as 'Video Card RAM',
Sound.Description0 as 'Sound card'
from
dbo.v_R_System R
inner join dbo.v_GS_X86_PC_MEMORY RAM on RAM.ResourceID = R.ResourceId
inner join dbo.v_GS_COMPUTER_SYSTEM CS on CS.ResourceID = R.ResourceID
inner join dbo.v_GS_PC_BIOS BIOS on BIOS.ResourceID =R.ResourceId
inner join dbo.v_GS_SYSTEM_DEVICES SD on SD.ResourceID = R.ResourceId
left outer join dbo.v_GS_SOUND_DEVICE Sound on Sound.ResourceID = R.ResourceId
left outer join (
select
VC.ResourceID,
VC.Name0,
Convert(VarChar, VC.AdapterRam0 / 1024) + ' MB' as 'VCRAM'
from
dbo.v_GS_Video_Controller VC
Where
VC.Name0 != 'ConfigMgr Remote Control Driver'
and VC.AdapterRam0 is not null) VC on R.ResourceID = VC.ResourceId
where
SD.CompatibleIDs0 = 'ACPI\Processor'
|
-
For full details please see http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/c42ba8cf-02a5-4e2e-8d20-39b05a4a5740 select distinct
R.Netbios_Name0 as 'Name',
R.User_Name0 as 'User',
CS.Manufacturer0 as 'Manufacturer',
CS.Model0 as 'Model',
BIOS.SerialNumber0 as 'Serial',
SD.Name0 as 'Processor',
RAM.TotalPhysicalMemory0 as 'Memory',
VC.Name0 as 'Video Card',
VC.VCRam as 'Video Card RAM'
from
dbo.v_R_System R
inner join dbo.v_GS_X86_PC_MEMORY RAM on RAM.ResourceID = R.ResourceId
inner join dbo.v_GS_COMPUTER_SYSTEM CS on CS.ResourceID = R.ResourceID
inner join dbo.v_GS_PC_BIOS BIOS on BIOS.ResourceID =R.ResourceId
inner join dbo.v_GS_SYSTEM_DEVICES SD on SD.ResourceID = R.ResourceId
left outer join (
select
VC.ResourceID,
VC.Name0,
Convert(VarChar, VC.AdapterRam0 / 1024) + ' MB' as 'VCRAM'
from
dbo.v_GS_Video_Controller VC
Where
VC.Name0 != 'ConfigMgr Remote Control Driver'
and VC.AdapterRam0 is not null) VC on R.ResourceID = VC.ResourceId
where
SD.CompatibleIDs0 = 'ACPI\Processor'
|
-
For full details please see http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/c42ba8cf-02a5-4e2e-8d20-39b05a4a5740 select distinct
R.Netbios_Name0 as 'Name',
R.User_Name0 as 'User',
CS.Manufacturer0 as 'Manufacturer',
CS.Model0 as 'Model',
BIOS.SerialNumber0 as 'Serial',
SD.Name0 as 'Processor',
RAM.TotalPhysicalMemory0 as 'Memory',
VC.Name0 as 'Video Card',
Convert(VarChar, VC.AdapterRam0 / 1024) + ' MB' as 'Video Card RAM'
from
dbo.v_R_System R
inner join dbo.v_GS_X86_PC_MEMORY RAM on RAM.ResourceID = R.ResourceId
inner join dbo.v_GS_COMPUTER_SYSTEM CS on CS.ResourceID = R.ResourceID
inner join dbo.v_GS_PC_BIOS BIOS on BIOS.ResourceID =R.ResourceId
inner join dbo.v_GS_SYSTEM_DEVICES SD on SD.ResourceID = R.ResourceId
inner join dbo.v_GS_Video_Controller VC on R.ResourceID = VC.ResourceID
where
SD.CompatibleIDs0 = 'ACPI\Processor'
and VC.Name0 != 'ConfigMgr Remote Control Driver'
|
-
Use this query to find all webcam – SQL reports select
CS.Name0,
SD.Name0,
SD.DeviceID0
from
dbo.v_GS_COMPUTER_SYSTEM CS
join dbo.v_GS_SYSTEM_DEVICES SD on CS.ResourceID = SD.ResourceID
Where
SD.Name0 like '%Webcam%'
|
-
Use this WQL query to find all webcam http://social.technet.microsoft.com/Forums/en-US/configmgrai/thread/fa5fcfe2-69ef-4482-adfc-de25d6d19669/?prof=required select
SMS_R_System.Name,
SMS_G_System_SYSTEM_DEVICES.Name,
SMS_G_System_SYSTEM_DEVICES.DeviceID
from
SMS_R_System
inner join SMS_G_System_SYSTEM_DEVICES on SMS_G_System_SYSTEM_DEVICES.ResourceID = SMS_R_System.ResourceId
where
SMS_G_System_SYSTEM_DEVICES.Name like "%webcam%"
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
|
-
-
For full details see: http://www.myitforum.com/forums/tm.aspx?high=&m=238953&mpage=1#239186 SELECT distinct
sys1.Name0,
csys.Model0,
csys.Manufacturer0,
os.Caption0,
os.CSDVersion0,
BIOS.SerialNumber0,
sys1.description0,
REPLACE((SELECT IPAddress0 + ', ' FROM v_GS_NETWORK_ADAPTER_CONFIGUR WHERE resourceID = sys1.resourceID AND ipenabled0 = 1 FOR XML PATH('') )+'..',', ..','') AS [IP Addresses]
FROM
v_r_SYSTEM sys1
join v_GS_COMPUTER_SYSTEM csys on csys.resourceid=sys1.resourceid
join v_GS_OPERATING_SYSTEM os on os.resourceid=sys1.resourceid
join v_GS_PC_BIOS bios on bios.resourceid=sys1.resourceid
JOIN v_FullCollectionMembership fcm on fcm.resourceid=sys1.resourceid
WHERE
fcm.Collectionid= 'SMS00001'
ORDER BY
sys1.Name0
|
-
For full details please see: http://social.technet.microsoft.com/Forums/en-ca/configmgrreporting/thread/19e0771c-eddc-43f7-956c-a5c9f0110e59 SELECT
A.Name0,
B.SerialNumber0 ,
A.Manufacturer0,
A.Model0,
C.Name0 ,
D.TotalPhysicalMemory0 ,
sum(E.Size0) ,
F.MACAddress0 ,
F.IPAddress0 ,
G.AD_Site_Name0 ,
A.UserName0 ,
H.Caption0 ,
H.CSDVersion0,
G.Creation_Date0 ,
I.LastHWScan--,
-- BL.DriveLetter0,
-- BL.ProtectionStatus0,
-- BL.PersistentVolumeID0,
-- CASE
-- WHEN BL.ProtectionStatus0 = 1 THEN 'Encrypted'
-- WHEN ((BL.ProtectionStatus0 = 0) AND (BL.PersistentVolumeID0 is not NULL)) THEN 'Suspended'
-- ELSE '*** UNENCRYPTED ***'
-- END AS 'Encryption_Status'
FROM
dbo.v_R_System G
join dbo.v_GS_COMPUTER_SYSTEM A on G.ResourceID = A.ResourceID
join dbo.v_GS_PC_BIOS B on G.ResourceID = B.ResourceID
join dbo.v_GS_PROCESSOR C on G.ResourceID = C.ResourceID
join dbo.v_GS_X86_PC_MEMORY D on G.ResourceID = D.ResourceID
join dbo.v_GS_DISK E on G.ResourceID = E.ResourceID
join dbo.v_GS_NETWORK_ADAPTER_CONFIGUR F on G.ResourceID = F.ResourceID
join dbo.v_GS_OPERATING_SYSTEM H on G.ResourceID = H.ResourceID
join dbo.v_GS_WORKSTATION_STATUS I on G.ResourceID = I.ResourceID
-- join dbo.v_GS_BITLOCKER_VOLUME_ENC BL on G.ResourceID = BL.ResourceID
WHERE
G.Netbios_Name0 like '%'
and F.MACAddress0 !=''
-- AND BL.DriveLetter0 like 'C:'
GROUP BY
A.Name0,
A.Manufacturer0,
A.Model0,
C.Name0,
D.TotalPhysicalMemory0,
G.AD_Site_Name0,
A.UserName0,
H.Caption0,
H.CSDVersion0,
G.Creation_Date0,
I.LastHWScan,
B.SerialNumber0,
F.MACAddress0,
F.IPAddress0
-- BL.DriveLetter0,
-- BL.ProtectionStatus0,
-- BL.PersistentVolumeID0
|
-
For full details, please see
http://social.technet.microsoft.com/Forums/en-ca/configmgrai/thread/7951b781-8354-490b-9b59-4fe4e68d1975
SELECT DISTINCT
CS.Name0,
CASE
WHEN ES.ChassisTypes0 in ('3','4','6','7','15') THEN 'Desktop'
WHEN ES.ChassisTypes0 in ('8','9','10','21') THEN 'Laptop'
Else 'Unknown'
END as 'Chassis',
BIOS.SerialNumber0,
CS.Manufacturer0,
CS.Model0,
OS.Caption0,
OS.CSDVersion0,
SCUM.TopConsoleUser0,
R.User_Name0 AS 'Last Logged',
BIOS.ReleaseDate0,
NAC.IPAddress0,
NAC.DefaultIPGateway0,
RAM.TotalPhysicalMemory0,
Sum(LD.Size0)
FROM
dbo.v_R_System R
INNER JOIN dbo.v_GS_PC_BIOS BIOS ON BIOS.ResourceID = R.ResourceID
INNER JOIN dbo.v_GS_COMPUTER_SYSTEM CS ON R.ResourceID = CS.ResourceID
INNER JOIN dbo.v_GS_NETWORK_ADAPTER_CONFIGUR NAC ON R.ResourceID = NAC.ResourceID
INNER JOIN dbo.v_GS_OPERATING_SYSTEM OS ON R.ResourceID = OS.ResourceID
LEFT OUTER JOIN dbo.v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP SCUM ON R.ResourceID = SCUM.ResourceID
INNER JOIN dbo.v_GS_SYSTEM_ENCLOSURE ES on R.ResourceID = ES.ResourceID
INNER JOIN dbo.v_GS_SYSTEM S on R.ResourceID = S.ResourceID
INNER JOIN dbo.v_GS_X86_PC_MEMORY RAM on R.ResourceID = RAM.ResourceID
INNER JOIN dbo.v_GS_LOGICAL_DISK LD on R.ResourceID = LD.ResourceID
WHERE
NAC.IPEnabled0 = 1
AND (NOT (NAC.DefaultIPGateway0 IS NULL))
AND S.SystemRole0 = 'Workstation'
Group by
CS.Name0,
CASE
WHEN ES.ChassisTypes0 in ('3','4','6','7','15') THEN 'Desktop'
WHEN ES.ChassisTypes0 in ('8','9','10','21') THEN 'Laptop'
Else 'Unknown'
END,
BIOS.SerialNumber0,
CS.Manufacturer0,
CS.Model0,
OS.Caption0,
OS.CSDVersion0,
SCUM.TopConsoleUser0,
R.User_Name0,
BIOS.ReleaseDate0,
NAC.IPAddress0,
NAC.DefaultIPGateway0,
RAM.TotalPhysicalMemory0
|
-
SELECT DISTINCT
CS.Name0,
CASE
WHEN ES.ChassisTypes0 in ('3','4','6','7','15') THEN 'Desktop'
WHEN ES.ChassisTypes0 in ('8','9','10','21') THEN 'Laptop'
Else 'Unknown'
END as 'Chassis',
BIOS.SerialNumber0,
CS.Manufacturer0,
CS.Model0,
OS.Caption0,
OS.CSDVersion0,
SCUM.TopConsoleUser0,
R.User_Name0 AS 'Last Logged',
BIOS.ReleaseDate0,
NAC.IPAddress0,
NAC.DefaultIPGateway0,
RAM.TotalPhysicalMemory0,
LD.DeviceID0,
LD.Size0
FROM
dbo.v_R_System R
INNER JOIN dbo.v_GS_PC_BIOS BIOS ON BIOS.ResourceID = R.ResourceID
INNER JOIN dbo.v_GS_COMPUTER_SYSTEM CS ON R.ResourceID = CS.ResourceID
INNER JOIN dbo.v_GS_NETWORK_ADAPTER_CONFIGUR NAC ON R.ResourceID = NAC.ResourceID
INNER JOIN dbo.v_GS_OPERATING_SYSTEM OS ON R.ResourceID = OS.ResourceID
LEFT OUTER JOIN dbo.v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP SCUM ON R.ResourceID = SCUM.ResourceID
INNER JOIN dbo.v_GS_SYSTEM_ENCLOSURE ES on R.ResourceID = ES.ResourceID
INNER JOIN dbo.v_GS_SYSTEM S on R.ResourceID = S.ResourceID
INNER JOIN dbo.v_GS_X86_PC_MEMORY RAM on R.ResourceID = RAM.ResourceID
INNER JOIN dbo.v_GS_LOGICAL_DISK LD on R.ResourceID = LD.ResourceID
WHERE
NAC.IPEnabled0 = 1
AND (NOT (NAC.DefaultIPGateway0 IS NULL))
AND S.SystemRole0 = 'Workstation'
|
-
For full details please see http://www.myitforum.com/Forums/tm.aspx?m=238674&high= SELECT distinct
DisplayName0,
Count(arp.ResourceID) AS 'Count',
Publisher0,
@CollID as CollectionID
FROM
dbo.v_Add_Remove_Programs arp
JOIN dbo.v_FullCollectionMembership fcm on arp.ResourceID = fcm.ResourceID
WHERE
fcm.CollectionID = @CollID
AND (Publisher0 LIKE 'Microsoft%')
AND DisplayName0 NOT LIKE '%Hotfix%'
AND DisplayName0 NOT LIKE '%Security Update%'
AND DisplayName0 NOT LIKE '%Update for%'
AND DisplayName0 NOT LIKE '%.NET%'
AND DisplayName0 NOT LIKE '%Viewer%'
AND DisplayName0 NOT LIKE '%Language Pack%'
AND DisplayName0 NOT LIKE '%Internet Explorer%'
AND DisplayName0 NOT LIKE '%MSXML%'
AND DisplayName0 NOT LIKE '%SDK%'
AND DisplayName0 NOT LIKE '%C++%'
AND DisplayName0 NOT LIKE '%Redistributable%'
AND DisplayName0 NOT LIKE '%Search%'
AND DisplayName0 NOT LIKE '%SMS%'
AND DisplayName0 NOT LIKE '%Silverlight%'
AND DisplayName0 NOT LIKE '%Live Meeting%'
AND DisplayName0 NOT LIKE '%(KB%'
AND DisplayName0 NOT LIKE '%Office Web%'
AND DisplayName0 NOT LIKE '%Office %Proof%'
AND DisplayName0 NOT LIKE '%Server %Proof%'
AND DisplayName0 NOT LIKE '%Office %Shared%'
AND DisplayName0 NOT LIKE '%Baseline Security Analyzer%'
AND DisplayName0 NOT LIKE '%Compatibility Pack%'
AND DisplayName0 NOT LIKE '%User State Migration Tools%'
GROUP BY
DisplayName0,
Publisher0
ORDER BY
Publisher0
|
-
-
-
-
For Full detail see http://social.technet.microsoft.com/Forums/en-US/configmgradminconsole/thread/547016e0-9f15-4611-aac9-24f582aa0614 select
R.netbios_name0,
R.user_name0,
OS.Caption0 AS 'Operating System',
ES.SerialNumber0 AS 'Serial Number'
from
dbo.v_R_System R
join dbo.v_GS_OPERATING_SYSTEM OS on OS.ResourceID = R.ResourceID
JOIN dbo.v_GS_System_Enclosure ES on ES.ResourceID = R.ResourceID
where
R.ResourceID not in
(
select distinct
ARP.ResourceId
From
dbo.v_ADD_REMOVE_PROGRAMS ARP
join dbo.v_GS_System S on ARP.ResourceID = S.ResourceId
where
ARP.DisplayName0 in
(
'Norton Antivirus Client',
'Norton AntiVirus Corporate Edition',
'Symantec AntiVirus',
'Symantec AntiVirus Client',
'Symantec AntiVirus Win64',
'Symantec Endpoint Protection'
)
or S.SMSID0 in
(
'GUID:6578610F-BFD0-4693-98B5-B6FB50129FB5',
'GUID:E64FF1A8-EF5B-46DD-8859-02D7A3E8818A',
'GUID:52EA60BD-F3DD-4615-9CCD-AFE0B7F28D41'
)
)
|
More Posts Next page »
|
|
|