(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)
(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)
(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
(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
(imported comment written by smill59)
Is there a sql query that will give me the computer name of only the Windows servers?
(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
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.
This post relates to your question.
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.
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.
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.
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.
If you can share what youāve tried then we may to provide better help.
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.
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