Friday, July 10, 2015

Determining installed applications last use time

ConfigMgr will definitely be able to let SCCM admins know what are installed in the environment regardless if it is a server or workstation. SCCM by default comes with a set of reports that SCCM admins can utilize to gather information. The one that I am going to mention today is , "Computers that has a metered program but have not run the program since a specific date". What is so special about this report you may think, I had the same thinking as you but it is pretty interesting to know how is this data derived.
The data set for this is based on Software Inventory and this means gathering file types that are on machines. The last used time is gathered asp art of software metering.
This report will provide the information based on the files inventoried and their respective last used date. Nothing unique right?
Here comes the interesting part, picture the scenario as below.
All of us will know that in order for Microsoft Office to be used, it has to be installed and after installation, it will appear under the machine's add/remove program. This report which I mentioned reports based on not a usable application but just the executable file. This means that if I copy excel.exe from another machine to my desktop, it will take it that a working copy of Excel exists of my machine and report against that. Not very smart and logical huh?
For me, I would require to report against what has been installed and not used and not any executable files that is related to the application.
To address this, I have put together my own query as below. Hopefully it would help someone who could be in the same boat as I was. :)

distinct VRS1.Name0,arp1.DisplayName0,z.ProductName,z.[Last Use],
when VRS1.Obsolete0 = 0  then 'Active'
when VRS1.Obsolete0 = 1  then 'inactive'
end as [SCCM Client Status]
,z.FileName,substring(OU.System_OU_Name0,15,3) [Site],VRS1.User_Name0,
when d.IsMobile0 = 1 then 'Laptop'                
when d.IsMobile0 = 0  and e.SystemRole0 = 'Workstation' then 'Desktop'
when d.IsMobile0 = 0  and e.SystemRole0 = 'Server' then 'Server'
end as [Type]
from v_Add_Remove_Programs ARP1
Left join(Select distinct VRS.resourceid,VRS.name0,ARP.DisplayName0, mf.ProductName,
MF.FileName,MAX(mu.LastUsage)[Last Use]

from v_R_System VRS
left join v_MonthlyUsageSummary MU on MU.ResourceID = VRS.ResourceID
left join v_Add_Remove_Programs ARP on vrs.ResourceID = arp.ResourceID
left join v_MeteredFiles MF on MF.MeteredFileID = Mu.FileID

where arp.DisplayName0 like '%App Name%' and  mf.FileName = 'App Exe File'
Group by vrs.Name0,vrs.ResourceID,arp.DisplayName0,MF.FileName,mf.productname)Z on z.ResourceID = ARP1.resourceid
Left join v_R_System VRS1 on VRS1.ResourceID = ARP1.ResourceID
left join v_RA_System_SystemOUName OU on ou.ResourceID = VRS1.ResourceID
inner join v_GS_PROCESSOR d on d.ResourceID = VRS1.ResourceID
inner join v_GS_SYSTEM e on e.ResourceID = VRS1.resourceid

where arp1.DisplayName0 like '%App Name%' and vrs1.Name0 is not null

Friday, July 3, 2015

Query for SCCM 2012 DP Status Overview

SCCM Distribution points as we may know is an important part of an SCCM Setup.
A distribution point which is not operational due to various reasons would have repercussion effects on a package deployment. Issues that arises may be from the perspective of network bandwidth etc.
It is always welcome to have a single pane view of teh status of anything that we need to have visibility on and this includes the status of the DPs.
The SQL query below will enable you to gain that "Single Pane" view from the available disk space as well as the settings that are configured which is inclusive of Transfer rate among others.

SELECT [ServerName]
     , DI.Drive
  FROM [CM_C00].[dbo].[v_DistributionPointInfoBase] IB
  left join dbo.v_DistributionPointDriveInfo DI on DI.NALPath = IB.NALPath

Thursday, June 18, 2015

Mass deleting Obsolete SCCM Clients

I would believe that every SCCM Admin would at some point of time need to cleanup obsolete SCCM Client objects from the SCCM Sites.
Most of the time when this is needed, all you will be provided is a list of the machines that are no longer in Active Directory and these SCCM Clients has to be purged from SCCM.
For this purpose, the below VBS will do just that

Const ForReading = 1, ForWriting = 2, ForAppending = 8
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
I = 0

strServer=InputBox("Enter Site Server Nmae",,"SCCM-Pri-Site-Server")
strSiteCode=InputBox("Enter Site Code",,"P01")

Set fso = CreateObject("Scripting.FileSystemObject")
strfolder = fso.GetParentFolderName(wscript.ScriptFullName) 

'You may replace obsolete.txt with any text file name you prefer. The contents of this file is used to list out the SCCM clients that are to be purged.
FileName = strfolder & "\Obsolete.txt"
'Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = fso.OpenTextFile(Filename,ForReading)
strmachine = objFile.ReadAll

arrcomp = split(strmachine, vbCrLf)
if isnull(strmachine) then
wscript.echo "No Machine info"
For each strmachine in arrcomp
strcomputer = strmachine
Set locator = CreateObject("WbemScripting.SWbemLocator")
Set WbemServices1 = locator.ConnectServer(strServer,"root\SMS\site_" & strSiteCode)
ResID = getResID(strComputer, WbemServices1)

Set sResource = WbemServices1.Get("SMS_R_System='" & ResID & "'")
Set sResource = Nothing
I = I +1

wscript.echo "Housekeep Done! A total of " & I & " obsolete SCCM records were deleted"
End if

Function getResID(strComputer, oWbem)
strQry = "Select ResourceID from SMS_R_System where Name=" & "'" & strComputer & "'"
Set objEnumerator = oWbem.ExecQuery(strQry)
If Err <> 0 Then
GetResID = 0
Exit Function
End If
For Each objInstance in objEnumerator
For Each oProp in objInstance.Properties_
GetResID = oProp.Value
Set objEnumerator = Nothing
End Function