A good way to pull 'Installed Applications - Windows' via T-SQL?

(imported topic written by NickFries91)

I’m pulling out “Installed Applications - Windows” along with the Computer Name and a custom global property '‘Employee Information - Global ID’.

Does this seem like the proper way to do this? (Am I missing anything? I am getting results back…)

I can use the BES_COLUMN_HEADINGS view to easily pull Global Properties but the Analysis Properties are another story which is why I’m doing this.

Also, are the results delimited by just CR or CRLF?

T-SQL:

SELECT Q.ComputerID,

(SELECT MAX(CASE WHEN SUBSTRING(Name, 1, 13) = ‘Computer Name’ THEN REPLACE(REPLACE(REPLACE(SUBSTRING(Value, 1, 20), CHAR(10), ‘’), CHAR(13), ‘’), CHAR(9), ‘’) ELSE ‘’ END)

FROM BES_COLUMN_HEADINGS WHERE BES_COLUMN_HEADINGS.ComputerID = Q.ComputerID) AS ‘Computer Name’,

(SELECT MAX(CASE WHEN SUBSTRING(Name, 1, 46) = ‘Employee Information - Global ID’ THEN SUBSTRING(Value, 1, 20) ELSE ‘’ END)

FROM BES_COLUMN_HEADINGS WHERE BES_COLUMN_HEADINGS.ComputerID = Q.ComputerID) AS ‘Employee GID’,

Q.ResultsText

FROM dbo.QUESTIONRESULTS Q

JOIN COMPUTERS C ON C.ComputerID = Q.ComputerID

WHERE AnalysisID = 34 AND PropertyID = 1

Thanks! =)

(imported comment written by BenKus)

It is possible to use the SOAP API (maybe through the Excel Connector http://support.bigfix.com/labs/excelconnect.html) to get this data? (it ends up being easier in most cases than SQL)

Ben

(imported comment written by SY57_Jim_Montgomery)

NickFries -

select top 50 retrievedProperty.name, c.computerid, CompPropertyTable.resultstext as RetrievedProperty, compnametable.resultstext as computerName, AppsList.resultstext as AppList from local_object_defs retrievedProperty join versions v on retrievedProperty.version = v.latestversion join questionresults CompPropertyTable on retrievedProperty.id=CompPropertyTable.analysisid join computers c on c.computerid=CompPropertyTable.computerid join questionresults CompNameTable on CompNameTable.computerid=c.computerid join questionresults AppsList on Appslist.computerid=c.computerid where retrievedProperty.name=
'Employee Information - Global ID' and retrievedProperty.contenttype=5 and retrievedProperty.parentid=0 and CompNameTable.analysisid=4 and AppsList.analysisid=34 and c.isdeleted=0

If you don’t mind using the tables (and risking this not working when the database changes in v8), then the above should work. In this query I join to questionresults 3 times. First for the retrieved property, next for the analysis (also stored in the same table) and then finally for the computer name. You also gotta remember to get the current version of the retrieved property (which is really in the fixlet table) and don’t show deleted computers.

–Jim

(imported comment written by NickFries91)

jimbot

NickFries -
If you don’t mind using the tables (and risking this not working when the database changes in v8), then the above should work. In this query I join to questionresults 3 times. First for the retrieved property, next for the analysis (also stored in the same table) and then finally for the computer name. You also gotta remember to get the current version of the retrieved property (which is really in the fixlet table) and don’t show deleted computers.

–Jim

Hi Jim, I forgot to thank you for replying to this. Your suggestions were very helpful, thanks! =)