Jump to content


skw

Dell BIOS Version Report

Recommended Posts

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

Share this post


Link to post
Share on other sites


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:

post-10749-0-92140500-1331654572_thumb.png

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

 

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

 

Thanks

Share this post


Link to post
Share on other sites

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?

 

Share this post


Link to post
Share on other sites

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. 


image.thumb.png.e5105b8c7623c7b60bfa19bc6eb280ce.png

Upon original testing, the section of code created the "filterwildcard" section when running the report  image.png.b885eb7e5c526b5a27e8a5b939ea0a98.png 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.

 

bios_report.thumb.PNG.f5dcb2de1ddf9c03a507b8fc3dcee7a8.PNG

 

datasets.PNG

Share this post


Link to post
Share on other sites
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. 

Share this post


Link to post
Share on other sites
2 hours ago, xerxes2985 said:

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

You would use this for your prompt queries. For SSRS, you would only need the "else" query with a few quick mods.

I see from your screenshot that you have to manually type each item. personally, I would extend this to:

  • Prompt for manufacturer name
  • Prompt for model name (use cascading prompt)
  • Prompt for BIOS version (use cascading prompts)

Share this post


Link to post
Share on other sites
23 minutes ago, GarthMJ said:

You would use this for your prompt queries. For SSRS, you would only need the "else" query with a few quick mods.

I see from your screenshot that you have to manually type each item. personally, I would extend this to:

  • Prompt for manufacturer name
  • Prompt for model name (use cascading prompt)
  • Prompt for BIOS version (use cascading prompts)

GarthMJ,

Luckily (or not) we only have one manufacturer (Dell). It would be pretty cool to add two cascading prompts for the other options. This is honestly the first SQL report I've tried to build, so it is quite new to me.

But... if you would like to assist, that would be awesome. :)
 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...