How to Avoid Cartesian Product

(imported topic written by ktakada91)

Hi,

I am trying to get a list of network shares on all the hosts where the BigFix client is installed along with some other information:

names of network shares, paths of network shares, hostnames, addresses of adapters of network, operating system

We want to feed this list to a DLP system and scan the shares for sensitive information. One part I am stuck at is that the above relevance obviously returns redundant, or all the possible combination of the result in each field. I think this is called Cartesian product in SQL language.

Can someone let me know how to get only the correct combination of the results?

Example of the result from the above relevance:

IPC$, , computer A, 192.168.x.x, WinXP 5.1.2600

IPC$, , computer A, 0.0.0.0, WinXP 5.1.2600

IPC$, , computer A, 10.x.x.x, WinXP 5.1.2600

IPC$, C:\temp\bigfix, computer A, 192.168.x.x, WinXP 5.1.2600

IPC$, C:\temp\bigfix, computer A, 0.0.0.0, WinXP 5.1.2600

IPC$, C:\temp\bigfix, computer A, 10.x.x.x, WinXP 5.1.2600

IPC$, C:\WINDOWS, computer A, 192.168.x.x, WinXP 5.1.2600

IPC$, C:\WINDOWS, computer A, 0.0.0.0, WinXP 5.1.2600

IPC$, C:\WINDOWS, computer A, 10.x.x.x, WinXP 5.1.2600

IPC$, C:, computer A, 192.168.x.x, WinXP 5.1.2600

IPC$, C:, computer A, 0.0.0.0, WinXP 5.1.2600

IPC$, C:, computer A, 10.x.x.x, WinXP 5.1.2600

bigfix, , computer A, 192.168.x.x, WinXP 5.1.2600

bigfix, , computer A, 0.0.0.0, WinXP 5.1.2600

bigfix, , computer A, 10.x.x.x, WinXP 5.1.2600

bigfix, C:\temp\bigfix, computer A, 192.168.x.x, WinXP 5.1.2600

bigfix, C:\temp\bigfix, computer A, 0.0.0.0, WinXP 5.1.2600

bigfix, C:\temp\bigfix, computer A, 10.x.x.x, WinXP 5.1.2600

bigfix, C:\WINDOWS, computer A, 192.168.x.x, WinXP 5.1.2600

bigfix, C:\WINDOWS, computer A, 0.0.0.0, WinXP 5.1.2600

bigfix, C:\WINDOWS, computer A, 10.x.x.x, WinXP 5.1.2600

bigfix, C:, computer A, 192.168.x.x, WinXP 5.1.2600

bigfix, C:, computer A, 0.0.0.0, WinXP 5.1.2600

bigfix, C:, computer A, 10.x.x.x, WinXP 5.1.2600

ADMIN$, , computer A, 192.168.x.x, WinXP 5.1.2600

ADMIN$, , computer A, 0.0.0.0, WinXP 5.1.2600

ADMIN$, , computer A, 10.x.x.x, WinXP 5.1.2600

ADMIN$, C:\temp\bigfix, computer A, 192.168.x.x, WinXP 5.1.2600

ADMIN$, C:\temp\bigfix, computer A, 0.0.0.0, WinXP 5.1.2600

ADMIN$, C:\temp\bigfix, computer A, 10.x.x.x, WinXP 5.1.2600

ADMIN$, C:\WINDOWS, computer A, 192.168.x.x, WinXP 5.1.2600

ADMIN$, C:\WINDOWS, computer A, 0.0.0.0, WinXP 5.1.2600

ADMIN$, C:\WINDOWS, computer A, 10.x.x.x, WinXP 5.1.2600

ADMIN$, C:, computer A, 192.168.x.x, WinXP 5.1.2600

ADMIN$, C:, computer A, 0.0.0.0, WinXP 5.1.2600

ADMIN$, C:, computer A, 10.x.x.x, WinXP 5.1.2600

C$, , computer A, 192.168.x.x, WinXP 5.1.2600

C$, , computer A, 0.0.0.0, WinXP 5.1.2600

C$, , computer A, 10.x.x.x, WinXP 5.1.2600

