I need to create a sql query to find out bios details of a single machine, help me as i do not have any sql experience.
Question
i came across a script online now i want to know if i edit the machine name with the one i have will give me result of that particular machine or will it bring result of machine in my environment
Select
comp.Name0 as [Machine Name],
comp.Manufacturer0 as [Manufacturer],
comp.Model0 as [Model],
bios.SerialNumber0 as [Serial Number],
bios.SMBIOSBIOSVersion0 as [BIOS Version],
comp.UserName0 as [User Name],
ops.Caption0 as [Operating System],
ops.CSDVersion0 as [Service Pack],
ops.Version0 as [OS Version],
ops.InstallDate0 as [OS Installed Date]
from
v_GS_COMPUTER_SYSTEM comp join
v_GS_OPERATING_SYSTEM ops on ops.ResourceID=comp.ResourceID join
v_GS_PC_BIOS bios on bios.ResourceID=comp.ResourceID
Answers ( 7 )
What I can suggest is , simple query to pull for One machine BIOS details from SQL is
select BS.BIOSVersion0,TS.Name0,BS.BuildNumber0,GS.Model0
from v_GS_PC_BIOS BS
join v_R_System TS
on TS.ResourceID=BS.ResourceID
join v_GS_COMPUTER_SYSTEM GS
on TS.ResourceID=GS.ResourceID
where TS.Name0=’@machinename’
What I can suggest is , simple query to pull for One machine BIOS details from SQL is
select BS.BIOSVersion0,TS.Name0,BS.BuildNumber0,GS.Model0
from v_GS_PC_BIOS BS
join v_R_System TS
on TS.ResourceID=BS.ResourceID
join v_GS_COMPUTER_SYSTEM GS
on TS.ResourceID=GS.ResourceID
where TS.Name0=’@machinename’
I have one SQL query handy which mostly covers all information You looking for ……That Query is based on ” Collection ID “. Simply change collection ID with your required machines to that collection.
If you want to get single info machine , put that single machine into collection and provide collection ID in place of ” SMS00001″ ( Which is default ALL SYSTEMS collection ID )
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Declare @CollectionID as Varchar(8)
Set @CollectionID = ‘SMS00001’ — specify scope collection ID
SELECT distinct SYS.Netbios_Name0, [IPAddress0] as ‘IP’,[IPSubnet0] as ‘Mask’,[DefaultIPGateway0] as ‘Gateway’,
MAX(IPSub.IP_Subnets0) as ‘Subnet’,SYS.AD_Site_Name0 as ‘ADSiteName’,
[MACAddress0] as ‘MAC’,OPSYS.Caption0 as OS, OPSYS.CSDVersion0 as ServicePack,
fcm.SiteCode, MEM.TotalPhysicalMemory0, CSYS.Manufacturer0, CSYS.Model0, BIOS.SerialNumber0, OPSYS.InstallDate0,
MAX(OU.System_OU_Name0) as OrganizationUnit, SYS.Operating_System_Name_and0 , OPSYS.LastBootUpTime0,[DHCPEnabled0]as ‘DHCPEnabled’, [DHCPServer0] as ‘DHCPServer’ FROM v_R_System SYS
INNER JOIN v_FullCollectionMembership fcm on SYS.ResourceID=fcm.ResourceID
INNER JOIN v_RA_System_SystemOUName OU on SYS.ResourceID=OU.ResourceID
LEFT JOIN v_GS_X86_PC_MEMORY MEM on SYS.ResourceID = MEM.ResourceID
LEFT JOIN v_GS_COMPUTER_SYSTEM CSYS on SYS.ResourceID = CSYS.ResourceID
LEFT JOIN v_GS_OPERATING_SYSTEM OPSYS on SYS.ResourceID=OPSYS.ResourceID
LEFT JOIN v_GS_PC_BIOS BIOS on SYS.ResourceID = BIOS.ResourceID
LEFT JOIN v_RA_System_IPSubnets IPSub on SYS.ResourceID = IPSub.ResourceID
LEFT JOIN v_GS_NETWORK_ADAPTER_CONFIGUR IP on SYS.ResourceID = IP.ResourceID
WHERE IP.DefaultIPGateway0 IS NOT NULL
and fcm. CollectionID = @CollectionID
Group by SYS.Netbios_Name0, fcm.SiteCode,OPSYS.Caption0,OPSYS.CSDVersion0,
SYS.Operating_System_Name_and0,MEM.TotalPhysicalMemory0, CSYS.Manufacturer0, CSYS.Model0, BIOS.SerialNumber0,
OPSYS.InstallDate0, OPSYS.LastBootUpTime0,IP.MACAddress0,IP.IPAddress0, IP.IPSubnet0,
Sys.AD_Site_Name0,IP.DefaultIPGateway0,IP.DHCPEnabled0,IP.DHCPServer0
For single machine Use ” Resource Explorer” From sccm console.
thank you for the suggestion naresh, let’s say the client is not reporting to the console and we are trying to find the info from sql side.
what corrections would be the right one to the script above?
This is going to dump data for all the machines in your environment.
right, can you please point out what changes is to be done to this script so that that it brings back the info for that one machine and what field corrections has to be made to specify the machine name!