Count of PCs by OU

Please see post http://social.technet.microsoft.com/Forums/systemcenter/en-US/aea60e7b-a88b-48d8-8cc3-8675097abe62/trying-to-do-a-report-of-sup-failures-by-ou?forum=configmgrinventory for full details

SELECT
    Max(SOU.System_OU_Name0),
    COUNT(*) AS 'COUNT'
FROM 
    dbo.v_RA_System_SystemOUName SOU 
    JOIN dbo.v_R_System SYS ON SYS.ResourceID = SOU.ResourceID

/*WHERE SYS.Netbios_Name0 IN ( 'PHO-PVR63186',
'WCO-PVP11579',
'PAL-PVI82336',
'LOM-PVI46044',
'LOM-PVI49102',
'LOM-PVR46148',
'PUG-PVI92643',
'RED-PVI54875',
'REN-PVR27553',
'WLA-PVR125834')*/
Group by 
    SOU.System_OU_Name0
ORDER BY
    SOU.System_OU_Name0

Read More»

Count of PCs by OU

Please see post http://social.technet.microsoft.com/Forums/systemcenter/en-US/aea60e7b-a88b-48d8-8cc3-8675097abe62/trying-to-do-a-report-of-sup-failures-by-ou?forum=configmgrinventory for full details

SELECT
    Max(SOU.System_OU_Name0),
    COUNT(*) AS 'COUNT'
FROM 
    dbo.v_RA_System_SystemOUName SOU 
    JOIN dbo.v_R_System SYS ON SYS.ResourceID = SOU.ResourceID

/*WHERE SYS.Netbios_Name0 IN ( 'PHO-PVR63186',
'WCO-PVP11579',
'PAL-PVI82336',
'LOM-PVI46044',
'LOM-PVI49102',
'LOM-PVR46148',
'PUG-PVI92643',
'RED-PVI54875',
'REN-PVR27553',
'WLA-PVR125834')*/
Group by 
    SOU.System_OU_Name0
ORDER BY
    SOU.System_OU_Name0

Read More»

PCs with Either of two applications installed.

Use this query to determine if a PC has either or both applications installed.

For more details, please see

http://social.technet.microsoft.com/Forums/systemcenter/en-US/6c4650c7-3246-4e13-a78d-05691c13c89d/duplicate-rows-when-finding-pcs-with-installed-software?forum=configmgrreporting

 

SELECT DISTINCT 
    R.Netbios_Name0, 
    R.User_Domain0+'\'+    R.User_Name0 as 'User Name', 
    OS.Caption0 AS 'Operating System Caption', 
    CASE isnull(WIR.ResourceID,0 ) 
        When 0 THEN ' ' 
        ELSE '*' 
    END AS 'Application1', 
    CASE isnull(MIR.ResourceID,0 ) 
        When 0 THEN ' ' 
        ELSE '*' 
    END AS 'Application2'
 FROM    
    dbo.v_R_System_Valid AS R 
    INNER JOIN dbo.v_GS_OPERATING_SYSTEM OS ON R.ResourceID = OS.ResourceID 
    Left outer join (Select ARP.ResourceID as 'ResourceID' from dbo.v_Add_Remove_Programs ARP Where arp.DisplayName0 like '%Warranty Information Reporting%') as WIR ON R.ResourceID = WIR.ResourceID
    Left outer join (Select ARP.ResourceID as 'ResourceID' from dbo.v_Add_Remove_Programs ARP Where arp.DisplayName0 like '%Monitor Information Reporting%') as MIR ON R.ResourceID = MIR.ResourceID
 ORDER BY 
    R.Netbios_Name0

Read More»

Limiting a report to a Collection

For more details please see

http://www.windows-noob.com/forums/index.php?/topic/9766-sccm-report-on-collection/

 

 

SELECT DISTINCT
    CS.Name0, 
    R.User_Name0, 
    MAX(SOU.System_OU_Name0) AS Expr1,                      
    CS.Description0, 
    CS.Manufacturer0, 
    CS.Model0,
    BIOS.SerialNumber0
FROM  
    dbo.v_Collection Coll
    join dbo.v_FullCollectionMembership FCM on Coll.CollectionID = FCM.CollectionID and Coll.Name = 'All systems'
    join dbo.v_GS_COMPUTER_SYSTEM CS on FCM.ResourceID = CS.ResourceID
    INNER JOIN dbo.v_R_System R ON CS.ResourceID = R.ResourceID 
    INNER JOIN dbo.v_RA_System_SystemOUName SOU ON CS.ResourceID = SOU.ResourceID 
    INNER JOIN dbo.v_GS_PC_BIOS BIOS ON CS.ResourceID = BIOS.ResourceID
