|
-
SELECT DISTINCT
COMSYS.Manufacturer0,
COMSYS.Model0,
Client.Version0,
Count(*)
FROM
v_GS_COMPUTER_SYSTEM COMSYS
JOIN v_FullCollectionMembership fcm ON COMSYS.ResourceID = FCM.ResourceID
JOIN v_GS_PROCESSOR PROC1 ON COMSYS.ResourceID = PROC1.ResourceID
JOIN v_GS_X86_PC_MEMORY MEM ON COMSYS.ResourceID = MEM.ResourceID
JOIN v_GS_OPERATING_SYSTEM OPSYS ON COMSYS.ResourceID = OPSYS.ResourceID
JOIN v_GS_LOGICAL_DISK LDISK ON COMSYS.ResourceID = LDISK.ResourceID and LDISK.DeviceID0=SUBSTRING(OPSYS.WindowsDirectory0,1,2)
JOIN v_GS_Client0 CLIENT ON COMSYS.ResourceID = CLIENT.ResourceID
WHERE
(fcm.CollectionID='SMS00001')
AND (ROUND (ROUND(CONVERT (FLOAT ,mem.TotalPhysicalMemory0) / 1048576, 2) * 1024, 0) < 1024
OR ROUND (CONVERT (FLOAT, PROC1.MaxClockSpeed0), -2)/ 1000 < 1
OR ROUND(CONVERT (FLOAT ,LDISK.FreeSpace0) / 1024, 2) < 16)
Group by
COMSYS.Manufacturer0,
COMSYS.Model0,
Client.Version0
|
-
SELECT DISTINCT
COMSYS.Manufacturer0,
COMSYS.Model0,
Count(*)
FROM
v_GS_COMPUTER_SYSTEM COMSYS
JOIN v_FullCollectionMembership fcm ON COMSYS.ResourceID = FCM.ResourceID
JOIN v_GS_PROCESSOR PROC1 ON COMSYS.ResourceID = PROC1.ResourceID
JOIN v_GS_X86_PC_MEMORY MEM ON COMSYS.ResourceID = MEM.ResourceID
JOIN v_GS_OPERATING_SYSTEM OPSYS ON COMSYS.ResourceID = OPSYS.ResourceID
JOIN v_GS_LOGICAL_DISK LDISK ON COMSYS.ResourceID = LDISK.ResourceID and LDISK.DeviceID0=SUBSTRING(OPSYS.WindowsDirectory0,1,2)
WHERE
(fcm.CollectionID='SMS00001')
AND (ROUND (ROUND(CONVERT (FLOAT ,mem.TotalPhysicalMemory0) / 1048576, 2) * 1024, 0) < 1024
OR ROUND (CONVERT (FLOAT, PROC1.MaxClockSpeed0), -2)/ 1000 < 1
OR ROUND(CONVERT (FLOAT ,LDISK.FreeSpace0) / 1024, 2) < 16)
Group by
COMSYS.Manufacturer0,
COMSYS.Model0
|
-
select distinct
SMS_R_System.Name,
SMS_R_System.ResourceId
from
SMS_R_System
where
SMS_R_System.ResourceId in ( select
SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID
from
SMS_G_System_ADD_REMOVE_PROGRAMS
where
SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Microsoft Lync 2010")
or SMS_R_System.ResourceId in ( select
SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceID
from
SMS_G_System_ADD_REMOVE_PROGRAMS_64
where
SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName = "Microsoft Lync 2010")
|
-
select
NetBiosName as "CAM NetBiosName",
LastPolicyRequest as "CAM LastPolicyRequest",
LastDDR as "CAM LastDDR",
sy.Operating_System_Name_and0 as "Operating System",
sy.Resource_Domain_OR_Workgr0 as "AD Domain",
NAC.IPAddress0
from
v_CH_ClientSummary
inner join v_R_System as sy on sy.ResourceID=MachineID
join dbo.v_GS_NETWORK_ADAPTER_CONFIGUR NAC on sy.ResourceID = NAC.ResourceID
where
sy.Operating_System_Name_and0 like '%workstation%'
and NAC.IPaddress0 is Not Null
|
-
SELECT
S.Name0 AS [System Name],
OS.Caption0 AS [Operating System],
OS.CSDVersion0 AS [Service Pack],
OS.InstallDate0 AS [Original OS Install Date],
OS.LastBootUpTime0 AS [Last Boot Time],
NAC.IPAddress0 AS [IP Address],
NAC.IPSubnet0 AS [Subnet Mask],
NAC.DefaultIPGateway0 AS [Gateway],
NAC.DNSServerSearchOrder0 AS [DNS Servers],
NAC.WINSPrimaryServer0 AS [Pri WINS],
NAC.WINSSecondaryServer0 AS [Sec WINS],
BIOS.SMBIOSBIOSVersion0 AS [System ROM Version], BIOS.ReleaseDate0 AS [System ROM Date]
FROM
dbo.v_GS_NETWORK_ADAPTER_CONFIGUR NAC
INNER JOIN dbo.v_GS_OPERATING_SYSTEM OS ON NAC.ResourceID = OS.ResourceID
INNER JOIN dbo.v_GS_PC_BIOS BIOS ON OS.ResourceID = BIOS.ResourceID
INNER JOIN dbo.v_GS_SYSTEM S ON BIOS.ResourceID = S.ResourceID
join dbo.v_FullCollectionMembership_Valid FCM on NAC.ResourceID = FCM.ResourceID
where
FCM.CollectionID = @variable and
NAC.IPaddress0 is Not Null
|
-
With the start of a new year, it is that time again to support the System Center Community, with that in mind I have made my yearly donation of $250 USD to MyITForum (MIF). http://myitforum.com/myitforumwp/aboutus/donations/ MIF is truly a community that helps each other both professionally and personally. But it cost thousands of dollars each MONTH to keep this community going. MIF has worked hard to get sponsor to defray the cost however we as the community should do our part. I encourage you to donate to MIF, no you don’t need to match my donation but you surely can afford to donate the equivalent of 1 Beer ($10) to a community that you use every day.
|
-
-
-
On Thursday night at the User Group event, I was challenged to write 70-659 exam before Dec 31 2011. There are high stakes at risk here, bragging rights and more importantly beer! Over the next few weeks you will see a few blogs post from me on the subject as a way for me to study for the exam. I spent a bit of time on the weekend, starting to get my act in order! First things first, study material! I have a reasonable lab setup but if you don’t you will need to get the software. The easy way to do that is download it from the Eval Center. My next move was to sign up for the MS Virtual Academy, there are a number of session there like that will help out: · Microsoft Virtualization for VMware Professionals – Management · Microsoft Virtualization for VMware Professionals – VDI · Enhancing Your Business and Career with the Private Cloud · Planning, Building and Managing a Private Cloud · System Center Virtual Machine Manager 2012 FYI currently my ranking is 266152, I think that I can change that in a few days. J Don’t forget about TechNet Cloud Hub. Luck for me, MS Canada TechNet team sent out a link to a free eBook for 70-659, you can download yourself and you don’t need to be a Canadian to get it either! J Don’t tell any of the MS guys but I have loaded it on my iPad already. J Finally, I printed off the skills being measured for this exam. http://www.microsoft.com/learning/en/us/exam.aspx?ID=70-659#tab2 Are you up to the Challenge? Did you think that you can write this exam by Dec 31, 2011?
|
-
Select distinct
ou.system_ou_name0,
sys.Netbios_Name0,
fcm.SiteCode,sys.User_Domain0,
sys.User_Name0,
-- DATEADD(mi,(usr.lastLogontimeStamp0 / 600000000) - 157258080,0) AS [Last Logon Time Stamp (UTC)],
usr.company0,
sys.Operating_System_Name_and0,
Displayname0,
Version0,
InstallDate0
FROM
dbo.v_R_System sys
JOIN dbo.v_r_User usr on SYS.user_name0 = usr.user_name0
join dbo.v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID
JOIN dbo.v_FullCollectionMembership fcm on sys.ResourceID = fcm.ResourceID
left outer join dbo.v_RA_System_SystemOUName OU on sys.ResourceID = OU.ResourceID
WHERE
fcm.CollectionID=@CollID
and ou.system_ou_name0 = 'enhansoft.com/WORKSTATIONS'
and (lower(DisplayName0) like '%µTorrent%'
or lower(DisplayName0) like '%skype%'
or lower(DisplayName0) like '%Remote PC%'
or lower(DisplayName0) like '%AOL%'
or lower(DisplayName0) like '%Ares%'
or lower(DisplayName0) like '%Galaxy%')
|
-
select
v_R_System.Name0 as 'Computername',
Count(v_StateNames.Statename) as 'Required Updates'
from
v_StateNames,
v_Update_ComplianceStatusAll
Inner Join v_R_System On (v_R_System.ResourceID = v_Update_ComplianceStatusAll.ResourceID)
Inner Join v_UpdateInfo On (v_UpdateInfo.CI_ID = v_Update_ComplianceStatusAll.CI_ID)
where
v_StateNames.TopicType = 500 and
v_StateNames.StateID = v_Update_ComplianceStatusAll.Status and
v_StateNames.Statename = 'Update is required'
Group By
v_R_System.Name0
Having
count(*) > @Var
|
-
http://social.technet.microsoft.com/Forums/en-US/configmgrai/thread/c23b6c8f-57a7-4bdf-932e-e28060d4323d
select
CS.Name0,
NAC.IPAddress0
from
dbo.v_GS_COMPUTER_SYSTEM CS
join dbo.v_GS_NETWORK_ADAPTER_CONFIGUR NAC on CS.ResourceID = NAC.ResourceID
Where
NAC.IPAddress0 != ''
Group by
CS.Name0,
NAC.IPAddress0
Having
count(NAC.IPAddress0) > 1
.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; }
|
-
-
Select distinct
SD.Name0 'Machine Name',
HARP.DisplayName0 as '32bit',
HARP.Version0,
HARP64.DisplayName0 as '64bit',
HARP64.Version0,
SD.User_Name0 'User Name'
From
v_R_System SD
Join v_FullCollectionMembership FCM on SD.ResourceID = FCM.ResourceID
Join v_Collection COL on FCM.CollectionID = COL.CollectionID
left outer join dbo.v_HS_ADD_REMOVE_PROGRAMS HARP on SD.ResourceID = HARP.ResourceID
left outer join dbo.v_HS_ADD_REMOVE_PROGRAMS_64 HARP64 on SD.ResourceID = HARP64.ResourceID
Where
COL.Name = 'All Systems'
and (HARP.DisplayName0 = 'Configuration Manager Client' or HARP64.DisplayName0 = 'Configuration Manager Client')
Order By
SD.User_Name0
|
-
SELECT DISTINCT
a.Netbios_Name0 AS 'Computer Name',
CASE WHEN ARP.OMSA IS NULL THEN 'N/A' ELSE ARP.OMSA END AS 'Dell OMSA Version',
CASE WHEN ARP.Equallogic IS NULL THEN 'N/A' ELSE ARP.Equallogic END AS 'Broadcom Version'
FROM
v_R_System_Valid a
INNER JOIN v_GS_COMPUTER_SYSTEM CS on CS.ResourceID = a.ResourceID
INNER JOIN v_FullCollectionMembership FCM on FCM.ResourceID = a.ResourceID
--INSTALLED SOFTWARE--
LEFT OUTER JOIN (SELECT ResourceID,
MAX(CASE WHEN DisplayName0 ='Dell OpenManage Server Administrator' THEN Version0 ELSE 'Not Installed' END) AS 'OMSA',
MAX(CASE WHEN DisplayName0 ='Dell Equallogic Host Integration Tools' THEN Version0 ELSE 'Not Installed' END) AS 'Equallogic'
FROM
v_ADD_REMOVE_PROGRAMS
WHERE
ProdID0 LIKE 'Microsoft SQL Server%'
OR DisplayName0 = 'Dell OpenManage Server Administrator'
OR DisplayName0 = 'Dell Equallogic Host Integration Tools'
GROUP BY
ResourceID) ARP ON ARP.ResourceID = a.ResourceID
Where
FCM.CollectionID = 'SMS00001'
Order by
a.Netbios_Name0
|
More Posts Next page »
|
|
|