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

solved 0
santosh krishh 4 weeks 2021-06-30T05:43:45+05:30 7 Answers 47 views Beginner 0

Answers ( 7 )

    2
    2021-07-01T23:07:42+05:30

    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’

    0
    2021-07-01T23:06:59+05:30

    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’

    1
    2021-07-01T20:43:13+05:30

    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

    Best answer
    0
    2021-07-01T00:37:19+05:30

    For single machine Use ” Resource Explorer” From sccm console.

      0
      2021-07-01T04:46:40+05:30

      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?

    0
    2021-06-30T18:10:32+05:30

    This is going to dump data for all the machines in your environment.

      0
      2021-07-01T04:47:58+05:30

      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!

Leave an answer

Sorry, you do not have a permission to answer to this question .