How do I remove duplicate rows from query results?

[plane]

Greetings,

This is my first post here. Please be gentle. I have the following statement:

(ids of it, ip addresses of applicable computers of it as string, operating systems of applicable computers of it, source severities of it) of bes fixlets whose (applicable computer count of it > 0 and source severity of it != “” and source severity of it != “Unspecified” and source severity of it != “N/A” and source severity of it != “”)

Here’s a partial list of results:

134, 192.168.1.2, Win2008R2 6.1.7601, Important
134, 192.168.1.2, Win2008R2 6.1.7601, Important
134, 192.168.1.2, Win2008R2 6.1.7601, Important
134, 192.168.1.2, Win2008R2 6.1.7601, Important
134, 192.168.1.2, Win2008R2 6.1.7601, Important
134, 192.168.1.2, Win2008R2 6.1.7601, Important
134, 192.168.25.12, Win7 6.1.7601, Important
134, 192.168.25.12, Win2008R2 6.1.7601, Important
134, 192.168.25.12, Win2008R2 6.1.7601, Important

There are a couple of issues here: First, lots of duplicate rows. Second, it reports 2 different O/S values for IP 192.168.25.12.

I’ve tried removing the duplicates by placing the unique values of clause in various spots in the query. This either results in an error or has no effect at all.

I have no idea how to deal with it reporting 2 different operating systems.

Any help much appreciated.

Regards,
Pete L.

[steve]

It depends what results you’re trying to get to. Right now your query is matching all fixlets that are applicable to at least one computer and have a source severity. For each of those fixlets, you are then asking it to give you information about each applicable computer. If you were only asking for the OS and had 10 computers relevant for one of the fixlets, you’ll get 10 results for that fixlet representing the different OSes for each of the 10 computers. When you add in the IP address result also, then you get 100 results (10 results for the different OSes for each of the 10 different IP addresses, assuming only 1 IP address for each computer). This is because your query is looking at the applicable computers separately for IP address and OS, for each fixlet.

If you want to just get the list of computers that are relevant for one or more fixlets that have a severity, you need to reference the applicable computers with ‘it’ instead of bes fixlets. e.g.

(name of it, ip addresses of it, operating system of it) of unique values of applicable computers of bes fixlets whose (applicable computer count of it > 0 and source severity of it != "" and source severity of it != "Unspecified" and source severity of it != "N/A" and source severity of it != "" and display name of site of it equals "Patches for Windows (English)")

or even better to address duplicates from multiple IP address systems:

(name of it, concatenation "|" of (ip addresses of it as string), operating system of it) of unique values of applicable computers of bes fixlets whose (applicable computer count of it > 0 and source severity of it != "" and source severity of it != "Unspecified" and source severity of it != "N/A" and source severity of it != "" and display name of site of it equals "Patches for Windows (English)")

If you want to get a list of applicable computers per patch, it’s a bit more complicated because you need to use two references in a tuple (bes fixlets AND applicable computers of bes fixlets). But you should be able to get the equivalent report by using Web Reports natively, e.g.

This is a standard Computers report adding the Relevant Fixlets column and filtering on the fixlets of interest.

[plane]

Steve,
Thanks for the timely response. I’m really new to relevance language and still trying to come up to speed on the concepts and grammar. I’m going to take some time to digest and experiment with the solutions you provided. I notice your usage of the concatenation key word. Where is this documented? I’ve looked in the Relevance Language Reference document which defines the ampersand as the concatenation operator. After nearly a week of playing around with this, I get the feeling I’m still not looking in the right places:^)

Thanks again.

[steve]

The most common way to find available properties/keywords is to use the introspection capabilities of relevance. They talk about it a bit in the official docs here:

http://www-01.ibm.com/support/knowledgecenter/SS63NW_9.2.0/com.ibm.tivoli.tem.doc_9.2/Platform/Relevance/c_determining_object_properties.html
ftp://public.dhe.ibm.com/software/tivoli/IEM/9.2/Platform/Relevance_Guide.pdf
https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/Tivoli%20Endpoint%20Manager/page/Fixlet%20Authoring (in the Core Inspectors Guide)

Here are the most common usages of introspection:

properties whose (it as string contains "bes fixlet")

or

properties of type "string"

[JasonWalker]

@steve

(name of it, concatenation “|” of (ip addresses of it as string), operating system of it) of unique values of applicable computers of bes fixlets whose (applicable computer count of it > 0 and source severity of it != “” and source severity of it != “Unspecified” and source severity of it != “N/A” and source severity of it != “” and display name of site of it equals “Patches for Windows (English)”)

If you want to get a list of applicable computers per patch, it’s a bit more complicated because you need to use two references in a tuple (bes fixlets AND applicable computers of bes fixlets). But you should be able to get the equivalent report by using Web Reports natively, e.g.

I know I’m going to kick myself tomorrow for posting while not in front of a console, but it appears to me that relevance should return details for all applicable computers of any of the filtered fixlets, without actually showing which fixlet was relevant to each computer (none of the “bes fixlet” properties are included). If you want a list of Fixlets, one Fixlet per result, and a list of details for all of the applicable computers for the given fixlet included in the same result, you might be able to try something like

