PowerShell Commands

Check System Uptime Using PowerShell

--> (get-date) – (gcim Win32_OperatingSystem).LastBootUpTime

Read More

Command Prompt - Commands

Check System Uptime Using Command Prompt

--> The commands “net statistics workstation” (for Windows workstations) and “net stats server” (for Windows servers)

--> systeminfo | find “System Boot Time”

Read More

Read the offersum box for SVF Files- PowershellScript

 ##Read the offersum box for SVF Files

$configMgrBoxpath = "E:\Temp\offersum Files TO scan"

$count = O

Get-ChildItem -Path $configMgrBoxpath -Include *.SVF -Recurse -Force -ErrorAction

Silentlycontinue -outBuffer 1000 | ForEach-Object {

$File = $_.FullName;

$count = $count + 1

If (($count % 1000) -eg 0) {

   write-host $count "files scanned"

try {

     ( ##Search for the non-existing deployment id passed in below select string within the SVF File

       Get-content _ReadCount 1 _TotalCount 6 -Path $_.FullName _ErrorAction Stop |

       select-String _Pattern ('2147024894') -ErrorAction stop

      ).Matches.Result($File)

} catch {

       ##write-warning -Message "Given deployment id not found in SFi1e"

}

     ##Write the non-existing deployment SVF file location on a text file

} | move-iten -Force -destination "E:\Temp|Offersum Files Irrelvent"

write-host "Script Finished !!!"

Read More

MECM Daily usage SQL Queries

Client:

List Of Devices in perticular Colelction
  
  select sys.Name0 as 'Host Name',
fcm.CollectionID as 'Collection ID',
vc.Name as 'Collection Name',
sys.Operating_System_Name_and0 as 'O.S. Name',
sys.Build01 as 'Build Version'
from v_R_System sys
inner join v_FullCollectionMembership fcm on fcm.ResourceID=sys.ResourceID
inner join v_Collection vc on vc.CollectionID = fcm.CollectionID
where fcm.CollectionID = 'SMS00001'

Query Results:

Check the machine part of which the collections
  
  select sys.Name0 as 'Host Name',
fcm.CollectionID as 'Collection ID',
vc.Name as 'Collection Name',
sys.Operating_System_Name_and0 as 'O.S. Name',
sys.Build01 as 'Build Version'
from v_R_System sys
inner join v_FullCollectionMembership fcm on fcm.ResourceID=sys.ResourceID
inner join v_Collection vc on vc.CollectionID = fcm.CollectionID
where 
sys.Name0 = 'WIN10'
--sys.Name0 in ('WIN10','SCCMPRI')

  
  Query Results:

  List the client version count and list
  
  select sys.Client_Version0 as 'Client Version',
count(sys.Client_Version0) as 'Client Count'
from v_R_System sys
group by sys.Client_Version0
order by sys.Client_Version0 desc

Query Results:

List of Operating System Count and list
  
  select sys.Operating_System_Name_and0 as 'O.S. Name',
Count(sys.Operating_System_Name_and0) as 'Count'
from v_R_System sys
group by sys.Operating_System_Name_and0

Query Results:

Package Details, Distrubution Status and Deployment Query:

Package Deployment Status with Deployment ID
  
select 
sys.Name0 as 'Host Name',
vc.Name as 'CollectionName',
pkg.Name as 'Pacakge Name',
sys.User_Name0 as 'User Name',
adv.AdvertisementID as 'Deplyment ID',
ads.LastAcceptanceStateName as 'Package Acceptance Status',
ads.LastAcceptanceStatusTime as 'Last Stuats',
ads.LastStateName as 'Package Status',
ads.LastStatusMessageIDName as 'Last Message ID',
ads.LastExecutionResult as 'Execution Result'
from v_Advertisement as adv
inner join v_Package pkg on pkg.PackageID = adv.PackageID
inner join v_ClientAdvertisementStatus as ads on adv.AdvertisementID=ads.AdvertisementID
inner join v_R_System as sys on ads.ResourceID=sys.ResourceID
inner join v_Collection as VC on adv.CollectionID=VC.CollectionID
Where adv.AdvertisementID = 'PS120001'
order by ads.LastStateName

Query Results:

Application Details, Status and Deployment Status Query:

Devices base Application Deployment Status with Deplyment ID 
  
SELECT distinct
vrs.Name0 [Computer Name], vgos.Caption0 [OS],vrs.User_Name0 [User Name],
v_CIAssignment.Assignment_UniqueID ,lac.DisplayName AS 'ApplicationName',CollectionName,
IIf([EnforcementState]=1001,'Installation Success',
IIf([EnforcementState]>=1000 And [EnforcementState]<2000 And [EnforcementState]<>1001,'Installation Success',
IIf([EnforcementState]>=2000 And [EnforcementState]<3000,'In Progress', IIf([EnforcementState]>=3000 And [EnforcementState]<4000,
'Requirements Not Met ', 
IIf([EnforcementState]>=4000 And [EnforcementState]<5000,'Unknown', IIf([EnforcementState]>=5000 
And [EnforcementState]<6000,'Error','Unknown')))))) AS Status
FROM dbo.v_R_System AS vrs
INNER JOIN (dbo.vAppDeploymentResultsPerClient
INNER JOIN v_CIAssignment
ON dbo.vAppDeploymentResultsPerClient.AssignmentID = v_CIAssignment.AssignmentID)
ON vrs.ResourceID = dbo.vAppDeploymentResultsPerClient.ResourceID
INNER JOIN dbo.fn_ListApplicationCIs(1033) lac
ON lac.ci_id=dbo.vAppDeploymentResultsPerClient.CI_ID
INNER JOIN dbo.v_GS_WORKSTATION_STATUS AS vgws
ON vgws.ResourceID=vrs.resourceid
INNER JOIN v_FullCollectionMembership coll
ON coll.ResourceID = vrs.ResourceID
INNER JOIN dbo.v_GS_OPERATING_SYSTEM AS vgos
ON vgos.ResourceID = vrs.ResourceID
WHERE v_CIAssignment.Assignment_UniqueID like '{C6073DF3-DBFD-4F62-BC47-5FE360A03347}'
Query Results:

User & Device based Application Deployment Status with Deplyment ID 
  
select a.UserName,a.MachineName,a.CollectionID,a.CollectionName,lac.DisplayName as'Application Nmae',a.Assignment_UniqueID as 'Deployment ID',a.Technology,
IIf([EnforcementState]=1001,'Installation Success',
IIf([EnforcementState]>=1000 And [EnforcementState]<2000 And [EnforcementState]<>1001,'Installation Success',
IIf([EnforcementState]>=2000 And [EnforcementState]<3000,'In Progress', IIf([EnforcementState]>=3000 And [EnforcementState]<4000,
'Requirements Not Met ', 
IIf([EnforcementState]>=4000 And [EnforcementState]<5000,'Unknown', IIf([EnforcementState]>=5000 
And [EnforcementState]<6000,'Error','Unknown')))))) AS Status
from vAppDeploymentAssetData a
INNER JOIN dbo.fn_ListApplicationCIs(1033) lac ON lac.ci_id=a.AppCI

where a.Assignment_UniqueID like '%{83B0F9D5-3436-49DB-8025-0E6775FF41B0}%'

order by a.UserName



Query Results:

Software Update deployment Status and patch scan staus (Updates Required,Not required,Installed & Unknown):

Sofware Update Deployment Status with Deplyment ID  
select vrs.Name0,vrs.Active0,
--vrs.AD_Site_Name0,vrs.User_Name0,vrs.Operating_System_Name_and0,

a.Assignment_UniqueID as DeploymentID,C.CollectionID,c.Name 'Collection Name',
a.AssignmentName as DeploymentName,a.StartTime as Available,a.EnforcementDeadline as Deadline,sn.StateName as LastEnforcementState,
wsus.LastErrorCode as 'LasErrorCode',wsus.LastScanTime as 'LastWSUSScan',DateDiff(D, wsus.LastScanTime, GetDate()) as 'LastWSUSScan Age',
wks.LastHWScan,
DateDiff(D, wks.LastHwScan, GetDate()) as 'LastHWScan Age'
from v_CIAssignment a
join v_AssignmentState_Combined assc on a.AssignmentID=assc.AssignmentID
join v_StateNames sn on assc.StateType = sn.TopicType and sn.StateID=isnull(assc.StateID,0)
join v_R_System vrs on vrs.ResourceID = assc.ResourceID
join v_GS_WORKSTATION_STATUS wks on wks.ResourceID = assc.ResourceID
join v_UpdateScanStatus wsus on wsus.ResourceID = assc.ResourceID
LEFT JOIN v_Collection C ON C.CollectionID = a.CollectionID
where a.Assignment_UniqueID like '{9846A1A8-3289-447C-9CCF-EA41C48EB916}'
and assc.StateType in (300,301)
order by 11 desc
Query Results:

Enforcement states for a software updates deployment
select
 vrs.name0 as ComputerName,
 a.AssignmentName as DeploymentName,
 assc.StateTime ,
 a.CollectionName, sn.StateName as LastEnforcementState
 from v_CIAssignment a
 join v_AssignmentState_Combined assc on a.AssignmentID=assc.AssignmentID
 join v_StateNames sn on assc.StateType = sn.TopicType and sn.StateID=isnull(assc.StateID,0)
 join v_R_System vrs on vrs.ResourceID=assc.ResourceID
 where 
--a.AssignmentID='xxxxxxxx' or 
a.Assignment_UniqueID like '{9846A1A8-3289-447C-9CCF-EA41C48EB916}'
 order by LastEnforcementState
 
 Query Results:
 
 

 
 

Software updates Instllation Status

select rsys.name0 AS 'Computername',
os.Caption0 AS 'Operating System',
--os.BuildNumber0 AS 'Build Number',
ui.ArticleID AS 'Update ID',
ui.Title,
--ui.Description,
--Set Install Status
Case
when ucs.Status='0' then 'Detection state unknown'
When ucs.Status='1' then 'Update is not required'
when ucs.Status='2' then 'Update is required'
When ucs.Status='3' then 'Update is installed'
End As 'Install Status',
--Set client Activity
Case
When ch.ClientActiveStatus='1' then 'Active'
When ch.ClientActiveStatus='0' then 'Inactive'
End As 'Client Active Status',
ui.InfoURL,
ui.DatePosted
from v_updateinfo ui
join v_Update_ComplianceStatusAll ucs on ucs.ci_id=ui.ci_id
join v_CICategories_All catall on catall.CI_ID=UCS.CI_ID
join v_CategoryInfo catinfo on catall.CategoryInstance_UniqueID = catinfo.CategoryInstance_UniqueID and catinfo.CategoryTypeName='UpdateClassification'
join v_R_System rsys on rsys.resourceid=ucs.resourceid
join v_GS_OPERATING_SYSTEM os on os.ResourceID = ucs.ResourceID
join v_CH_ClientSummary ch on ch.resourceID = ucs.ResourceID


where rsys.name0 in ('ORG5405DBA9F104')  
 --and ucs.Status in ('0','1','2','3')
order by ui.Title

 Query Results:
 

 
 
 

Read More

Package Deployment Status with Deployment ID

Note:- Replace stars (***) with Deployment ID(Example:- PS120001)

select
vc.Name as 'CollectionName',
pkg.Name as 'Pacakge Name',
sys.Name0 as 'Host Name',
sys.User_Name0 as 'User Name',
ads.LastAcceptanceStateName as 'Package Acceptance Status',
ads.LastAcceptanceStatusTime as 'Last Stuats',
ads.LastStateName as 'Package Status',
ads.LastStatusMessageIDName as 'Last Message ID',
ads.LastExecutionResult as 'Execution Result'
from v_Advertisement as adv
inner join v_Package pkg on pkg.PackageID = adv.PackageID
inner join v_ClientAdvertisementStatus as ads on adv.AdvertisementID=ads.AdvertisementID
inner join v_R_System as sys on ads.ResourceID=sys.ResourceID
inner join v_Collection as VC on adv.CollectionID=VC.CollectionID
Where adv.AdvertisementID = '******'
order by ads.LastStateName

Query Result:-

Read More

Deployment Detailed status for specific software Update deployment

Note:- Replace stars (***) with Deployment Unique ID(Example:- {9846A1A8-3289-447C-9CCF-EA41C48EB916})

select vrs.Name0,vrs.Active0,
--vrs.AD_Site_Name0,vrs.User_Name0,vrs.Operating_System_Name_and0,

a.Assignment_UniqueID as DeploymentID,C.CollectionID,c.Name 'Collection Name',
a.AssignmentName as DeploymentName,a.StartTime as Available,a.EnforcementDeadline as Deadline,sn.StateName as LastEnforcementState,
wsus.LastErrorCode as 'LasErrorCode',wsus.LastScanTime as 'LastWSUSScan',DateDiff(D, wsus.LastScanTime, GetDate()) as 'LastWSUSScan Age',
wks.LastHWScan,
DateDiff(D, wks.LastHwScan, GetDate()) as 'LastHWScan Age'
from v_CIAssignment a
join v_AssignmentState_Combined assc on a.AssignmentID=assc.AssignmentID
join v_StateNames sn on assc.StateType = sn.TopicType and sn.StateID=isnull(assc.StateID,0)
join v_R_System vrs on vrs.ResourceID = assc.ResourceID
join v_GS_WORKSTATION_STATUS wks on wks.ResourceID = assc.ResourceID
join v_UpdateScanStatus wsus on wsus.ResourceID = assc.ResourceID
LEFT JOIN v_Collection C ON C.CollectionID = a.CollectionID
where a.Assignment_UniqueID like '************'
and assc.StateType in (300,301)
order by 11 desc

Read More

SCCM Application Deployment Status using SQL Query Custom Report

SCCM Application Deployment Status using SQL Query Custom Report

Note:- Replace stars (***) with Deployment Unique ID(Example:- {C6073DF3-DBFD-4F62-BC47-5FE360A03347})

SELECT distinct
vrs.Name0 [Computer Name], vgos.Caption0 [OS],vrs.User_Name0 [User Name],
v_CIAssignment.Assignment_UniqueID ,lac.DisplayName AS 'ApplicationName',CollectionName,
IIf([EnforcementState]=1001,'Installation Success',
IIf([EnforcementState]>=1000 And [EnforcementState]<2000 And [EnforcementState]<>1001,'Installation Success',
IIf([EnforcementState]>=2000 And [EnforcementState]<3000,'In Progress', IIf([EnforcementState]>=3000 And [EnforcementState]<4000,
'Requirements Not Met ', 
IIf([EnforcementState]>=4000 And [EnforcementState]<5000,'Unknown', IIf([EnforcementState]>=5000 And [EnforcementState]<6000,'Error','Unknown')))))) AS Status
FROM dbo.v_R_System AS vrs
INNER JOIN (dbo.vAppDeploymentResultsPerClient
INNER JOIN v_CIAssignment
ON dbo.vAppDeploymentResultsPerClient.AssignmentID = v_CIAssignment.AssignmentID)
ON vrs.ResourceID = dbo.vAppDeploymentResultsPerClient.ResourceID
INNER JOIN dbo.fn_ListApplicationCIs(1033) lac
ON lac.ci_id=dbo.vAppDeploymentResultsPerClient.CI_ID
INNER JOIN dbo.v_GS_WORKSTATION_STATUS AS vgws
ON vgws.ResourceID=vrs.resourceid
INNER JOIN v_FullCollectionMembership coll
ON coll.ResourceID = vrs.ResourceID
INNER JOIN dbo.v_GS_OPERATING_SYSTEM AS vgos
ON vgos.ResourceID = vrs.ResourceID
WHERE v_CIAssignment.Assignment_UniqueID like '********'

Query Result

Read More