Browse by Tags

Network Report

Select Distinct CS.Name0, NIC.Description0, NAC.IPAddress0, NAC.DefaultIPGateway0 from dbo.v_GS_COMPUTER_SYSTEM CS, dbo.v_GS_NETWORK_ADAPTER NIC, dbo.v_GS_NETWORK_ADAPTER_CONFIGUR NAC Where CS.ResourceID = NIC.ResourceID and CS.ResourceID = NAC.ResourceID and NAC.ServiceName0 = NIC.ServiceName0 and NAC...

Posted to Garth's Blog (Weblog) by Anonymous on 07-29-2008
Filed under: ConfigMGr, SMS, Code, SQL
Count of Device by AD site

Select B.ADSite as 'AD Site', Count(B.ADSite) as 'Count' from (SELECT Case When R.AD_Site_Name0 = '' Then '<No AD Site>' When R.AD_Site_Name0 = NULL Then '<No AD Site>' When isnull(R.AD_Site_Name0,'one') = 'one' Then '<No AD Site>'...

Posted to Garth's Blog (Weblog) by Anonymous on 07-30-2008
Filed under: ConfigMGr, SMS, Code, SQL
Two NICs enabled at the same time

Select Distinct CS.Name0, NIC.Description0, NAC.IPAddress0, NAC.DefaultIPGateway0, NIC.*, Nac.* from dbo.v_GS_COMPUTER_SYSTEM CS, dbo.v_GS_NETWORK_ADAPTER...

Posted to Garth's Blog (Weblog) by Anonymous on 08-08-2008
Filed under: ConfigMGr, SMS, Code, SQL
History install date for ARP (example)

Select ARP.DisplayName0, HARP.InstallDate0 from dbo.v_GS_ADD_REMOVE_PROGRAMS ARP, dbo.v_HS_ADD_REMOVE_PROGRAMS HARP Where ARP.ResourceID = HARP.ResourceID and ARP.ProdID0 = HARP.ProdID0 Group...

Posted to Garth's Blog (Weblog) by Anonymous on 08-08-2008
Filed under: ConfigMGr, SMS, Code, SQL
Active workstations in the last 30 days

Select CS.Name0, WS.LastHWScan From dbo.v_GS_COMPUTER_SYSTEM CS, dbo.v_GS_WORKSTATION_STATUS WS Where CS.Resourceid = WS.ResourceID and datediff(dd,WS.LastHWScan,getdate())< 30

Posted to Garth's Blog (Weblog) by Anonymous on 08-08-2008
Filed under: ConfigMGr, SMS, Code, SQL
List all discovered devices which are not a client

This query will display all devices (PCs, routers, etc.) that ConfigMgr / SMS knows about which are not clients. select Name0, Resource_Domain_Or_Workgr0, Operating_system_name_and0, AD_Site_Name0, Community_name0 from v_R_System R where R.ResourceID Not in (Select CS.ResourceID From v_GS_Computer_System...

Posted to Garth's Blog (Weblog) by Anonymous on 08-20-2008
Filed under: ConfigMGr, SMS, Code, SQL
Query Help

declare @olddate datetime set @olddate=dateadd(day,-8, getdate()) select CS.Name0 as 'Computer Name', arp.installdate0 as 'Installed Date', arp.displayname0 as 'Application Name', from dbo.v_GS_ADD_REMOVE_PROGRAMS arp, dbo.v_GS_Computer_System CS where CS.resourceid = arp.resourceid...

Posted to Garth's Blog (Weblog) by Anonymous on 08-21-2008
Filed under: ConfigMGr, SMS, Code, SQL
Forum Reply - T-SQL Best Practice

SELECT DISTINCT ARP.DisplayName0, ARP.Publisher0, ARP.Version0, CS.Name0, CS.UserName0, WS.LastHWScan, BIOS.SerialNumber0, R.AD_Site_Name0 FROM dbo.v_GS_ADD_REMOVE_PROGRAMS ARP INNER JOIN dbo.v_GS_Computer_System CS ON ARP.ResourceID = CS.ResourceID INNER JOIN dbo.v_GS_PC_BIOS BIOS ON CS.ResourceID ...

Posted to Garth's Blog (Weblog) by Anonymous on 08-22-2008
Filed under: ConfigMGr, SMS, Code, SQL
PCs with the no logon information for 30 days

SELECT distinct CS.Name0 AS 'Computer', isnull(CS.UserName0,HCS.UserName0) AS 'User Name' , CS.TimeStamp FROM v_GS_COMPUTER_SYSTEM CS Left outer join v_HS_COMPUTER_SYSTEM HCS on CS.ResourceID = HCS.ResourceID WHERE HCS.UserName0 is not NULL and datediff(dd,CS.TimeStamp,getdate()) >...

Posted to Garth's Blog (Weblog) by Anonymous on 08-27-2008
Filed under: ConfigMGr, SMS, Code, SQL
List all non-DHCP NICs

SELECT CS.Name0, NA.Name0, NAC.IPAddress0, NAC.DefaultIPGateway0, NAC.IPSubnet0, NAC.DNSHostName0 FROM dbo.v_GS_COMPUTER_SYSTEM CS, dbo.v_GS_NETWORK_ADAPTER NA, dbo.v_GS_NETWORK_ADAPTER_CONFIGUR NAC WHERE NAC.ResourceID = CS.ResourceID AND NA.ResourceID = CS.ResourceID AND NA.ServiceName0 = NAC.ServiceName0...

Posted to Garth's Blog (Weblog) by Anonymous on 09-09-2008
Filed under: ConfigMGr, SMS, Code, SQL

Page 1 of 4 (33 items) 1 2 3 4 Next >