Collecting the user's FULL NAME in the SCCM Database

Step 1:  Check to see if you're already gathering the full username by going to Collections, All Users.  Do you have usernames listed?  If so, pick one (like your own), right-click properties on it, and look through the Discovery Data, i.e., looking for Full User Name.  If you're already gathering the information, then it's just a matter of designing a custom report correctly.  If you aren't Discovering Users out of AD, then check to confirm you have "Active Directory User Discovery" enabled in Site Management, <your primary site>, Site Settings, Discovery Methods.  You may need to click on the * and add in the right LDAP location.  Monitor Logs\adUsrDis.log to confirm user discovery is working right to pull in user information.  Once it's done (per the log), go update/refresh th All Users collection and make sure you see the Full User Name now.

Step 2:  Linking last logged on user to the full name.... something like...


select


sys.netbios_name0, usr.Full_User_Name0,usr.Unique_User_name0,usr.User_Name0from v_r_user Usrjoin v_r_system sys on sys.user_name0=usr.User_name0

NOTE: sometimes, people may not login as <short username> and select the domain from the pull-down menu.  So if you think you might have that, where people type in DomainName\Username, and then their password, the join won't match up right.  You'd need a report like this:


select

sys.netbios_name0, usr.Full_User_Name0,usr.Unique_User_name0,usr.User_Name0from v_r_user Usrjoin v_r_system sys on sys.user_name0=usr.Unique_User_name0

Useful Software Metering Rules

One way to ensure a really fast ROI with Configuration Manager is usage of Software Metering. Creating the rules is a simple process but gathering the exe names of the software packages can be time consuming. To save you some time I have added some of the most commonly used products.

Application	Filename	Vendor
Visio	Visio.exe	Microsoft
Project	winproj.exe	Microsoft
Frontpage	Frontpg.exe	Microsoft
Access	Msaccess.exe	Microsoft
Autocad	Autocad.exe	Autodesk
Acrobat Writer std	Acrobat.exe	Adobe
Acrobat Writer Prof.	Acrobat.exe	Adobe
Illustrator	Illustrator.exe	adobe
Image Ready	ImageReady.exe	Adobe
InDesign	InDesign.exe	Adobe
Photoshop	Photoshop.exe	Adobe
DreamWeaver	DreamWeaver.exe	Adobe
Flash Prof.	Flash.exe	Adobe
Crystal Reports	crw32.exe	Business Objects

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

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