C$, C:\temp\bigfix, computer A, 192.168.x.x, WinXP 5.1.2600

C$, C:\temp\bigfix, computer A, 0.0.0.0, WinXP 5.1.2600

C$, C:\temp\bigfix, computer A, 10.x.x.x, WinXP 5.1.2600

C$, C:\WINDOWS, computer A, 192.168.x.x, WinXP 5.1.2600

C$, C:\WINDOWS, computer A, 0.0.0.0, WinXP 5.1.2600

C$, C:\WINDOWS, computer A, 10.x.x.x, WinXP 5.1.2600

C$, C:, computer A, 192.168.x.x, WinXP 5.1.2600

C$, C:, computer A, 0.0.0.0, WinXP 5.1.2600

C$, C:, computer A, 10.x.x.x, WinXP 5.1.2600

Thanks!

Kotaro Takada

(imported comment written by NoahSalzman)

Is this what you want:

names of network shares; paths of network shares; hostnames; addresses of adapters of network as string; operating system as string

(imported comment written by jeremylam)

If you just wanted a single result for each computer, you could concatenate each list into a single string and concatenate them all together. For one item:

concatenation ", " of (addresses of adapters of network as string)

I’m assuming you want the names and paths of the network shares associated with each other:

concatenation "; " of (concatenation ", " of ((name of it as string & " " & path of it as string) of network shares as string); concatenation ", " of hostnames; concatenation ", " of (addresses of adapters of network as string); operating system as string)

Here I’m using commas to separate out individual items and semicolons to separate out lists of items.

(imported comment written by ktakada91)

Noah- Not quite, but glad to see you still being active here.

Jeremy- This is awesome!

One more newbie question: As you can imagine, the results will contain a lot of C$, IPC$ and ADMIN$. What is the most efficient relevance to remove any share names that ends with $? I tried some if exists then, but I can’t get the syntax right so far.

Thanks!

Kotaro Takada

(imported comment written by NoahSalzman)

q: names of network shares

A: ADMIN$

A: C$

A: IPC$

q: (names of network shares) whose (character (length of it - 1) of it = “$”)

A: ADMIN$

A: C$

A: IPC$

And using Regex… you have to escape the “literal $” symbol and leave the “end of line $” unescaped.

q: (names of network shares) whose (exists match (regex “$$”) of it)

A: ADMIN$

A: C$

A: IPC$

If you just wanted shares that ended in “N$” you would do this:

q: (names of network shares) whose (exists match (regex “N$$”) of it)

A: ADMIN$

But you want to exclude those… the easiest way to do that would be:

q: (names of network shares) whose (not exists match (regex “$$”) of it)

(imported comment written by ktakada91)

So close… Now I am getting a singular/non-unique error in the debugger.

q: concatenation "; " of (concatenation ", " of ((name of network share as string) whose (not exists match (regex “$$”) of it) & " " & (path of network share as string)); concatenation ", " of hostnames; concatenation ", " of (addresses of adapters of network as string); operating system as string)

E: Singular expression refers to non-unique object.

q: concatenation "; " of (concatenation ", " of ((names of network shares as string) whose (not exists match (regex “$$”) of it) & " " & (paths of network shares as string)); concatenation ", " of hostnames; concatenation ", " of (addresses of adapters of network as string); operating system as string)

E: A singular expression is required.

(imported comment written by NoahSalzman)

q: concatenation "; " of (concatenation ", " of ((name of it as string & " " & path of it as string) of network shares as string); concatenation ", " of hostnames; concatenation ", " of (addresses of adapters of network as string); operating system as string)

A: ADMIN$ C:\Windows, C$ C:, IPC$ ; surprise; 0.0.0.0, 172.16.131.128; Win2008 6.0.6002

q: concatenation "; " of (concatenation ", " of ((name of it as string & " " & path of it as string) of network shares whose (not exists match (regex “$$”) of name of it) as string); concatenation ", " of hostnames; concatenation ", " of (addresses of adapters of network as string); operating system as string)

A: ; surprise; 0.0.0.0, 172.16.131.128; Win2008 6.0.6002

(imported comment written by ktakada91)

Belated thank you, Noah.