To Record all softwares machine wise

This query can be used to record all softwares machines against collection.

NOTE :- This is very expensive query on your database, please use carefully.

select

v_R_System.Netbios_Name0‘Computer Name’,

v_GS_OPERATING_SYSTEM.Caption0 ‘OS Details’,

v_R_System.User_Name0 ‘User name’,

v_R_System.User_Domain0‘User domain’,

v_RA_System_IPAddresses.IP_Addresses0‘IP Address’,

v_RA_System_IPSubnets.IP_Subnets0‘Subnet’,

MAC.MAC_Addresses0 ‘MAC ADDRESS’,

v_Add_Remove_Programs.DisplayName0‘Software Display Name’,

v_Add_Remove_Programs.Publisher0‘Publisher’,

v_Add_Remove_Programs.RevisionID‘Revision’,

v_Add_Remove_Programs.Version0‘Version’,

v_Add_Remove_Programs.InstallDate0‘Installed Date’,

ins.InstalledLocation00 ‘installed path’,

sta.LastHWScan ‘Last H/w Scan’

from v_Add_Remove_Programs

join CollectionMembers on CollectionMembers.MachineID = v_Add_Remove_Programs.ResourceID

join Collections on Collections.SiteID = CollectionMembers.SiteID

join v_R_System on v_R_System.ResourceID = v_Add_Remove_Programs.ResourceID

join v_GS_WORKSTATION_STATUS sta on sta.resourceid = v_R_System.ResourceID

join v_RA_System_IPSubnets on v_RA_System_IPSubnets.ResourceID =v_R_System.ResourceID

join v_RA_System_IPAddresses on v_RA_System_IPAddresses.ResourceID =v_R_System.ResourceID

join INSTALLED_SOFTWARE_DATA ins on ins.MachineID = v_R_System.ResourceID

join v_GS_OPERATING_SYSTEM on v_GS_OPERATING_SYSTEM.ResourceID =v_R_System.ResourceID

JOIN v_RA_System_MACAddresses MAC ON MAC.ResourceID = v_R_System.ResourceID

join v_GS_SOFTWARE_TAG tag on tag.ResourceID = v_R_System.ResourceID

and v_Add_Remove_Programs.DisplayName0 not like ‘Security Update%’

and v_Add_Remove_Programs.DisplayName0 not like ‘Hotfix for %’

and v_Add_Remove_Programs.DisplayName0 not like ‘Update for Windows %’

and v_Add_Remove_Programs.DisplayName0 not like ‘Configuration Manager Client’

and v_RA_System_IPAddresses.IP_Addresses0 not like ‘0.0.0.0’

and v_RA_System_IPAddresses.IP_Addresses0 not like ‘%::%’

and Collections.CollectionName in (‘All systems’)

Advertisements
Posted in Uncategorized | Leave a comment

Count of all softwares

The below query can be used find count of all the softwares registered in your hierarchy

 

select  app.DisplayName0‘Program name’,app.Version0,app.Publisher0 ‘Publisher’, COUNT (*)‘Count of Instance’ from v_Add_Remove_Programs app

group by DisplayName0,app.Publisher0, app.Version0

 

Posted in Uncategorized | Leave a comment

Server Rejected registration Request: 3 (ClientIDManagerStartup.log)

Issue :-

we can see below error message in ClientIDManagerStartup.log

Server Rejected registration Request: 3

Solution :-

1)    Identify the system.
2)    Delete the c:\windows\smscfg.ini file
3)    Run CCMDELCERT on the client
4)    Restart the SMS agent Host service.
5)    A new GUID will be generated

 

It will take about 15 to 20 min to get the policy from Primary.

Posted in Troubleshooting | Tagged | Leave a comment

Enable CLR in SQL 2008

 Problem :-

You will find below error in statesys.log when your CLR of SQL is disabed.

*** [42000][6263][Microsoft][ODBC SQL Server Driver][SQL Server]Execution of user code in the .NET Framework is disabled. Enable “clr enabled” configuration option.

Solution :-

First run the below query to check if CLR is enabled. The output should be equal to 1.

