File Inventory Report Query by Name, Count and Computer name

Useful script, refer from here : http://eskonr.com/2013/11/sccm-configmgr-reportcount-specific-inventory-file-on-computers/

select cs.Name0 [Computer name],COUNT(*) as [No of Times]
from v_GS_SoftwareFile SF,v_GS_COMPUTER_SYSTEM cs
where FileName like '%.mdf%' and
SF.ResourceID=cs.ResourceID
group by cs.Name0
order by 2 desc

Removing collection from the database that didn’t exist in the console

I notice there is some collection missing in console but exist in database. After do some research, it will become an issue while upgrading from SCCM 2007 to SCCM 2012.

First, run sql below:

Select * from collections

Then identified which collection does not appear in console and delete by sql below:

DELETE FROM collections
WHERE CollectionName='Your Collection'

If you are interested to know more about the issue, Check It Out

Warning: It is not supported to modify the ConfigMgr database in any way. 

Office 2010 Version Numbers

1. Open an Office 2010 application such as Microsoft Word 2010.
2. On the File tab, click Help. You will see the version information in the About Microsoft <ApplicationName> section. 
3. The version number of Office 2010 SP2 is greater than or equal to 14.0.7015.1000.
4. The version number of Office 2010 SP1 is greater than or equal to 14.0.6029.1000 but less than 14.0.7015.1000.
5. The version number of the original RTM release of Office 2010 (that is, with no service pack) is greater than or equal to 14.0.4763.1000 but less than 14.0.6029.1000.

Server Up Time Report

Here is the report for server up time:

Select os.Caption0 as 'Operating System',
cs.Name0 as Name,
DateDiff(hour,os.LastBootUpTime0,ws.LastHWScan) as 'Uptime (in Hours)',
CONVERT(varchar(20),os.LastBootUpTime0,100) as 'Last Reboot Date/Time',
CONVERT(varchar(20),ws.LastHWScan,100) as 'Last Hardware Inventory'
From
dbo.v_GS_WORKSTATION_STATUS ws Left Outer Join dbo.v_GS_Operating_System os
on ws.ResourceID = os.ResourceID
inner join dbo.v_GS_COMPUTER_SYSTEM cs
on cs.ResourceID = os.ResourceID
WHERE os.Caption0 LIKE '%server%'
and ws.LastHWScan <> 0 and cs.Name0 is not null
Order by Cs.Name0

Report to show major Internet Explorer version

Here is the query to show major Internet Explorer

select
    SF.FileName,
    OS.Caption0 as 'OS Version',
    replace(left(SF.FileVersion,2), '.','') as 'IE Version',
    Count (Distinct SF.ResourceID) as 'Total'
From
    dbo.v_GS_SoftwareFile SF
    JOIN v_FullCollectionMembership fcm on SF.ResourceID=fcm.ResourceID
    JOIN dbo.v_GS_OPERATING_SYSTEM OS ON SF.ResourceID = OS.ResourceID
    join dbo.v_GS_SYSTEM S on SF.ResourceID = S.ResourceID
Where
    SF.FileName = 'iexplore.exe'
    and SF.FilePath like '%Internet Explorer%'
    and S.SystemRole0 = 'Workstation'
Group by
    SF.FileName,
    OS.Caption0,
    replace(left(SF.FileVersion,2), '.','')
Order by OS.Caption0

But did you notice there is IE9 on Windows XP workstation, after checking on that workstation, found that the workstation running on dual OS( Win XP+Win 7 ) :

Summary of harddisk space used by exe

First make sure your Software Inventory Client Agent collect *.exe

Here is the report that query *.exe

select

            v_GS_COMPUTER_SYSTEM.Name0 as 'PC Name',

            v_GS_COMPUTER_SYSTEM.UserName0 as 'User Name',

            sum(v_GS_SoftwareFile.fileSize/1024/1024) as 'Size in MB'

from

            v_GS_SoftwareFile,

            v_GS_COMPUTER_SYSTEM

Where

            v_GS_SoftwareFile.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID

            and v_GS_SoftwareFile.FileName like ('%.exe')

Group by

            v_GS_COMPUTER_SYSTEM.Name0,

            v_GS_COMPUTER_SYSTEM.UserName0

Order By

            v_GS_COMPUTER_SYSTEM.Name0,

            v_GS_COMPUTER_SYSTEM.UserName0

Report on File System Shares

Go to \Microsoft Configuration Manager\Inboxes\clifiles.src\hinv\SMS_DEF.mof , edit accordingly :

[ SMS_Report     (TRUE),
  SMS_Group_Name ("Shares"),
  SMS_Class_ID   ("MICROSOFT|SHARE|1.0") ]

class Win32_Share : SMS_Class_Template
{
    [SMS_Report (TRUE), SMS_Units("DecimalString")]
        uint32     AccessMask;
    [SMS_Report (TRUE)     ]
        boolean    AllowMaximum;
    [SMS_Report (TRUE)     ]
        string     Caption;
    [SMS_Report (TRUE)     ]
        string     Description;
    [SMS_Report (TRUE)     ]
        datetime   InstallDate;
    [SMS_Report (TRUE)     ]
        uint32     MaximumAllowed;
    [SMS_Report (TRUE), key]
        string     Name;
    [SMS_Report (TRUE)     ]
        string     Path;
    [SMS_Report (TRUE)     ]
        string     Status;
    [SMS_Report (TRUE), SMS_Units("DecimalString")]
        uint32     Type;
};

 Ps.: Only change to TRUE the string that you want to use.

Add SQL query as below :

SELECT     dbo.v_R_System.Name0 AS 'Computer', dbo.v_GS_SHARE.Path0 AS 'Path', dbo.v_GS_SHARE.Name0 AS 'Name',
                    dbo.v_GS_SHARE.Caption0 AS 'Caption', dbo.v_GS_SHARE.Status0 AS 'Status'
FROM         dbo.v_GS_SHARE INNER JOIN
                      dbo.v_R_System ON dbo.v_GS_SHARE.ResourceID = dbo.v_R_System.ResourceID
ORDER BY 'Computer'


Or you can use some filter to disable administrative shares

SELECT TOP (100) PERCENT dbo.v_GS_SHARE.ResourceID, dbo.v_R_System.Name0 AS Hostname, dbo.v_GS_SHARE.Name0 AS ShareName,
dbo.v_GS_SHARE.Path0 AS Path
FROM dbo.v_GS_SHARE INNER JOIN
dbo.v_R_System ON dbo.v_GS_SHARE.ResourceID = dbo.v_R_System.ResourceID
WHERE (dbo.v_GS_SHARE.Name0 NOT LIKE ‘_$’) AND (dbo.v_GS_SHARE.Name0 <> ‘admin$’) AND (dbo.v_GS_SHARE.Name0 <> ‘ipc$’)


Ps.: This is a simple query that shows all shares for all computers.
Download the attached MOF file and import this in your SCCM to have a complete report.
SCCM_Report_Network_Shares.zip