Skip results in relevance

(imported topic written by jnmoore91)

Hey All,

I’m wondering if there is anyway to skip a number of results of a collection in relevance? For example, in order to achieve paged results, if a user wants to look at page 2, relevance needs to be able to skip the first 100 results (0-100), pull the next 100 results (101-200), and then ignore the rest of the results (201-+to infinity and beyond…+). This is absolutely necessary if you have tens of thousands of machines.

In BES Web Reports, I see paged results everywhere, so there must be a way to do it.

Thanks,

–Jerroyd

(imported comment written by Lee Wei)

We don’t have such a construct in the relevance language.

Any pagination you see is done via programming.

SQL Server has “select top xx from column from table”

Oracle has “where rownum <= xx”

I think this is a useful enhancement.

Lee Wei

(imported comment written by NoahSalzman)

However, there are lots of ways to “drill down” into a set of data… but, yes, you can’t think of it in terms of “order this set by this column and then limit the results”… you have to be able to select the subsets by their properties.

JNMoore, can you be more specific regarding your use case, is their a specific set of properties (of the thousands of machines) you are looking for?

(imported comment written by jnmoore91)

noah

JNMoore, can you be more specific regarding your use case, is their a specific set of properties (of the thousands of machines) you are looking for?

For my web report, it’s quite simple. The user selects one or more fixlets they wish to view the progress on, and the press submit. In the next page, it shows the fixlet progress, and the click on one to view affected / remediated computers (sound familiar?). For computers still affected by the fixlet, my customers want to be able to choose any number of computer properties (but usually no more than a dozen), and view the computers and their properties in a table. This satisfies my customers.

Now, because there could be 100s or 1000s of affected computers, I need to page the results, so users get a quick response with my report, and they can page or export (I got export to work) the results if they wish. With my testing, querying in relevance (via JS EvaluateRelevance SOAP call), the query takes ~10 seconds (and this is with < 60). If I had 600 computers, it’d take a minute and a half! If I had 6,000 computers it’d take 15 minutes!

Perhaps my relevance to create the table needs optimizing, but paged results are essential.

–Jerroyd

(imported comment written by BenKus)

Hey Jerroyd,

You are correct that you have to do pagination yourself in the custom report… but I wouldn’t expect that the report length would scale linearly with the number of computers. For the Web Reports, there is latency and overhead to each EvaluateRelevance call and generally adds 1 sec for each call… If you can cut back the number of Evaluate Relevance calls, you likely will scale much better.

Ben

(imported comment written by jnmoore91)

Hi Ben,

Thanks for the tip about latency with EvaluateRelevance. In one report, I’ve combined 3 or 4 EvaluateRelevance into one, and the performance boost was 53%!

But in the report regarding displaying a table of computers and their properties, I’m only using 1 EvaluateRelevance for each table already. I’ve taken the same query, and I encapsulated the <?relevance ?> tags around it in a different report to see if there was a performance boost. There wasn’t. Maybe you could take a look at my relevance and tell me if there are any optimizations you see?