GROUP BY 
    CS.Name0, 
    R.User_Name0, 
    CS.Description0,
    CS.Manufacturer0, 
    CS.Model0, 
    BIOS.SerialNumber0

Read More»

Find all PC with HW and SW inventory dates greater than 180 days

This WQL query will show you all PCs with a HW and SW Scan date of greater than 180 days.

select 
    SMS_R_System.Name, 
    SMS_R_System.LastLogonUserDomain, 
    SMS_R_System.LastLogonUserName
from  
    SMS_R_System 
    inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceId = SMS_R_System.ResourceId 
    inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceId = SMS_R_System.ResourceId 
    inner join SMS_G_System_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceId = SMS_R_System.ResourceId 
    inner join SMS_G_System_LastSoftwareScan on SMS_G_System_LastSoftwareScan.ResourceId = SMS_R_System.ResourceId 
where 
    SMS_G_System_SYSTEM.SystemRole = "Workstation" 
    and SMS_G_System_COMPUTER_SYSTEM.Model != "%Virtual%" 
    and Datediff(DD,SMS_G_System_WORKSTATION_STATUS.LastHardwareScan, Getdate()) > 180 
    and Datediff(DD,SMS_G_System_LastSoftwareScan.LastScanDate, Getdate()) > 180

Read More»

Finding all PCs with MSAcess.exe in the All Systems collection

Use this query to find all PCs with MS Access in the All systems collection.

 

select 
    SMS_R_System.ResourceID,
    SMS_R_System.ResourceType,
    SMS_R_System.Name,
    SMS_R_System.SMSUniqueIdentifier,
    SMS_R_System.ResourceDomainORWorkgroup,
    SMS_R_System.Client 
from 
    SMS_R_System 
    inner join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId 
    inner join SMS_FullCollectionMembership on SMS_FullCollectionMembership.ResourceID = SMS_R_System.ResourceId 
where 
    SMS_G_System_SoftwareFile.FileName = "msaccess.exe" 
    and SMS_FullCollectionMembership.CollectionID = 'SMS00001'

Read More»

Joining Users to a PC

Use this query to join Users to PCs

Select 
    R.Name0,
    U.User_Name0, 
    U.Full_User_Name0, 
    U.Unique_User_Name0,
    U.Mail0
From 
    dbo.v_R_System R 
    INNER JOIN dbo.v_R_User U ON U.Unique_User_Name0 = R.User_Domain0 + '\'+ R.User_Name0

.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; }

Read More»

Joining Users to a PC

Use this query to join Users to PCs

Select 
    R.Name0,
    U.User_Name0, 
    U.Full_User_Name0, 
    U.Unique_User_Name0,
    U.Mail0
From 
    dbo.v_R_System R 
    INNER JOIN dbo.v_R_User U ON U.Unique_User_Name0 = R.User_Domain0 + '\'+ R.User_Name0

.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; }

Read More»

Joining Users to a PC

Use this query to join Users to PCs

Select 
    R.Name0,
    U.User_Name0, 
    U.Full_User_Name0, 
    U.Unique_User_Name0,
    U.Mail0
From 
    dbo.v_R_System R 
    INNER JOIN dbo.v_R_User U ON U.Unique_User_Name0 = R.User_Domain0 + '\'+ R.User_Name0

.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; }

Read More»

Joining Users to a PC

Use this query to join Users to PCs

Select 
    R.Name0,
    U.User_Name0, 
    U.Full_User_Name0, 
    U.Unique_User_Name0,
    U.Mail0
From 
    dbo.v_R_System R 
    INNER JOIN dbo.v_R_User U ON U.Unique_User_Name0 = R.User_Domain0 + '\'+ R.User_Name0

.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; }

Read More»

Joining Users to a PC

Use this query to join Users to PCs

Select 
    R.Name0,
    U.User_Name0, 
    U.Full_User_Name0, 
    U.Unique_User_Name0,
    U.Mail0
From 
    dbo.v_R_System R 
    INNER JOIN dbo.v_R_User U ON U.Unique_User_Name0 = R.User_Domain0 + '\'+ R.User_Name0

.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; }

Read More»

Joining Users to a PC

Use this query to join Users to PCs

Select 
    R.Name0,
    U.User_Name0, 
    U.Full_User_Name0, 
    U.Unique_User_Name0,
    U.Mail0
From 
    dbo.v_R_System R 
    INNER JOIN dbo.v_R_User U ON U.Unique_User_Name0 = R.User_Domain0 + '\'+ R.User_Name0

.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; }

Read More»