(id of it, name of it, concatenation "; " of 
            (name of it & ", " & concatenation "|" of (ip addresses of it as string) & ", " & operating system of it as string) 
                        of applicable computers of it)
 ) of bes fixlets whose 
         (applicable computer count of it > 0 and 
           source severity of it != "" and 
           source severity of it != "Unspecified" and 
           source severity of it != "N/A" and 
           source severity of it != "" and 
           display name of site of it equals "Patches for Windows (English)")

Personally I find that ends up being too much detail for reporting. I rarely include a list of IP addresses in a report like that, and only occasionally include a list of computer names. Instead I tend to include “number of applicable computers of it” for the bes fixlets, or sum up for each OS with something like (it, multiplicity of it) of unique values of names of operating systems of applicable computers of bes fixlets whose (...)

If you’re doing this for use in the BES Console or viewed directly in Web Reports (rather than something printed / exported), you might find it useful to get clickable HTML links with links of bes computers and links of bes fixlets rather than putting too much other detail in.

[plane]

Jason/Steve,

Ultimately my goal is to obtain something like this:

• computerA, IP address, o/s, fixlet123, fixlet description, severity
• computerA, IP address, o/s, fixlet246, fixlet description, severity
• computerA, IP address, o/s, fixlet345, fixlet description, severity
• …
• computerB, IP address, o/s, fixlet246, fixlet description, severity
• computerB, IP address, o/s, fixlet456, fixlet description, severity
• …

Essentially a list of all computers having applicable fixlets, each row containing a unique combination of computer & fixlet details. I’m doing this through the REST API for export to another application. The sorting order is not important. For instance, instead of sorting by computer then fixlet as above, the reverse is also acceptable (fixlet then computer).

Looking back, I see my original post omitted some of these details and I apologize if that caused any confusion.

[gearoid]

Here’s another way to work through this. Try these in the presentation debugger. Highlight the its to see what each refers to as you go.

you’re looking for details of your computers … so start with that:

bes computers

now the details you want of a computer:

(name of it, concatenations "," of (ip addresses of it as string)) of bes computers

now the relevant fixlets of each computer:

(name of it, concatenations "," of (ip addresses of it as string), ids of relevant fixlets of it) of bes computers

now some more of the properties of the relevant fixlets:

(name of it, concatenations "," of (ip addresses of it as string), (id of it, name of it) of relevant fixlets of it) of bes computers

now filter down the relevant fixlets to those with a severity:

(name of it, concatenations "," of (ip addresses of it as string), (id of it, name of it, source severity of it) of relevant fixlets whose (source severity of it != "") of it) of bes computers

[plane]

Using the advice posted in this topic, my query now looks like this:

(hostname of it,
ip addresses of it as string, operating systems of it,
(id of it,
name of site of it,
name of it,
source of it,
categories of it,
message of it,
cve id lists of it,
source severity of it)
of relevant fixlets
whose (source severity of it != ""
and source severity of it != ""
and source severity of it != "Unspecified"
and source severity of it != “N/A”) of it)
of bes computers

A few things I notice with respect to the cve id list of property. According to the docs, this clause should return a simple string.This is also the case with the name of property. Why is it then that the former must be plural, but the latter can be singular or plural? At least, that appears to be the case in the context of this query.

Also, if I remove the cve id lists of it clause from the query, it returns more rows. I don’t anticipate that every fixlet will have associated CVEs, so I would expect this string to be empty/null in many (if not most) cases. I would also expect that this would not have any impact on the number of rows returned. Do I need to modify the query somehow to say “give me all fixlets, even those without any CVEs defined”? (I kind of thought it would do that by default.)

[JasonWalker]

You can only use a singular if there is exactly one result. Plurals are used for both multiple results, as well as for zero results. So you have to use the plural form “cve id lists of it” in case there is no CVE value at all.; else you get the “singular expression refers to nonexistant object” result.

In any case where any of the properties you query has no result, the entire row is excluded. So, in addition to using the plural cve id lists of it, you also have to check for existence if any of the fields may be empty. And the results of both sides of an “if/then/else” must be the same type & plurality, so you have to take your (potentially) multiple CVE IDs and concatenate them to get one result. And a vagary of using if/then/else is that at some point the context of “it” may be lost, so you should put the clause in parentheses. Something like

message of it, (if exists cve id lists of it then concatenation ";" of cve id lists of it else "<no cve>"), source severity of it

Of course, you may need to use this if/then/else construct on any of the other optional fields, such as category and ip addresses (potential for ipv6-only). I’m also not sure whether every fixlet has a ‘message’ property either.

[gearoid]

quick tip - if you surround your relevance statement in markdown it’ll format it correctly.
use this markdown to surround your relevance statement [that’s 3 ticks followed by the word relevance, and close it with another three ticks ]

```relevance

[plane]

I feared I’d have to do something like that. Gotta lose this SQL mindset…

Thanks.

[jgstew]

@JasonWalker

message of it, (if exists cve id lists of it then concatenation “;” of cve id lists of it else “<no cve>”), source severity of it

You can do this instead, assuming there is at most 1 cve id list:

message of it, (cve id list of it | "<no cve>"), source severity of it

This should work if there can be multiple:

message of it, (concatenation ";" of cve id lists of it | "<no cve>"), source severity of it