<table> <?relevance thead of tr of ( td of (html tag(
"b", 
"Computer Name") ) & ( td of ( html tag (
"b", name of it) ) of bes property whose ( id of it as string is 
"2147486277, 6784, 1" ) ) & ( td of ( html tag (
"b", name of it) ) of bes property whose ( id of it as string is 
"2147486277, 6781, 1" ) ) ?><?relevance tbody of concatenation of trs of ( td of item 0 of it & td of item 1 of it & td of item 2 of it ) of ( html tag (
"b", link of it), ( 

if( exists value of it ) then value of it as html 

else html tag (
"b", 
"<undefined>") ) of property results whose ( id of property of it as string is 
"2147486277, 6784, 1" ) of it, ( 

if( exists value of it ) then value of it as html 

else html tag (
"b", 
"<undefined>") ) of property results whose ( id of property of it as string is 
"2147486277, 6781, 1" ) of it ) of computers of results whose ( relevant flag of it ) of bes fixlet whose ( name of site of it is 
"Security Prod" AND id of it is 97347203 ) ?></table>

This was the only way I found that I could make sure the value of the bes property would fall under the name of the bes property column.

This relevance was generated in JS, and saved as a new custom report. I thought it would be more efficient to search based on IDs instead of names (also don’t have to worry about two properties sharing names).

I also do the same query for remediated computers, except instead of the

relevant flag of it

, I use

not relevant flag of it AND exists last became relevant of it

. I’ve contemplated merging the two relevance queries together, but the syntax for it sounds like a nightmare!

Any thoughts?

–Jerroyd

(imported comment written by jnmoore91)

Lee Wei

We don’t have such a construct in the relevance language.
Any pagination you see is done via programming.

SQL Server has "select top xx from column from table"
Oracle has “where rownum <= xx”

I think this is a useful enhancement.

Lee Wei

Any chance of porting this functionality? If you guys are able to do it in your default reports, shouldn’t we be able to do it in our custom reports?

(imported comment written by BenKus)

Hey jnmoore,

Yes… I will talk to the developers and see what we can do in a future version… Note that the reason this is different is because our custom reports evolved significantly after the original set of reports and they use a different engine to generate the reports.

Ben

(imported comment written by jnmoore91)

jnmoore

Hi Ben,

Thanks for the tip about latency with EvaluateRelevance. In one report, I’ve combined 3 or 4 EvaluateRelevance into one, and the performance boost was 53%!

But in the report regarding displaying a table of computers and their properties, I’m only using 1 EvaluateRelevance for each table already. I’ve taken the same query, and I encapsulated the <?relevance ?> tags around it in a different report to see if there was a performance boost. There wasn’t. Maybe you could take a look at my relevance and tell me if there are any optimizations you see?

<?relevance thead of tr of ( td of (html tag( "b", "Computer Name") ) & ( td of ( html tag ( "b", name of it) ) of bes property whose ( id of it as string is "2147486277, 6784, 1" ) ) & ( td of ( html tag ( "b", name of it) ) of bes property whose ( id of it as string is "2147486277, 6781, 1" ) ) ?><?relevance tbody of concatenation of trs of ( td of item 0 of it & td of item 1 of it & td of item 2 of it ) of ( html tag ( "b", link of it), (

if( exists value of it ) then value of it as html

else html tag (
“b”,
"") ) of property results whose ( id of property of it as string is
"2147486277, 6784, 1" ) of it, (

if( exists value of it ) then value of it as html

else html tag (
“b”,
"") ) of property results whose ( id of property of it as string is
"2147486277, 6781, 1" ) of it ) of computers of results whose ( relevant flag of it ) of bes fixlet whose ( name of site of it is
"Security Prod" AND id of it is 97347203 ) ?>

This was the only way I found that I could make sure the value of the bes property would fall under the name of the bes property column.
This relevance was generated in JS, and saved as a new custom report. I thought it would be more efficient to search based on IDs instead of names (also don’t have to worry about two properties sharing names).

I also do the same query for remediated computers, except instead of the relevant flag of it, I use not relevant flag of it AND exists last became relevant of it. I’ve contemplated merging the two relevance queries together, but the syntax for it sounds like a nightmare!

Any thoughts?

–Jerroyd

Does anyone know if this is the correct way to build a table of computer properties?

(imported comment written by BenKus)

Hey Jerroyd,

Sorry I missed your earlier post… Session relevance optimization is currently somewhat of an art, but I have some suggestions for you that should help quite a lot:

  • Rather than “… of computers of results whose (relevant flag of it ) of bes fixlet whose …” use instead “… applicable computers of bes fixlet whose …”. This helps a lot because the “applicable computers” is a fast look up compared to having to iterate through all computers.

  • Rather than “… property results whose (id of property of it as string is “2147486277, 6784, 1”) of it…” use instead “… results (it, bes property whose (id of it as string is “2147486277, 6784, 1”) ) …”. This helps a lot because in your way you had to look at all the property results for each iteration through all computers.

I think this will dramatically make your report faster (my very basic test was a 3x time improvement, but you might get more…

There are more perf enhancements, but they get complicated and I think they have declining returns…

Good job on building this report, btw…

Ben

(imported comment written by BenKus)

Also… does this different approach using the Excel Connector work for you?

http://support.bigfix.com/labs/excelconnect.html

Ben

(imported comment written by jnmoore91)

Hi Ben,

Thanks for the suggestions, Any relevance optimizations are greatly appreciated!

Few Questions…

  1. Thanks for the tip to use

applicable computers

instead of

computers of results whose (relevant flag of it )

. Is there an equivalent for

computers of results whose (not relevant flag of it AND exists last became relevant of it)

, aka +remediated computers+? (probably not, but I figured I’d ask in case there was!)

  1. For this phrase:

results (it, bes property whose (id of it as string is “2147486277, 6784, 1”) ) …

, in the documentation, I’ve never seen the syntax for this, so I’m a little confused what this is doing. What does the first

it

refer to?

edit: nevermind, I figured it out

In regards to the Excel Connector, I haven’t had a chance to use it since we’re still trying to upgrade our environment to 7.1. Additionally, we fear the tool may be too technical for upper management / nontechnical people, so we’ll probably be using both that tool and my my web report.

edit: we just upgraded, and I’ve had a chance to play with the excel plug in. While the session relevance editor allows me the flexibility, the query wizard does not. Since we’d offer this tool to our customers to use, they’d have to learn relevance language in order to accomplish their goals. The Query Wizard isn’t malleable enough to be useful to me.

edit

I just noticed that if a property of a computer has not been reported, then that computer does not show up on my table (Is this new to 7.2?). As a result, I’ve had to modify my query (also added a check for plural value) from

( name of it, ( 

if(exists values of it) then ( 

if(plural flag of it) then (concatenation 
";" of values of it) 

else value of it ) 

else 
"<error>" ) of results ( bes property whose (id of it as string is 
"2147486277, 7536, 1"), it ) ) of applicable computers of bes fixlet whose (name of site of it is 
"Non Security Production" AND id of it is 336)

to

( name of it, ( 

if( exists results ( bes property whose (id of it as string is 
"2147486277, 7536, 1"), it ) ) then ( ( 

if(exists values of it) then ( 

if(plural flag of it) then (concatenation 
";" of values of it) 

else value of it ) 

else 
"<error>" ) of results ( bes property whose (id of it as string is 
"2147486277, 7536, 1"), it ) ) 

else 
"<not reported>" ) ) of applicable computers of bes fixlet whose (name of site of it is 
"Non Security Production" AND id of it is 336)

Notice the 3 layers of if-then checks for existing inspectors: check to see if results exist, check to see if values of results exist, check to see if plural flag of results exist :stuck_out_tongue:

There has to be a better way, right?

–Jerroyd