SCCM Report count needed patches

This report will show the needed patches count:

**********************************************

select

      CS.Name0,

      CS.UserName0,

case

when (sum(case when UCS.status=2 then 1 else 0 end))>0 then ('Needs '+(cast(sum(case when UCS.status=2 then 1 else 0 end)as varchar(10))+ ' Patches'))

else 'Good Client'

end as 'Status',

      ws.lasthwscan as 'Last HW scan',

      FCM.collectionID--,

from

      v_UpdateComplianceStatus UCS

left outer join dbo.v_GS_COMPUTER_SYSTEM  CS on CS.ResourceID = UCS.ResourceID

join v_CICategories_All catall2 on catall2.CI_ID=UCS.CI_ID

join v_CategoryInfo catinfo2 on catall2.CategoryInstance_UniqueID = catinfo2.CategoryInstance_UniqueID and catinfo2.CategoryTypeName='UpdateClassification'

left join v_gs_workstation_status ws on ws.resourceid=CS.resourceid

left join v_fullcollectionmembership FCM on FCM.resourceid=CS.resourceid

Where

      UCS.Status = '2'

and FCM.collectionid = 'SMS00001'

Group by

      CS.Name0,

      CS.UserName0,

      ws.lasthwscan,

      FCM.collectionID

Order by

      CS.Name0,

      CS.UserName0,

      ws.lasthwscan,

      FCM.collectionID

***************************************************

Read more →

SCCM Report Collections with maintenance windows

This report will show only the collection in your SCCM that have a maintenance windows configured:

Read more →

VBS Script to delete Folder

You can this simple .vbs script to delete folders:


************************************************


 '====================================================================
'
' NAME: 
'
' AUTHOR:  
' DATE  : 07/11/2012
'
' COMMENT: 
'exemplary damages arising out of or in any way relating to the use of this script, 
'including without limitation damages for loss of goodwill, work stoppage, 
'lost profits, loss of data, and computer failure or malfunction. 
'You bear the entire risk as to the quality and performance of this script.
'
'===================================================================

