HW inventory by times

 select sys.name0 AS 'Device name',

vgs.ResourceID,

sys.User_Name0,

Case when CS.ClientActiveStatus='1' then 'Active' When CS.ClientActiveStatus='0' then 'Inactive' end as 'Client Active Status',

os.Caption0 AS  'Operating System',

os.Version0,

os.CSDVersion0,

img.core_image_version0 AS 'image version ',

sln.SerialNumber0 AS 'Serial Number',

max(vgs.LastLogon0) As Lastlogon,

max(hb.AgentTime) AS 'HeartBeat time',

hw.LastHWScan,

sw.LastScanDate,

cs.LastPolicyRequest,

cs.LastOnline,

ip.IPAddress0,

sys.Full_Domain_Name0,

cs.LastMPServerName,

max(ou.System_OU_Name0) AS OU

from  v_GS_NETWORK_LOGIN_PROFILE as vgs

left join v_R_System sys on vgs.ResourceID=sys.ResourceID

inner join v_FullCollectionMembership col on sys.resourceid=col.resourceid

left Join v_CH_ClientSummary CS on sys.ResourceId=CS.ResourceID

left join v_AgentDiscoveries hb on hb.ResourceId=sys.ResourceID

left join v_GS_WORKSTATION_STATUS hw on hw.ResourceID=sys.ResourceID

left join v_GS_OPERATING_SYSTEM OS on sys.ResourceID=os.ResourceID

left join v_GS_IP_Address_Primary IP on sys.ResourceID=ip.ResourceID

left join v_GS_LastSoftwareScan sw on sys.ResourceID=sw.ResourceID

left join v_RA_System_SystemOUName OU on sys.ResourceID=ou.ResourceID

left join v_GS_PC_BIOS sln on sln.ResourceID=sys.ResourceID

where hb.AgentName like 'Heartbeat Discovery' and col.CollectionID='collectionID'

group by vgs.ResourceID,sys.Name0,hb.AgentName, hw.LastHWScan,os.Caption0,sw.LastScanDate,ip.IPAddress0,cs.LastPolicyRequest,sln.SerialNumber0,cs.LastOnline,img.core_image_version0,sys.Full_Domain_Name0,sys.User_Name0,CS.ClientActiveStatus,os.Version0,

os.CSDVersion0,cs.LastMPServerName



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

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,    Res...
Read more