Dell BIOS Version Report

[skw]

Pulled the following SQL statement directly from Dell's website. It shows me a total count of machines based on the BIOS version. Trying to figure out how to create a drill-down report or create a new report to show me the actual machine names and their BIOS version. It's nice to know that I have 5 machines that are Latitude D820's with BIOS A05, but which machines have A05?

 

SELECT CompSys.Manufacturer0 as 'OEM', CompSys.model0 as 'Model',BIOS.SMBIOSBIOSVERSION0 as 'BIOSVersion', COUNT(Sys.Resourceid) AS 'TotalSystems'

FROM V_R_System as Sys

LEFT JOIN V_GS_PC_BIOS as BIOS on Sys.resourceid = BIOS.resourceid

LEFT JOIN V_GS_COMPUTER_SYSTEM as CompSys on Sys.resourceid = CompSys.resourceid

WHERE

 

CompSys.Manufacturer0 LIKE '%dell%'

AND (CompSys.Model0 LIKE '%optiplex%'

OR CompSys.Model0 LIKE '%latitude%'

OR CompSys.Model0 LIKE '%precision%')

 

GROUP BY CompSys.Manufacturer0,CompSys.model0, BIOS.SMBIOSBIOSVersion0

ORDER BY CompSys.Model0, BIOS.SMBIOSBIOSVersion0

[André Gomes]

Great Report, but how to put de machine name in this report?

[P@docIT]

SELECT CompName.Name0 as 'Computer Name', CompSys.model0 as 'Model', BIOS.SMBIOSBIOSVERSION0 as 'BIOSVersion', CompSys.Manufacturer0 as 'OEM'

FROM V_R_System as Sys

LEFT JOIN V_GS_PC_BIOS as BIOS on Sys.resourceid = BIOS.resourceid

LEFT JOIN V_GS_COMPUTER_SYSTEM as CompSys on Sys.resourceid = CompSys.resourceid

LEFT JOIN V_GS_SYSTEM as CompName on Sys.resourceid = CompName.resourceid

WHERE

 

CompSys.Manufacturer0 LIKE '%dell%'

AND (CompSys.Model0 LIKE '%optiplex%'

OR CompSys.Model0 LIKE '%latitude%'

OR CompSys.Model0 LIKE '%precision%')

 

GROUP BY CompSys.Manufacturer0,CompSys.model0, BIOS.SMBIOSBIOSVersion0, CompName.Name0

ORDER BY CompSys.Model0, BIOS.SMBIOSBIOSVersion0

This report will give the below results:

[P@docIT]

Trying to add a prompt so that you can enter a specific model and see all bios versions out in the wild just on that model, not having much luck though. So if anyone knows of a way it would be greatly appreciated.

[P@docIT]

Figured out the prompt.

 

In your report SQL Change:

CompSys.Manufacturer0 LIKE '%dell%'

AND (CompSys.Model0 LIKE '%optiplex%'

OR CompSys.Model0 LIKE '%latitude%'

OR CompSys.Model0 LIKE '%precision%')

 

to

 

CompSys.Manufacturer0 LIKE '%dell%'

AND CompSys.Model0 LIKE @variable

 

 

Then build your prompt as follows:

Name: variable

Prompt text: Can be anything you want

Check the box "Provide a SQL statement

SQL: below

begin

if (@__filterwildcard = '')

SELECT DISTINCT CompSys.Model0 from v_GS_COMPUTER_SYSTEM as CompSys Order by CompSys.Model0

else

SELECT DISTINCT CompSys.Model0 from v_GS_COMPUTER_SYSTEM as CompSys

WHERE CompSys.Model0 like @__filterwildcard

Order by CompSys.Model0

end

 

NOTE: @variable is just an example. It can be anything you want. For instance in my actual report i used @ModelNumber and called my prompt ModelNumber.

[xerxes2985]

I know this is an old post, but what does if (@__filterwildcard= ") do? and @__filterwildcard ? Am I supposed to change it to lets say (BIOS Version Number:)?
 

On 3/26/2012 at 1:28 PM, P@docIT said:

begin

if (@__filterwildcard = '')

SELECT DISTINCT CompSys.Model0 from v_GS_COMPUTER_SYSTEM as CompSys Order by CompSys.Model0

else

SELECT DISTINCT CompSys.Model0 from v_GS_COMPUTER_SYSTEM as CompSys

WHERE CompSys.Model0 like @__filterwildcard

Order by CompSys.Model0

end

 

 is what it shows up like. I've put random things and there, and it doesn't seem to do anything?

 

Thanks

[GarthMJ]

This was written for CM07. What version of CM are you using? 

 

[xerxes2985]

CM12 (SCCM 1710)

[GarthMJ]

Just to be clear there is no such version as CM12 (1710) since you called out 1710, you must have CMCB 1710. 

 

How exactly are you creating this report? What do your prompts look like?

 

[xerxes2985]

Sorry for the delay. Yes CMCB 1710 (I am not familiar enough with the naming conventions). 

I created a new report through the console and edited it in Microsoft SQL Server Report Builder. I used some of the items from the built-in reports to create my template, and pasted the query language into the query section. I created a new datasource. 

Upon original testing, the section of code created the "filterwildcard" section when running the report   and it didn't function as anything. After some further review of the SQL, I was able to come up with a working solution for the report. 

SELECT CompName.Name0 as 'Computer Name', CompSys.model0 as 'Model', BIOS.SMBIOSBIOSVERSION0 as 'BIOSVersion', CompSys.Manufacturer0 as 'OEM'

FROM V_R_System as Sys

LEFT JOIN V_GS_PC_BIOS as BIOS on Sys.resourceid = BIOS.resourceid
LEFT JOIN V_GS_COMPUTER_SYSTEM as CompSys on Sys.resourceid = CompSys.resourceid
LEFT JOIN V_GS_SYSTEM as CompName on Sys.resourceid = CompName.resourceid

WHERE

CompSys.Manufacturer0 LIKE '%dell%'
AND CompSys.Model0 LIKE @ModelNumber
AND BIOS.SMBIOSBIOSVERSION0 LIKE @BIOSVersion

GROUP BY CompSys.Manufacturer0,CompSys.model0, BIOS.SMBIOSBIOSVersion0, CompName.Name0
ORDER BY CompSys.Model0, BIOS.SMBIOSBIOSVersion0

Both "Model Number" and "BIOS Version" fields are filterable when running the report.

 

 

[xerxes2985]

On 3/26/2012 at 1:28 PM, P@docIT said:

begin

if (@__filterwildcard = '')

SELECT DISTINCT CompSys.Model0 from v_GS_COMPUTER_SYSTEM as CompSys Order by CompSys.Model0

else

SELECT DISTINCT CompSys.Model0 from v_GS_COMPUTER_SYSTEM as CompSys

WHERE CompSys.Model0 like @__filterwildcard

Order by CompSys.Model0

end

 

So it seems whatever this particular portion was intending to do, is not needed.