dim filesys
Set filesys = CreateObject("Scripting.FileSystemObject")
If filesys.FolderExists("c:\example\") Then 
   filesys.DeleteFolder "c:\example"
     End If 


************************************************************

Read more →

SCCM Report Advertisement detailed

This report will show you the current status of your running advertisements:

Read more →

SCCM Report printer inventory

This useful report will show a complete inventory of your network printers:

*******************************************************************

SELECT    

'Numero Total de Impresores' AS Type, COUNT(PPR.DeviceID0) AS [Count of Devices]
FROM         dbo.v_GS_PRINTER_DEVICE PPR INNER JOIN
                      dbo.v_R_System ON PPR.ResourceID = dbo.v_R_System.ResourceID
INNER JOIN V_fullCOLLECTIONmembership CCO ON CCO.ResourceID = PPR.ResourceID
WHERE  CCO.CollectionID like 'SMS000DS' AND (PPR.Caption0 NOT LIKE 'Microsoft Office Document%' AND PPR.Caption0 NOT LIKE 

'%pdf%' AND PPR.Caption0 NOT LIKE '%Microsoft XPS%')


UNION

SELECT  'Numero Total PrintServers' AS Type, COUNT( DISTINCT dbo.v_R_System.ResourceID) AS [Count of Devices]
FROM         dbo.v_R_System INNER JOIN
                      dbo.v_GS_PRINTER_DEVICE ON dbo.v_R_System.ResourceID = dbo.v_GS_PRINTER_DEVICE.ResourceID
INNER JOIN V_fullCOLLECTIONmembership ON v_fullcollectionmembership.ResourceID = dbo.v_GS_Printer_Device.ResourceID
WHERE  v_Fullcollectionmembership.CollectionID like 'SMS000DS'

SELECT Distinct SYS.Netbios_Name0,PRT.DriverName0, PRT.DeviceID0,PRT.PortName0 
FROM v_R_System SYS 
INNER JOIN v_GS_PRINTER_DEVICE PRT on SYS.ResourceID = PRT.ResourceID
INNER JOIN V_fullCOLLECTIONmembership CCO ON cco.resourceID  = SYS.ResoUrceID

WHERE  CCO.COLLECTIONid  like 'SMS000DS'AND (PRT.Caption0 NOT LIKE 'Microsoft Office Document%' AND PRT.Caption0 NOT LIKE 

'%pdf%' AND PRT.Caption0 NOT LIKE '%Microsoft XPS%')
order by Netbios_Name0

*******************************************************

Read more →

SCCM Query To check machine RAM Memory

This query will show the machine name, IP address, and ram memory:

********************************

select distinct 

SMS_R_System.NetbiosName, 

SMS_G_System_PC_BIOS.SerialNumber, SMS_G_System_X86_PC_MEMORY.TotalPhysicalMemory, 

SMS_R_System.IPAddresses, SMS_G_System_NETWORK_ADAPTER_CONFIGURATION.DNSDomain 

from  SMS_R_System inner join SMS_G_System_PC_BIOS 

on SMS_G_System_PC_BIOS.ResourceID = SMS_R_System.ResourceId 

inner join SMS_G_System_X86_PC_MEMORY 

on SMS_G_System_X86_PC_MEMORY.ResourceID = SMS_R_System.ResourceId 

inner join SMS_G_System_NETWORK_ADAPTER_CONFIGURATION 

on SMS_G_System_NETWORK_ADAPTER_CONFIGURATION.ResourceID = SMS_R_System.ResourceId 

order by SMS_R_System.NetbiosName

*****************************************

Read more →

SCCM Query to check Client version

This simple query will show the SCCM client version (works with SCCM 2007 and 2012):



select distinct
 SYS.Name0 as 'Machine Name',
 SYS.User_Name0 as 'Login ID',
 SYS.User_Domain0 as 'Domain',
 USR.Full_User_Name0 as 'Full Name',
 case
  when SYS.Client0 = 1 then 'Yes'
  when SYS.Client0 = 0 then 'No'
  else convert(varchar(2), SYS.Client0)
 end as 'Client Installed',
 SYS.Client_Version0 as 'Client Version'
from
 v_R_System SYS
 join v_FullCollectionMembership FCM on SYS.ResourceID = FCM.ResourceID
 join v_Collection CN on FCM.CollectionID = CN.CollectionID
 join v_R_User USR on SYS.User_Name0 = USR.User_Name0
where
 CN.Name = 'All Systems'

Read more →

SCCM 2012 query machines not contacting from 15 Days

You can apply this query on SCCM 2007/2012. This query will show the machines that are not contacting with SCCM from the last 15 days. 

****************************************

select 
SMS_R_System.ResourceID,
SMS_R_System.ResourceType,
SMS_R_System.Name,
SMS_R_System.SMSUniqueIdentifier,
SMS_R_System.ResourceDomainORWorkgroup,
SMS_R_System.Client from SMS_R_System where AgentName in ("Heartbeat Discovery") and DATEDIFF(day,AgentTime,GetDate())>=15

***************************************

Read more →

Script to extract users data from Active Directory (VBS)

This script will extract users data (machine name, last logon,OS,OU name) from your AD and it will autocratically save into a .csv file :



********************************************

'====================================================================
'
' NAME: 
'
' AUTHOR:  
' DATE  : 11/01/2012
'
' COMMENT: 
'Exemplary damages arising out of or in any way relating to the use of this script, 
'including without limitation damages for loss of goodwill, work stoppage, 
'lost profits, loss of data, and computer failure or malfunction. 
'You bear the entire risk as to the quality and performance of this script.
'
'
'====================================================================

Set objFSO = CreateObject("Scripting.FileSystemObject")

Dim HostName, DisName, Domain 
Dim Ruta(51) 

    'Sheets("AD_Computers").Select

Set adoCommand = CreateObject("ADODB.Command")
Set ADOConnection = CreateObject("ADODB.Connection")
ADOConnection.Provider = "ADsDSOObject"
ADOConnection.Open "Active Directory Provider"
adoCommand.ActiveConnection = ADOConnection

' Search entire Active Directory domain.
Set objRootDSE = GetObject("LDAP://RootDSE")
strDNSDomain = objRootDSE.Get("defaultNamingContext")
strBase = "<LDAP://" & strDNSDomain & ">"

' Filter on computer objects.
strFilter = "(&(objectCategory=computer))"

' Comma delimited list of attribute values to retrieve.
strAttributes = "name,distinguishedName,lastLogonTimestamp,operatingSystem"

' Construct the LDAP syntax query.
strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"
adoCommand.CommandText = strQuery
adoCommand.Properties("Page Size") = 100
adoCommand.Properties("Timeout") = 30
adoCommand.Properties("Cache Results") = False

' Run the query.
Set adoRecordset = adoCommand.Execute
Domain = "Domain.COM"

    Set FICHERO = objFSO.opentextfile("AD_COMPUTERS.csv", 2, True)
     FICHERO.WRITELINE "Machine Name;lastLogonTimeStamp;Operating System;System OU Name"
    FICHERO.Close

' Enumerate the resulting recordset.

Do Until adoRecordset.EOF
i = i + 1

    On Error Resume Next
    HostName = adoRecordset.Fields("name").Value
    DisName = adoRecordset.Fields("distinguishedName").Value
OS = adoRecordset.Fields("operatingSystem").Value

   Set objDate = adoRecordset.Fields("lastLogonTimeStamp").Value
    
    If (Err.Number <> 0) Then
        On Error GoTo 0
        dtmDate = #1/1/1601#
    Else
        On Error GoTo 0
        lngHigh = objDate.HighPart
        lngLow = objDate.LowPart
        If (lngLow < 0) Then
            lngHigh = lngHigh + 1
        End If
        If (lngHigh = 0) And (lngLow = 0) Then
            dtmDate = #1/1/1601#
        Else
            dtmDate = #1/1/1601# + (((lngHigh * (2 ^ 32)) + lngLow) / 600000000 - lngBias) / 1440
        End If
    End If

    ' Display values for the user.
    If (dtmDate = #1/1/1601#) Then
        Tiempo = "Never"
    Else
        Tiempo = dtmDate
Tiempo = left(Tiempo,InStr(1,Tiempo, " ") -1)
Tiempo = day (Tiempo) & "/" & month (Tiempo)  & "/" & year (Tiempo)
    End If

    distinguishedName = DisName
     

AD = Split(DisName, ",")
    
    i2 = 50
    For Each Item In AD
      If Left(Item, 3) = "OU=" Then
       Ruta(i2) = Replace(Item, "OU=", "/")
       i2 = i2 - 1
      End If
      
    Next
    
    DisName = ""
    For i3 = 1 To 50
    If Ruta(i3) <> "" Then DisName = DisName + Ruta(i3)
    
    Next

    
    If DisName = "" Then DisName = "/COMPUTERS"
    

    Set FICHERO = objFSO.opentextfile("AD_COMPUTERS.csv", 8, True)
     on error resume next
     FICHERO.WRITELINE ucase(HostName) & ";" & Tiempo & ";" & OS & ";" & ucase(Domain & DisName)
     on error goto 0
    FICHERO.Close
    


    'Range("A" & i).Select
   ' Range("A" & i).Value = HostName
   ' Range("B" & i).Value = Domain & DisName
   ' Range("C" & i).Value = distinguishedName

'If i = 5760 Then
'MsgBox "va"
'End If
    'Move to the next record in the recordset.
     adoRecordset.MoveNext
     Erase Ruta

Loop
' Clean up.
adoRecordset.Close
ADOConnection.Close

Set adoRecordset = Nothing
Set objRootDSE = Nothing
Set ADOConnection = Nothing
Set adoCommand = Nothing

msgbox "END"

**************************************

Read more →

SCCM 2012 report count Advertisement

This report will show a simple count of advertisement running on SCCM 2012:

********************************

SELECT     COUNT(LastStateName) AS Count, LastStateName
FROM         dbo.v_ClientAdvertisementStatus
WHERE     (DATEDIFF(m, LastStatusTime, GETDATE()) = 1)
GROUP BY LastStateName

*********************************

Read more →

SCCm Query Firefox Browser version

This Query can be use also in a SCCM report to show machine names and Firefox version installed:

*****************************************

SELECT     TOP (100) PERCENT dbo.v_R_System.Netbios_Name0, dbo.v_GS_SoftwareFile.FileName, dbo.v_GS_SoftwareFile.FileVersion,
                      dbo.v_GS_SoftwareFile.FilePath
FROM         dbo.v_GS_SYSTEM INNER JOIN
                      dbo.v_R_System ON dbo.v_GS_SYSTEM.ResourceID = dbo.v_R_System.ResourceID INNER JOIN
                      dbo.v_GS_SoftwareFile ON dbo.v_GS_SYSTEM.ResourceID = dbo.v_GS_SoftwareFile.ResourceID
WHERE     (dbo.v_GS_SoftwareFile.FilePath LIKE '%Program Files%' or
                      dbo.v_GS_SoftwareFile.FilePath LIKE '%Program Files (x86)%') and (dbo.v_GS_SoftwareFile.FileName like '%firefox.exe%')
ORDER BY dbo.v_R_System.Netbios_Name0

******************************************

Read more →

Sccm Query todetect Dell Machines

This query will target only Dell machines:

********************************


select * from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceID where SMS_G_System_COMPUTER_SYSTEM.Manufacturer like "Dell%"

*******************************

Read more →

Query to check Office 2013 installation

This query will show you if machines have Office 2013 Pro installed:

************************************
select distinct 

SMS_R_System.Name, SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName, SMS_G_System_ADD_REMOVE_PROGRAMS.Version from  SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Microsoft Office Professional Plus 2013" order by SMS_R_System.Name

*************************************

Read more →

This script creates a message box to tell the user that on the next reboot the machine will installed a determined software.then it writes a reg key to run the package or other scripts. You can send this script before running the actual package installation:

******************************************************



' NAME: MsgBox
'
' AUTHOR:  
'
' DATE  : 12/02/2014
'
' COMMENT: create a msgbox for user while writes a reg key in runonce.
'
'exemplary damages arising out of or in any way relating to the use of this script, 
'including without limitation damages for loss of goodwill, work stoppage, 
'lost profits, loss of data, and computer failure or malfunction. 
'You bear the entire risk as to the quality and performance of this script.
'
'$~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Option Explicit 
Dim Fichero
Dim lcScriptPath
Dim lcScriptName
Dim App_Path
Dim oShell,ofso,strRuta
Dim Shell
Dim objFso
Dim strPath
Set ofso = CreateObject("Scripting.FileSystemObject") 
Set oShell = WScript.CreateObject("WScript.Shell")


 MsgBox "A package has been delivered to your computer, please reboot for changes to be applied.", vbOKOnly, "@soft name"
  

Set shell= createobject("WSCRIPT.SHELL")
Set objFso = CreateObject("Scripting.FileSystemObject")
strPath = objFso.GetParentFolderName(WScript.ScriptFullName)


strRuta= "cscript.exe " & strPath & "\2_CopyFileSAPlogon.vbs"


oShell.regwrite "HKEY_CURRENT_USER\SOFTWARE\Microsoft\Windows\CurrentVersion\RunOnce\Copy@@Program.exe@@",strRuta,"REG_SZ"
  
  
  
  strRuta = ""
Set ofso=Nothing
Set oShell= Nothing
WScript.Quit

****************************************************

Read more →

SCCM Empty collections

This report will show all the empty collection in your SCCM:

Read more →

Office 2013 Deployment with MS SCCM 2012

This is a quick guide I found that helps to deploy MS Office 2013 with SCCM 2012:


https://drive.google.com/open?id=0B3O4bQWeKhyBREMzS3ZjSEpYTmM&authuser=0

Read more →

SCCM Report count IE versions

this simple report will show a count of all IE versions in your workstations:

***********************************

select 
    SF.FileName,
    OS.Caption0,
    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 
    2

***********************************

Read more →

SCCM query to check for obsolete clients

This query will help you check obsolete clients in SCCM:



********************************************

select SMS_R_System.ResourceID,
SMS_R_System.ResourceType,
SMS_R_System.Name,SMS_R_System.SMSUniqueIdentifier,
SMS_R_System.ResourceDomainORWorkgroup,
SMS_R_System.Client from SMS_R_System where obsolete = 1

*****************************************

Read more →

SCCM Report all Advertisements by date

This report will show all advertisement created filtered by date:

****************************************

Declare     @AdvName Varchar(256)

Set   @AdvName = '%'

Select      adv.AdvertisementName

,    adv.AdvertisementID

,    s.Host

,    LastAcceptanceMessageIDName

,    LastAcceptanceStateName

,    LastAcceptanceStatusTime

,    LastStatusMessageIDName

,    LastStateName

,    LastStatusTime

,    LastExecutionResult

From  (

      Select      AdvertisementName

      ,    AdvertisementID

      From  dbo.v_AdvertisementInfo

      Where AdvertisementName Like @AdvName

      ) As  adv

Join  (

      Select      AdvertisementID

      ,    ResourceID

      ,    LastAcceptanceMessageIDName

      ,    LastAcceptanceStateName

      ,    LastAcceptanceStatusTime

      ,    LastStatusMessageIDName

      ,    LastStateName

      ,    LastStatusTime

      ,    LastExecutionResult

      From  dbo.v_ClientAdvertisementStatus

      Where LastStatusTime >= DateAdd(d,-3,GetDate())

      ) As  cas

On    adv.AdvertisementID = cas.AdvertisementID

Join  (

      Select      ResourceID

      ,    Netbios_Name0 As Host

      From  dbo.v_R_System

      Where Client0 = 1

      And  Active0 = 1

      And  Obsolete0 = 0    

      ) As  s

On    cas.ResourceID = s.ResourceID

Order By

      AdvertisementName

,    Host

*********************************************

Read more →

All Servers SCCM report

This report will show you a list of all servers detected in SCCM with server hostname, OS and more features:

********************************************

SELECT distinct
CS.name0 as 'Server Name',
OS.Caption0 as 'OS',
CU.Manufacturer0 as 'Manufacturer',
CU.Model0 as 'Model',
RAM.TotalPhysicalMemory0/1024 as [RAM (MB)],
processor.Name0 as 'Processor',
BIOS.ReleaseDate0 as 'BIOS Manufacture Date',
OS.InstallDate0 as 'OS Install Date'

from
v_R_System CS
FULL join v_GS_PC_BIOS BIOS on BIOS.ResourceID = CS.ResourceID
FULL join v_GS_OPERATING_SYSTEM OS on OS.ResourceID = CS.ResourceID
FULL join V_GS_X86_PC_MEMORY RAM on RAM.ResourceID = CS.ResourceID
FULL JOIN v_GS_PROCESSOR Processor on Processor.ResourceID=CS.ResourceID
FULL join v_GS_SYSTEM_ENCLOSURE SE on SE.ResourceID = CS.ResourceID
FULL join v_GS_COMPUTER_SYSTEM CU on CU.ResourceID = CS.ResourceID

WHERE CS.Operating_System_Name_and0 LIKE '%nt%server%' and CS.Client0 = 1

group by
CS.Name0,
OS.Caption0,
CU.Manufacturer0,
CU.Model0,
RAM.TotalPhysicalMemory0,
BIOS.ReleaseDate0,
OS.InstallDate0,
Processor.Name0,
BIOS.ReleaseDate0
Order by CS.Name0

************************************************

Read more →

Adobe Products SCCM Report

Report that shows you all Adobe products filtred by machine name and AD site:

Read more →

Vbs script to ping from .txt machine list txt

This is a script that will ping all the machines you put in the "Machinelist.txt" and report to an Excel file the result:

'===================================================
'
' NAME: Ping Script
'
' AUTHOR:   
' DATE  : 09/07/2013
'
' COMMENT: create file MachineList.Txt on C:\
' 
'
'exemplary damages arising out of or in any way relating to the use of this script, 
'including without limitation damages for loss of goodwill, work stoppage, 
'lost profits, loss of data, and computer failure or malfunction. 
'You bear the entire risk as to the quality and performance of this script.
'
'=================================================

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Add
intRow = 2

objExcel.Cells(1, 1).Value = "Server Name"
objExcel.Cells(1, 2).Value = "IP Address"

Set Fso = CreateObject("Scripting.FileSystemObject")
Set InputFile = fso.OpenTextFile("MachineList.Txt")

Do While Not (InputFile.atEndOfStream)
HostName = InputFile.ReadLine

Set WshShell = WScript.CreateObject("WScript.Shell")
Ping = WshShell.Run("ping -n 1 " & HostName, 0, True)

objExcel.Cells(intRow, 1).Value = HostName

Select Case Ping
Case 0 objExcel.Cells(intRow, 2).Value = "On Line"
Case 1 objExcel.Cells(intRow, 2).Value = "Off Line"
End Select

intRow = intRow + 1
Loop

objExcel.Range("A1:B1").Select
objExcel.Selection.Interior.ColorIndex = 19
objExcel.Selection.Font.ColorIndex = 11
objExcel.Selection.Font.Bold = True

objExcel.Cells.EntireColumn.AutoFit 

Read more →

Active directory extraction vbs script

This Script will allow you to extract from AD some useful data list (machine name, last login,) and export automatically into a .csv file. Just make sure you have enough rights to run it:



'=============================================
'
' NAME: 
'
' AUTHOR:  
' DATE  : 11/01/2012
'
' COMMENT: 
'Exemplary damages arising out of or in any way relating to the use of this script, 
'including without limitation damages for loss of goodwill, work stoppage, 
'lost profits, loss of data, and computer failure or malfunction. 
'You bear the entire risk as to the quality and performance of this script.
'
'
'============================================

Set objFSO = CreateObject("Scripting.FileSystemObject")

Dim HostName, DisName, Domain 
Dim Ruta(51) 

    'Sheets("AD_Computers").Select

Set adoCommand = CreateObject("ADODB.Command")
Set ADOConnection = CreateObject("ADODB.Connection")
ADOConnection.Provider = "ADsDSOObject"
ADOConnection.Open "Active Directory Provider"
adoCommand.ActiveConnection = ADOConnection

' Search entire Active Directory domain.
Set objRootDSE = GetObject("LDAP://RootDSE")
strDNSDomain = objRootDSE.Get("defaultNamingContext")
strBase = "<LDAP://" & strDNSDomain & ">"

' Filter on computer objects.
strFilter = "(&(objectCategory=computer))"

' Comma delimited list of attribute values to retrieve.
strAttributes = "name,distinguishedName,lastLogonTimestamp,operatingSystem"

' Construct the LDAP syntax query.
strQuery = strBase & ";" & strFilter & ";" & strAttributes & ";subtree"
adoCommand.CommandText = strQuery
adoCommand.Properties("Page Size") = 100
adoCommand.Properties("Timeout") = 30
adoCommand.Properties("Cache Results") = False

' Run the query.
Set adoRecordset = adoCommand.Execute
Domain = "@Domain Name"

    Set FICHERO = objFSO.opentextfile("AD_Extract.csv", 2, True)
     FICHERO.WRITELINE "Machine Name;lastLogonTimeStamp;Operating System;System OU Name"
    FICHERO.Close

' Enumerate the resulting recordset.

Do Until adoRecordset.EOF
i = i + 1

    On Error Resume Next
    HostName = adoRecordset.Fields("name").Value
    DisName = adoRecordset.Fields("distinguishedName").Value
OS = adoRecordset.Fields("operatingSystem").Value

   Set objDate = adoRecordset.Fields("lastLogonTimeStamp").Value
    
    If (Err.Number <> 0) Then
        On Error GoTo 0
        dtmDate = #1/1/1601#
    Else
        On Error GoTo 0
        lngHigh = objDate.HighPart
        lngLow = objDate.LowPart
        If (lngLow < 0) Then
            lngHigh = lngHigh + 1
        End If
        If (lngHigh = 0) And (lngLow = 0) Then
            dtmDate = #1/1/1601#
        Else
            dtmDate = #1/1/1601# + (((lngHigh * (2 ^ 32)) + lngLow) / 600000000 - lngBias) / 1440
        End If
    End If

    ' Display values for the user.
    If (dtmDate = #1/1/1601#) Then
        Tiempo = "Never"
    Else
        Tiempo = dtmDate
Tiempo = left(Tiempo,InStr(1,Tiempo, " ") -1)
Tiempo = day (Tiempo) & "/" & month (Tiempo)  & "/" & year (Tiempo)
    End If

    distinguishedName = DisName
     

AD = Split(DisName, ",")
    
    i2 = 50
    For Each Item In AD
      If Left(Item, 3) = "OU=" Then
       Ruta(i2) = Replace(Item, "OU=", "/")
       i2 = i2 - 1
      End If
      
    Next
    
    DisName = ""
    For i3 = 1 To 50
    If Ruta(i3) <> "" Then DisName = DisName + Ruta(i3)
    
    Next

    
    If DisName = "" Then DisName = "/COMPUTERS"
    

    Set FICHERO = objFSO.opentextfile("AD_COMPUTERS.csv", 8, True)
     on error resume next
     FICHERO.WRITELINE ucase(HostName) & ";" & Tiempo & ";" & OS & ";" & ucase(Domain & DisName)
     on error goto 0
    FICHERO.Close
    


    'Range("A" & i).Select
   ' Range("A" & i).Value = HostName
   ' Range("B" & i).Value = Domain & DisName
   ' Range("C" & i).Value = distinguishedName

'If i = 5760 Then
'MsgBox "va"
'End If
    'Move to the next record in the recordset.
     adoRecordset.MoveNext
     Erase Ruta

Loop
' Clean up.
adoRecordset.Close
ADOConnection.Close

Set adoRecordset = Nothing
Set objRootDSE = Nothing
Set ADOConnection = Nothing
Set adoCommand = Nothing

msgbox "END"

Read more →

SCCM clean cache vbs script

The following script will clean all machine sccm cache, Watch OUT: It works only on machines with SCCM 2007 client.

'$~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'
'
' NAME: Clean Cache
'
' AUTHOR:
' DATE  : 01/03/2013
'
' COMMENT:
'
'Exemplary damages arising out of or in any way relating to the use of this script,
'including without limitation damages for loss of goodwill, work stoppage,
'lost profits, loss of data, and computer failure or malfunction.
'You bear the entire risk as to the quality and performance of this script.
'$~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



Dim objFSO
Dim objFolder
Dim objSubFolder
Dim winsh
Dim winenv

'deletes folders with a date modified of 7 day or older
Const intDaysOld = 7
set winsh = CreateObject("WScript.Shell")
set winenv = winsh.Environment("Process")
windir = winenv("WINDIR")
Set objFSO = CreateObject("Scripting.FileSystemObject")
'looks for \system32\ccm\cache for 32bit
if objFSO.FolderExists (windir & "\system32\ccm\cache") Then
Set objFolder = objFSO.GetFolder(windir & "\system32\ccm\cache")
For Each objSubFolder In objFolder.SubFolders
                If objSubFolder.DateLastModified < DateValue(Now() - intDaysOld) Then
           objSubFolder.Delete True
    End If
Next
  Wscript.quit
End if
'looks for \sysWOW64\ccm\cache for 64bit
if objFSO.FolderExists (windir & "\sysWOW64\ccm\cache") Then
Set objFolder = objFSO.GetFolder(windir & "\sysWOW64\ccm\cache")
For Each objSubFolder In objFolder.SubFolders
                If objSubFolder.DateLastModified < DateValue(Now() - intDaysOld) Then
           objSubFolder.Delete True
    End If
Next
End if

Read more →

Last Boot up time report

This Report will show the exact machine last boot up time, HW scan, last heartbeat:

Read more →

Machines are not reporting SCCM from 30 days

This is a report that check the status of machines and detect if they're not contacting wit SCCM :

Read more →

All current Deployment Dashboard

This Dashboard will show all active deployments on your SMS  - SCCM with all percentage of each one:

Read more →

SMS - SCCM Advertisement Dashboard report

Some customers could ask for a live dashboard to follow a specific deployment. This one will help.

It's a detailed dashboard pointing to the deployment advertisement:

*****************************************************************************
declare @Total int
declare @Accepted int
declare @AdvName VARCHAR(100)
set @AdvName = 'type here your advertisement name'

SELECT @Total=count(*), @Accepted=sum(case LastState when 0 then 0 else 1 end)
FROM v_ClientAdvertisementStatus INNER JOIN
v_Advertisement ON v_ClientAdvertisementStatus.AdvertisementID = v_Advertisement.AdvertisementID
WHERE (v_Advertisement.AdvertisementName LIKE @AdvName)

SELECT LastAcceptanceStateName as 'Status', count(*) as 'Number of Resources', 
      ROUND(100.0*count(*)/@Total,1) as 'Percent of Resources',
ProgramName, AdvertisementName, v_ClientAdvertisementStatus.AdvertisementID
FROM v_ClientAdvertisementStatus INNER JOIN
v_Advertisement ON v_ClientAdvertisementStatus.AdvertisementID = v_Advertisement.AdvertisementID
WHERE (v_Advertisement.AdvertisementName LIKE @AdvName)
group by LastAcceptanceStateName, ProgramName, AdvertisementName, v_ClientAdvertisementStatus.AdvertisementID

SELECT LastStateName as 'Status of Targeted Resources', count(*) as 'Number of Resources', 
       ROUND(100.0*count(*)/@Accepted,1) as 'Percent of Resources',
ProgramName, AdvertisementName, v_ClientAdvertisementStatus.AdvertisementID
FROM v_ClientAdvertisementStatus INNER JOIN
v_Advertisement ON v_ClientAdvertisementStatus.AdvertisementID = v_Advertisement.AdvertisementID
WHERE (v_Advertisement.AdvertisementName LIKE @AdvName)
group by LastStateName, ProgramName, AdvertisementName, v_ClientAdvertisementStatus.AdvertisementID


SELECT a.Netbios_name0 as 'Host Name', a.Resource_Domain_OR_Workgr0,
site.sms_installed_sites0 as 'Sitecode',
a.Client0,
a.Obsolete0,
adv.AdvertisementName,
adv.AdvertisementID,
pkg.Name AS 'Package Name',
adv.ProgramName,
advstate.LastAcceptanceStatusTime,
advstate.LastAcceptanceStateName,
advstate.LastAcceptanceMessageIDname,
advstate.LastStatusmessageIDName,
advstate.LaststateName,
advstate.LastExecutionResult,
advstate.LastStatusTime, 
advstate.LastExecutionContext
FROM v_Advertisement adv
INNER JOIN v_Package pkg ON adv.PackageID = pkg.PackageID
INNER JOIN v_ClientAdvertisementStatus advstate on adv.AdvertisementID=advstate.AdvertisementID
INNER JOIN V_R_SYSTEM a ON a.Resourceid=advstate.resourceid
INNER JOIN v_GS_WORKSTATION_STATUS HW ON a.resourceid=hw.resourceid
INNER JOIN v_GS_LastSoftwareScan sw ON a.resourceid=sw.resourceid
LEFT OUTER JOIN v_RA_System_SMSInstalledSites site ON a.resourceid=site.resourceid
WHERE ADV.AdvertisementName like @AdvName

order by advstate.LaststateName

*****************************************************************************

the result will be like this:

Read more →