Jump to content


  • 0
skw

Dell BIOS Version Report

Question

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

Recommended Posts

  • 0

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

  • 0

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

  • 0

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

  • 0

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

  • 0
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

Join the conversation

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

Guest
Answer this question...

×   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...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.