TASK sequence duration report

 SELECT 

 sys.Netbios_Name0 AS [Machine Name], 

 sys.User_Name0, 

 os.Caption0[Operating System],

 os.InstallDate0 [OS installed date],

 os.BuildNumber0[OS build number],

 tsstart.Time as [TS StartTime], 

 tslast.Time as [TS EndTime], 

 CASE  

  WHEN DATEDIFF(dd, tsstart.Time, tslast.Time) > 0 THEN  

  CAST(DATEDIFF(dd, tsstart.Time, tslast.Time) AS VARCHAR(2)) + 'd ' 

  ELSE '' 

 END + 

  

convert(char(8),DATEDIFF(MINUTE, tsstart.Time , tslast.Time)) AS [Duration in Minutes]

FROM 

( 

 SELECT 

  MAX(ExecutionTime) AS Time, 

  AdvertisementID, 

  ResourceID 

 FROM v_TaskExecutionStatus

 WHERE LastStatusMessageID = 11140 AND Step = 0 

 GROUP BY AdvertisementID,ResourceID 

) tsstart 

 

INNER JOIN 

( 

 SELECT 

  AdvertisementID, 

  ResourceID, 

  LastStatusTime as Time 

 FROM v_ClientAdvertisementStatus  WHERE LastState = 13 

) tslast 

ON 

 tsstart.AdvertisementID = tslast.AdvertisementID AND 

 tsstart.ResourceID = tslast.ResourceID AND 

 tslast.Time > tsstart.Time 

 

INNER JOIN v_R_System  sys ON tsstart.ResourceID = sys.ResourceID 

left join v_GS_OPERATING_SYSTEM os ON os.ResourceID=sys.ResourceID

WHERE tsstart.AdvertisementID in ('BEL2194A','BEL21948','BEL21949','BEL21947','BEL21946','BEL21945')

and tsstart.Time >=DATEADD(day,-7,getdate())

Comments

Post a Comment

Popular posts from this blog

powershell script to export applications and its requirement in weird way

 $SiteCode = 'DEV' $sccmserver = 'covid-100' $Applications = Get-WmiObject -Namespace "root\SMS\Site_$SiteCode" -Class SMS_Application -ComputerName $SCCMServer foreach ($apps in $Applications){ $DTS = Get-CMDeploymentType -ApplicationName $apps.LocalizedDisplayName foreach ($DT in $DTS){ $req = Get-CMDeploymentTypeRequirement -InputObject $DT "$($apps.LocalizedDisplayName) ; $($req.name)" | Out-File -FilePath "C:\Users\username\Desktop\dtsssss.csv" -Append } }
Read more

get-allrecent udpated logs

 # winrm service should be running on the machine where script is running for ciminstance query else change it to get-wmiobject # Get-Allrecentlogs -computername 'localhost' -startdateandtime '01/01/2005 00:00' -enddateandtime '07/07/2020 00:00' -logfilextension 'flv' # Get-Allrecentlogs -computername 'localhost' -startdateandtime '01/01/2005 00:00' -enddateandtime '07/07/2020 00:00' -logfilextension 'mp4' Function Get-Allrecentlogs{ param([string]$computername = 'localhost', [datetime]$startdateandtime, [datetime]$enddateandtime, [string]$logfilextension = 'log' ) if ($computername -eq 'localhost')     {     $location = (Get-CimInstance -ClassName Win32_logicaldisk -Filter "DriveTYpe = '3'").DeviceId     } else { $shares = Get-CimInstance -ComputerName $computername -ClassName Win32_share | Where-Object {$_.Path -match '^\w{1}:\\$'} [System.Collections.ArrayList]$location ...
Read more