Month – November 2011

Challenge aka 70-659

    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 […]

Query on Prohibited application for specific OU

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 = […]

Show list of computers whithout more than 30 criticals patches not installed

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

Query to find multiple Net bois names on single IP

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: […]

Don’t you love emails like this one!

I left ITProTeach (DevTeach) a bit early so that I could head home to pack before my long overdue vacation! After getting back, I found this nice surprise in my inbox!   If you want to download the PPT, you can find it in Enhansoft’s blog http://support.enhansoft.com/Blogs/post/Diving-Deep-in-the-SCCM-data!-Bring-your-Swim-trunks!-e28093-Part-2.aspx http://support.enhansoft.com/Blogs/post/Diving-Deep-in-the-SCCM-data!-Bring-your-Swim-trunks!.aspx