(imported topic written by SystemAdmin)
Hi,
I would like to get a list of Installed Applications for all machines in BigFix, I can do this via Web Reports, but when I ry export, it does not export to CSV properly, when I try via Excel add-in, I get an XML error, so I would like to do this via SQL query, does anyone know the query I would need to execute?
Thanks
Anthony
(imported comment written by MattBoyd)
I don’t have a SQL query for you, but what version of Web Reports are you using? There was a bug in the SOAP API prior to version 8.1.551.0 that would cause XML parsing errors if results contained special characters.
(imported comment written by SystemAdmin)
Thanks boyd, our upgrade is planned for July time period, in the mean time . . . anyone? Please?
(imported comment written by wnolan91)
If you have access to the BES Inventory and License SITE, it has an analyses that contains this RP:
Installed Applications - Windows
(if it = “” then nothing else it) of unique values of ((if (exists value"DisplayName" of it AND exists value “DisplayName” of it as string) then (value “DisplayName” of it as string) else ("")) & (if (exists value “DisplayVersion” of it AND exists value “DisplayVersion” of it as string) then (" | " & value “DisplayVersion” of it as string) else (""))) of keys whose (exists value “UninstallString” of it AND ((not exists value “SystemComponent” of it) OR (exists value “SystemComponent” of it AND name of it starts with “{” AND name of it contains “}” AND (exists match (regex “(9|A|B|C)(0|1)1(2|4)\d\d\d\d-(0011|0012|0013|0014|0017|002E|002F|0030|0031|0033|0035|003A|003B|0044|0051|0052|0053|0057|00BA |00CA|10D7|110D)-(0000|0409)-(0|1)000-(0|1)000000FF1CE$”) of preceding text of first “}” of following text of first “{” of name of it))) AND (if (exists value “DisplayName” of it AND exists value “DisplayName” of it as string) then ((it does not contain “Hotfix” AND it does not contain “Security Update for” AND it does not contain “Update for” AND it does not contain “Security Update for Windows” AND it does not contain “Update for Windows” AND it does not contain “Security Update for Microsoft” AND (length of it > 0) AND (number of substrings " " of it < length of it)) of (value “DisplayName” of it as string)) else true)) of keys “HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall” of (if x64 of operating system then (x32 registry; x64 registry) else registry)
A: 2007 Microsoft Office Suite Service Pack 2 (SP2)
A: Adobe AIR | 2.5.1.17730
A: Apple Application Support | 1.3.2
A: Apple Software Update | 2.1.1.116
A: AutoIt v3.3.6.1
A: BES Session Relevance Tester 8.0.627 | 8.0.627
A: BigFix Enterprise Console | 7.2.5.22
A: BigFix Excel Connector 3.1 | 3.1.0
A: DB BitLocker Management Tool | 7.0.700.0
A: DB CitrixICAClient | 1.0.1
A: DB Managed Client 7 | 7.0.950.0
A: DB7Devaccess | 1.0.0
A: Double-Take Enterprise Install Console | 1.4.11
A: Google Toolbar for Internet Explorer | 7.0.1710.2246
A: Java™ 6 Update 18 (64-bit) - DB086245 | 6.0.180
A: Java™ 6 Update 18 32bit - DB086242 | 6.0.180
A: Juniper Installer Service | 2.1.2.5973
A: Juniper Networks Network Connect 6.5.0 | 6.5.0.15255
A: Juniper Networks Setup Client Activex Control | 2.1.1.1
A: Lenovo Auto Scroll Utility | 1.00
A: Lenovo System Interface Driver | 1.05
A: LiveUpdate 3.3 (Symantec Corporation) | 3.3.0.92
A: MSXML 4.0 SP2 (KB954430) | 4.20.9870.0
A: Microsoft Application Virtualization Desktop Client | 4.6.0.10181
A: Microsoft Office Communicator 2007 | 2.0.6362.137
A: Microsoft Office InfoPath MUI (English) 2007 | 12.0.6425.1000
A: Microsoft Office Language Pack 2007 Service Pack 2 (SP2)
A: Microsoft Office Professional Plus 2007 | 12.0.6425.1000
A: Microsoft Office Visio 2007 Service Pack 2 (SP2)
A: Microsoft Office Visio Professional 2007 | 12.0.6425.1000
A: Microsoft Save as PDF or XPS Add-in for 2007 Microsoft Office programs | 12.0.4518.1014
A: Microsoft Silverlight | 3.0.50106.0
A: Microsoft Visual C++ 2005 Redistributable (x64) | 8.0.56336
A: Microsoft Visual C++ 2005 Redistributable | 8.0.56336
A: Microsoft Visual C++ 2005 Redistributable | 8.0.59193
A: Microsoft Visual C++ 2008 Redistributable - x86 9.0.30729.17 | 9.0.30729
A: Microsoft Visual C++ 2008 Redistributable - x86 9.0.30729.4148 | 9.0.30729.4148
A: Microsoft Visual C++ 2010 x86 Redistributable - 10.0.30319 | 10.0.30319
A: Notepad++ | 5.7
A: On Screen Display | 6.30.00
A: PDF-XChange Pro 4.0 | 4.176.46.0
A: PdaNet for Android 2.45
A: QuickTime | 7.68.75.0
A: Symantec Endpoint Protection | 11.0.5002.333
A: Symantec Enterprise Vault Outlook Add-In English - DB086411 | 8.0.18375
A: SysTracer v2.2
A: ThinkPad FullScreen Magnifier | 2.24
A: ThinkPad Modem Adapter | 7.80.5.0
A: ThinkPad Power Management Driver | 1.60.0.4
A: ThinkPad UltraNav Driver | 15.3.6.0
A: Tivoli Endpoint Manager Excel Connector 3.3 | 3.3.0
A: WMI Tools | 1.50.1131.0001
A: WebEx
A: WebSlingPlayer ActiveX | 1.4.0.111
A: WinPcap 4.1.2 | 4.1.0.2001
A: Windows Resource Kit Tools - SubInAcl.exe | 5.2.3790.1164
A: dbSupportPlus Center | 7.1.500.0
T: 71.587 ms
I: plural string with multiplicity
(imported comment written by wnolan91)
I’m sorry I misunderstood your question at first…
NOTE: BigFix does not support SQL Queries against there database, and highly recommends using SOAP. That being said…
Here is a good start… but you will need to breakup the last field based on ‘|’
SELECT ch1.value, lqr1.ResultsText FROM LONGQUESTIONRESULTS as lqr1, bes_column_headings as ch1
WHERE lqr1.AnalysisID = ‘34’ and lqr1.SiteID = ‘3093’ and lqr1.PropertyID = ‘1’ AND ch1.Name = ‘Computer Name’ and lqr1.ComputerID = ch1.ComputerID
(imported comment written by SystemAdmin)
Hi, thanks for your reply, but that returns zero rows!
Surely someone must know how to do this, or even via SOAP, but I believe the SOAP interface has a problem and wouldn’t return the results.
(imported comment written by russnuck91)
I am running into a similar issue. There are two tables you need to query against.
QUESTIONRESULTS
LONGQUESTIONRESULTS
If the “ResultsText” for whatever analysis property is smaller than the column size (7936), it will be in QUESTIONRESULTS. If the “ResultsText” for that analysis property goes beyond that limit, it will be in LONGQUESTIONRESULTS.
You can try to query both tables with the same computer ID to verify.
select *
from dbo.LONGQUESTIONRESULTS INNER JOIN
dbo.computers on dbo.computers.computerid = dbo.longquestionresults.computerid
where dbo.longquestionresults.analysisid = ‘34’ AND dbo.longquestionresults.siteid = ‘3093’ AND dbo.longquestionresults.propertyid = ‘1’ and dbo.computers.isdeleted = ‘0’ and dbo.computers.computerid = ‘5452’
select *
from dbo.QUESTIONRESULTS INNER JOIN
dbo.computers on dbo.computers.computerid = dbo.questionresults.computerid
where dbo.questionresults.analysisid = ‘34’ AND dbo.questionresults.siteid = ‘3093’ AND dbo.questionresults.propertyid = ‘1’ and dbo.computers.isdeleted = ‘0’ and dbo.computers.computerid = '5452 ’
In your case one should return a row and the other should not.
The problem I am running into is, there are entries in both tables! So I need to know which one is accurate (I have sql scripts to return and parse the data), but I do not know which table takes precedence.
(imported comment written by Lee Wei)
QuestionResults has a column ResultsText that holds a maximum of 8000 characters. This is the SQL Server limit for varchar fields.
LongQuestionResults use “Text” datatype, which maxes out into the 2GB range.
So anything below 8000 will get stored in QuestionResults, and when over 8000 will be found in LongQuestionResults.
The data in LongQuestionResults take precedence.
CREATE TABLE QUESTIONRESULTS
(
SiteID INTEGER NOT NULL,
ID INTEGER NOT NULL,
ComputerID INTEGER NOT NULL,
Version INTEGER NOT NULL,
IsFailure TINYINT NOT NULL,
IsPlural TINYINT NOT NULL,
ResultsCount INTEGER NOT NULL,
ResultsText VARCHAR( 8000 ),
Sequence ROWVERSION NOT NULL,
CONSTRAINT pk_questionresults PRIMARY KEY NONCLUSTERED ( ComputerID, SiteID, ID )
)
CREATE TABLE LONGQUESTIONRESULTS
(
SiteID INTEGER NOT NULL,
ID INTEGER NOT NULL,
ComputerID INTEGER NOT NULL,
ResultsText TEXT NOT NULL,
CONSTRAINT pk_longquestionresults PRIMARY KEY ( ComputerID, SiteID, ID )
)
(imported comment written by russnuck91)
So if the ResultsText drops below the 8000 character limit, will the entry in LONGQUESTIONRESULTS be removed?
(imported comment written by Lee Wei)
I am wrong about that earlier. If we look at the definition of the view BES_COLUMN_HEADINGS, then we see that the logic is:
If the QuestionResults.ResultsText column is NULL, then use LongQuestionsResults, otherwise, use QuestionResults.ResultsText.
Lee Wei
SELECT Q.ComputerID, P.Name, CASE WHEN Q.ResultsText IS NULL THEN CAST(L.ResultsText AS VARCHAR(7936)) ELSE Q.ResultsText END AS Value, Q.IsFailure
FROM dbo.QUESTIONRESULTS AS Q LEFT OUTER JOIN
dbo.LONGQUESTIONRESULTS AS L ON Q.SiteID = L.SiteID AND Q.AnalysisID = L.AnalysisID AND Q.PropertyID = L.PropertyID AND
Q.ComputerID = L.ComputerID INNER JOIN
dbo.SITENAMEMAP AS M ON Q.SiteID = M.SiteID INNER JOIN
dbo.LOCAL_OBJECT_DEFS AS P ON Q.AnalysisID = P.ID INNER JOIN
dbo.VERSIONS AS V ON P.ID = V.ID AND M.Sitename = V.Sitename AND P.Version = V.LatestVersion INNER JOIN
dbo.COMPUTERS AS C ON Q.ComputerID = C.ComputerID
WHERE (M.Sitename = ‘ActionSite’) AND (Q.PropertyID = 1) AND (P.ParentID = 0) AND (P.ContentType = 5) AND (C.IsDeleted = 0) AND (M.IsDeleted = 0)