SELECT name, value FROMsys.configurationsWHERE name = ‘clr enabled’

If the output is Zero from the above query, then execute the below query to enable CLR.

sp_configure

‘show advanced options’, 1;

GO

RECONFIGURE;

GO

sp_configure

‘clr enabled’, 1;

GO

RECONFIGURE;

GO

Posted in Troubleshooting | Tagged | Leave a comment

Track movies, mp3 and photos from SCCM 2007

Agenda
Track movies, mp3 and photos from SCCM 2007

solution :-

To acheive this, we need to enable inventory all requested format in SCCM 2007.

123

 

After enabling the above settings, use the below query to get the details.

select

distinct

sys.Netbios_Name0 ‘Computer Name’,

sys.User_Domain0 ‘Last Logged on Domain’,

sys.User_Name0 ‘Last Logged on User’,

soft.FileName‘File Name’,

soft.FileDescription ‘File description’,

soft.FileVersion ‘File version’,

soft.FilePath ‘File Path’

from

v_GS_SoftwareFile soft

join v_R_System sysonsys.ResourceID = soft.ResourceID

join CollectionMembers coll on Coll.MachineID = soft.ResourceID

join Collections cols on cols.SiteID = Coll.SiteID

where soft.FileName like ‘%.AVI’

Or soft.FileName like ‘%.MPEG’

Or soft.FileName like ‘%.asf’

Or soft.FileName like ‘%.WMV’

Or soft.FileName like ‘%.MPEG1’

Or soft.FileName like ‘%.MPG’

Or soft.FileName like ‘%.M1V’

Or soft.FileName like ‘%.MP2’

Or soft.FileName like ‘%.WAV’

Or soft.FileName like ‘%.SND’

Or soft.FileName like ‘%.AU’

Or soft.FileName like ‘%.AIF’

Or soft.FileName like ‘%.AIFC’

Or soft.FileName like ‘%.AIFF’

Or soft.FileName like ‘%.ASF’

Or soft.FileName like ‘%.WM’

Or soft.FileName like ‘%.WMA’

Or soft.FileName like ‘%.WMV’

Or soft.FileName like ‘%.BMP’

Or soft.FileName like ‘%.JPG’

Or soft.FileName like ‘%.JPEG’

Or soft.FileName like ‘%.JPE’

Or soft.FileName like ‘%.JFIF’

Or soft.FileName like ‘%.GIF’  

Or soft.FileName like ‘%.MP3’

Or soft.FileName like ‘%.AIF’

and cols.CollectionName in(‘all systems’)

group by cols.CollectionName, sys.Netbios_Name0, sys.User_Domain0, sys.User_Name0, soft.FileName, soft.FileDescription, soft.FileVersion, soft .FilePath

Posted in SMS Query | Tagged , , | Leave a comment

0x8004100e in distmgr.log

error CWmi::Connect(): ConnectServer(Namespace) failed. – 0x8004100e in distmgr.log
If you see this error in distmgr.log on a DP, then take the following steps:

1. Windows server 2008 : Using Server manager, the the IIS 6 WMI Metabase compatibility feature and II6 WMI compatibility feature.

2. Windows server 2003 : Try to reinstall IIS, select all IIS components, ASP, Webdav, BITS etc and enable them in internet services manager. On the Central site console, uncheck the option “Use Bits to download packages from this DP”. Wait for some time.

Posted in Troubleshooting | Tagged , | Leave a comment

SCCM collection for systems with IE 9

Use the below Query to create collections for the systems with Internet explorer 9

Note :- This query will work only, when you have enabled inventory collection for *.exe files.

select distinct SMS_R_System.NetbiosName, SMS_R_System.LastLogonUserName, SMS_R_System.LastLogonUserDomain, SMS_R_System.SMSAssignedSites, SMS_G_System_SoftwareFile.FileVersion from SMS_R_System inner join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SoftwareFile.FilePath like “%\\Program Files\\Internet Explorer\\” and SMS_G_System_SoftwareFile.FileName like “iexplore.exe” and SMS_G_System_SoftwareFile.FileVersion like “9.%”

Posted in SMS Query | Tagged , | Leave a comment