SQL Query for Computer Name

[system]

(imported topic written by koldenbu91)

How do I get the Computer Name while running SQL queries on the BigFix Enterprise DB. I can see the Computer ID but that is a number. I really need to have the computer name (NetBios Name for example)

[leewei]

(imported comment written by Lee Wei)

Koldenbu,

The view is called “BES_Column_Headings”.

You can try this:

select ComputerID, Value “Computer Name”

from bes_column_headings

where Name = ‘Computer Name’

order by Value

Lee Wei

[system]

(imported comment written by koldenbu91)

Thanks,

It worked great

[system]

(imported comment written by SY57_Jim_Montgomery)

If you want to dive deeper and avoid the views (if you are a really big geek) then you can join computers and questionresults on the computerid columns. QuestionResults with AnalysisID=4 (and propertyID=1) is the computer name.

Take a look at questionresults for a certain computer, and you’ll see the results for all the analysis’es that run on the box. You can then pick out the analysisID of what you want.

–Jim

[system]

(imported comment written by smill59)

Is there a sql query that will give me the computer name of only the Windows servers?

[leewei]

(imported comment written by Lee Wei)

smill59,

First we have to create a Manage Property to return the computer type for Windows computers.

I took the relevance from the Power Management site.

In my case, I name the Manage Property “Windows Computer Type”.

if (name of operating system as lowercase starts with 
"win") then ( 

if (name of operating system = 
"Win95" OR name of operating system = 
"Win98" OR name of operating system = 
"WinME") then 
"Workstation" 

else 

if (exists wmi AND exists selects 
"* from win32_Battery" of wmi) then 
"Laptop" 

else (

if (value 
"ProductType" of key 
"HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\ProductOptions" of registry = 
"WinNT") then 
"Workstation" 

else 
"Server" ) ) 

else 
"Unknown"

Then we can run the following SQL command to extract only Servers.

Note that the valid values are “Server”, “Workstation”, “Laptop” and “Unknown”.

select A.ComputerID, A.Value 
"Computer Name", B.Value 
"Computer Type" from bes_column_headings A, BES_COLUMN_HEADINGS B where A.ComputerID = B.ComputerID and A.Name = 
'Computer Name' and B.Name = 
'Windows Computer Type' and SUBSTRING(B.Value, 1, LEN(B.Value)-1) = 
'Server' order by A.Value

Lee Wei

[manojthathera]

@leewei

Koldenbu,

The view is called “BES_Column_Headings”.

You can try this:

select ComputerID, Value “Computer Name”

from bes_column_headings

where Name = ‘Computer Name’

order by Value

Hi Leewei,

This query seems very simple which helped to find the ‘Computer Name’.

I need to find the ‘Serial Number’ .

I tried below script but found nothing .

select ComputerID, Value “Serial Number”

from [BFEnterprise].[dbo].[bes_column_headings]

where Name = ‘Serial Number’

order by Value

Need urgent help.

[leewei]

This post relates to your question.

SQL Analyses Properties

Hi Guys I’m trying to find my way into the views of the DB. First of all, i know that it’s better to do it by API, but i really want to do it on DB-level. I already found the post How to get the values of property resulting from an analyses?, where i can do it by Session Relevance. So back to my topic: The first thing I notice is that there is no Analyses_Properties. You do have a Baseline_Properties etc, but why is there no Analyses_Properties? [image] Second: I can retrieve the XML-file …

How will you be using the output from the query?
It is better to use the REST API and Session Relevance.
Let us know how will the data be consumed, and what format you need.

[manojthathera]

Hi Leewei,

I want the data in below format.

I am able to retrieve the IP Address and OS from BES_COLUMN_HEADING view, but unable to get the Serial Number.

If session relevance will help us to get the better and easy results, then it would be great for us.

[swap041]

@manojthathera

nable to get t

Hey Manoj,

Computer Serial Number is not a predefined property, you need to make it a custom to look it in the DB.
Not all properties can be looked in.

How Do you know which properties are predefined?
This can be found from Manage Properties tab from Console.

But as Lee said you Can use Reporting tool to fetch the report.

[manojthathera]

@swap041

Computer Serial Number is not a predefined property, you need to make it a custom to look it in the DB.

Hi Swap,

I Agree what you said, but we have created custom reports with the name “Computer Serial Number”.
Is there any other way to fetch the serial number from that custom analysis?

I have tried a few session relevance queries using which i can fetch the serial number but not in the format that I want.

[gearoid]

@manojthathera

have tried a few session relevance queries using which i can fetch the serial number but not in the format that I want.

If you can share what you’ve tried then we may to provide better help.

[manojthathera]

Hi Gearoid,

This is the relevance which i tried.

I’m getting the serial number against the host.

But I want this to be done in SQL Server Management Studio by using Views.

[AlanM]

We’d never suggest using SQL directly to get information as the schema can change between releases and thus the query will go invalid quickly. The session relevance is the better way of doing it. The output format you can construct fairly easily with session relevance, even in Web Reports