SQL Analyses Properties

steini44 · October 23, 2015, 8:48am

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?

Second: I can retrieve the XML-file for an analysis by the following code:

select p.id, p.name, convert( xml, convert( varbinary(max), p.fields ) ) as "XML-file" 
from local_object_defs p join versions v on v.id=p.id and v.sitename=
'ActionSite' and p.version=v.latestversion where NOT p.parentid=1 AND p.isfixlet=1 AND Name = 'Data Pulling Devices' order by name

This will get the following part XML-file (didn’t copy the whole + i left my relevance queries from IBM out of it:

So, my question… How do I retrieve those values with SQL or how can I adjust the XML file that it’s a better XML-file?

If i’m missing anything, let me know!

As always, thank you for your help!

Greetings
Steini44

jgstew · October 23, 2015, 3:39pm

What are you trying to achieve?
Why do you want to do it with SQL instead of with Session Relevance?

There are more potential negative performance consequences of reading the database directly instead of using the REST API as far as the root server is concerned.

steve · October 23, 2015, 8:33pm

Are you trying to get the results that the endpoints report for each property or the relevance for each property just split up into separate elements?

steini44 · October 26, 2015, 6:52am

Hi jgstew, thanks for you answer.

I want it with SQL so we can take a snapshot every week, month,… It’s not recommended, i know… Is this easily done with Session Relevance too?

steini44 · October 26, 2015, 6:55am

Hi Steve

Actually, i want both if its possible. But my main goal is to have each property from a custom analysis for a specific device. Is this possible?
But I think to achieve the first, you need to have the second no? If the XML is split up in different elements, I can easily retrieve it with SQL, no?

Thanks for you reply!

jgstew · October 26, 2015, 3:13pm

If you want to grab all property results, you can do this with session relevance.

I would be surprised if you couldn’t do whatever you are trying to do with session relevance.

steini44 · October 27, 2015, 6:35am

Yes, i know i can do that with session relevance. But my question is, can it also be done with SQL on the DB, even though i know it’s not recommended.

Where can i find the property results in the DB view? That is my question.

jgstew · October 27, 2015, 5:52pm

It is definitely possible with the DB and it should be in one of the views, but I’m not certain how since I haven’t really done it before.

steve · October 27, 2015, 10:23pm

The relevance for each property is in the output that you included above, and can be extracted more simply from the BES_OBJECT_DEFS view (no join with the VERSIONS table), though the XML format will be the same.

The property result data is not available from the views. To clarify the _PROPERTIES views have the metadata associated with fixlets, analyses, and baselines like Category, Severity, Source, etc; they do not have anything to do with property data from endpoints. It is probably easier to get both of these things from session relevance, but if you want to use SQL, I would suggest using the PropertyIDMapper utility from here: https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/Tivoli%20Endpoint%20Manager/page/Utilities

When you run that utility, you’ll get a new PROPERTYIDMAP table that contains the name of all the properties and the 3 ids you need to query for the results: SiteID, AnalysisID, and PropertyID. You use those id triples to query for the results of specific properties from the QUESTIONRESULTS and LONGQUESTIONRESULTS tables.

steini44 · October 28, 2015, 7:31am

Hi Steve

Thank you for your clear answer! That’s what i needed, so big thanks!

I will definitely look into the tool and try to do it, but first I need to talk about it with my colleagues

Thanks for your help again, i will let you know if we will try this and what the outcome is.

steini44 · October 29, 2015, 10:58am

Hi @steve, I tried the utility and it works. Now it’s just a matter of having the right SQL query and I’ll be fine. Because the problem I have now, is that my properties have multiple values in it, so it’s just a long String. Any idea how to adjust my SQL or isn’t this the right forum for it? I guess not, but i can give it a try

 SELECT ResultsText as Hostname
  FROM [BFEnterprise].[dbo].[LONGQUESTIONRESULTS]
  Where ([AnalysisID] = 119  and [PropertyID] = 1) 
        
SELECT ResultsText as Date
  FROM [BFEnterprise].[dbo].[LONGQUESTIONRESULTS]
  Where ([AnalysisID] = 119  and [PropertyID] = 2) 
 
 SELECT ResultsText as InstanceName
  FROM [BFEnterprise].[dbo].[LONGQUESTIONRESULTS]
  Where ([AnalysisID] = 119  and [PropertyID] = 3)

thanks for you help anyway!

steve · November 4, 2015, 8:55am

You’re queries are valid, though you should also include SiteID to be sure your always matching the intended property. Multi-string results are actually separated by a line feed character (%0a). I can’t recall the best way to separate these within SQL, but you should be able to do a find/replace or split of the results keying off this character.

steini44 · November 4, 2015, 9:07am

Hi Steve

Thanks for your answer. I’m still looking at it to bind everything. I’m getting there, I just need to play with a couple more joins etc I’ll let you know when it’s done!

steini44 · November 4, 2015, 11:07am

Hi Guys

For everybody who’s interested in the query to get ALL the properties from ALL the analysis (included custom analysis):

Use BFEnterprise
Go
---------------------------------------------------------------------
SELECT CH.Value,
       Q.ComputerID,
       A.Sitename,
       PM.SiteID,
       A.Name as 'Analysis Name',
       PM.AnalysisID,
       PM.PropertyID,
       Q.ResultsText

FROM dbo.PROPERTYIDMAP PM    
    
join (
    SELECT      
        ComputerID,
        SiteID, 
        AnalysisID, 
        PropertyID,
        ResultsText 
    FROM QUESTIONRESULTS
    WHERE ResultsText IS NOT NULL
    UNION ALL
    SELECT      
        ComputerID, 
        SiteID,
        AnalysisID, 
        PropertyID,
        ResultsText
    FROM LONGQUESTIONRESULTS
) Q
    
on Q.SiteID = PM.SiteID
    and Q.AnalysisID = PM.AnalysisID
    and Q.PropertyID = PM.PropertyID

join BES_ANALYSES A ON A.ID = Q.AnalysisID
join SITENAMEMAP SM ON SM.UndecoratedSitename = A.Sitename
join BES_COLUMN_HEADINGS CH ON CH.ComputerID = Q.ComputerID    AND CH.Name = 'Computer Name' 

order by Sitename, SiteID, AnalysisID, ComputerID, PropertyID asc

If you have any questions, let me know. Thanks for all the help @steve!

Greetings

steini44 · November 6, 2015, 7:44am

Update:

We changed the code a bit, because there was a wrong join on it. The reason is that in the View BES_ANALYSES the ID not unique is. Example: a existing analysis in BigFix can have 140, a custom one will have 00-140 in BigFix. In de DB, it has both ID 140… Which sucks. So you need to make a unique Value (SiteID, AnalysisID and PropertyID). Another problem is the custom sites: so you need to join on SiteID AND UndecoratedSiteName too (of SiteNameMap). Because in de DB, all the custom sites have an ID “-19…”. So that’s why you need to join on UndecoratedSiteName (with the SiteName from BES_Analyses) too.

SELECT 
    SUBSTRING(CH.Value, 1, LEN(CH.Value) - 1) as HostName,
    R.ComputerID,
    SM.UndecoratedSitename As SiteName,
    R.SiteID,
    AN.Name As AnalysisName,
    R.AnalysisID,
    PM.PropertyName,
    R.PropertyID,
    R.ResultsText
 FROM (
    SELECT      
        ComputerID,
        SiteID,
        AnalysisID, 
        PropertyID,
        '(' + CONVERT(VARCHAR, SiteID) + ',' + CONVERT(VARCHAR, AnalysisID) + ',' + CONVERT(VARCHAR, PropertyID) + ')' As UniqueID,
        ResultsText 
    FROM QUESTIONRESULTS    
    WHERE ResultsText IS NOT NULL
    UNION ALL
    SELECT      
        ComputerID, 
        SiteID,
        AnalysisID, 
        PropertyID,
        '(' + CONVERT(VARCHAR, SiteID) + ',' + CONVERT(VARCHAR, AnalysisID) + ',' + CONVERT(VARCHAR, PropertyID) + ')' As UniqueID,
        ResultsText
    FROM LONGQUESTIONRESULTS
) R
JOIN (
    SELECT 
        SiteID,
        AnalysisID,
        PropertyID,
        PropertyName,
        '(' + CONVERT(VARCHAR, SiteID) + ',' + CONVERT(VARCHAR, AnalysisID) + ',' + CONVERT(VARCHAR, PropertyID) + ')' As UniqueID
    FROM PROPERTYIDMAP
) PM
ON PM.UniqueID = R.UniqueID
join BES_COLUMN_HEADINGS CH on CH.ComputerID = R.ComputerID and CH.Name = 'Computer Name'
join SITENAMEMAP SM on SM.SiteID = R.SiteID
JOIN (
    SELECT 
        SM.SiteID,
        A.ID As AnalysisID,
        A.Name
    FROM BES_ANALYSES A
    JOIN SITENAMEMAP SM ON SM.UndecoratedSitename = A.Sitename
) AN
ON (AN.SiteID = R.SiteID AND AN.AnalysisID = R.AnalysisID)

Then you can create a view and run snapshots every day, week,…

Greetings