Compare two tables and show the recidivists

[northward]

Hello,

I would like to know how to compare two tables and present the results that are the same.

I have the following table structure:

"Host" "Vulnerability" "Port" "SO" "_time"

I need to compare to see if the lines are the same at different times.

I also have the files without being grouped, they follow the same structure ("Host" "Vulnerability" "Port" "OS" "_time"), but each month is in a different file.

Can someone please help me?

[AlB]

Hi @northward

Can you try explaining a bit more with a sample of your data and an example of what you need based on that sample?

[northward]

For example, I have the following table: 

Month	CVE	Host	Vulnerability	Port	Protocol	SO
November	CVE-1999-0103	10.110.0.141	Echo Service Detection	7	udp	Microsoft Windows 7 Professional
November	CVE-1999-0103	10.110.0.141	Quote of the Day (QOTD) Service Detection	17	udp	Microsoft Windows 7 Professional
November	CVE-1999-0508	10.10.0.18	PostgreSQL Default Unpassworded Account	5432	tcp	Linux Kernel 3.10 Linux Kernel 3.5 Linux Kernel 3.8 Linux Kernel 3.9
November	CVE-1999-0508	10.10.0.22	PostgreSQL Default Unpassworded Account	5432	tcp	Linux Kernel 3.10 Linux Kernel 3.5 Linux Kernel 3.8 Linux Kernel 3.9
November	CVE-1999-0511	10.110.0.1	IP Forwarding Enabled	0	tcp	CISCO IOS 15 CISCO IOS 12 Cisco IOS XE CISCO PIX
November	CVE-1999-0511	10.110.0.220	IP Forwarding Enabled	0	tcp	Mac OS X 10.4
November	CVE-1999-0511	10.110.0.221	IP Forwarding Enabled	0	tcp	Mac OS X 10.4
November	CVE-1999-0511	10.110.0.222	IP Forwarding Enabled	0	tcp	Mac OS X 10.4
November	CVE-1999-0511	10.110.0.224	IP Forwarding Enabled	0	tcp	Mac OS X 10.4
November	CVE-1999-0511	10.110.0.225	IP Forwarding Enabled	0	tcp	Mac OS X 10.4
January	CVE-1999-0511	10.110.0.227	IP Forwarding Enabled	0	tcp	Mac OS X 10.4
January	CVE-1999-0517	10.2.9.100	SNMP Agent Default Community Name (public)	161	udp	Lantronix Universal Device Server UDS1100
January	CVE-1999-0517	10.9.0.135	SNMP Agent Default Community Name (public)	161	udp	CISCO IOS 12.4(21)
January	CVE-1999-0517	192.168.1.6	SNMP Agent Default Community Name (public)	161	udp	Linux Kernel 2.6
January	CVE-1999-0517	192.168.2.6	SNMP Agent Default Community Name (public)	161	udp	Linux Kernel 2.6
January	CVE-1999-0519	10.10.0.136	Microsoft Windows SMB Shares Unprivileged Access	445	tcp	Linux Kernel 3.10 on CentOS Linux release 7
January	CVE-1999-0519	10.9.0.55	Microsoft Windows SMB NULL Session Authentication	445	tcp	Microsoft Windows Server 2003 Service Pack 2
January	CVE-1999-0524	10.10.0.104	ICMP Timestamp Request Remote Date Disclosure	0	icmp	Microsoft Windows Server 2008 R2 Standard Service Pack 1
January	CVE-1999-0524	10.10.0.11	ICMP Timestamp Request Remote Date Disclosure	0	icmp	Linux Kernel 3.13 on Ubuntu 14.04 (trusty)
January	CVE-1999-0524	10.10.0.111	ICMP Timestamp Request Remote Date Disclosure	0	icmp	Microsoft Windows Server 2012 R2 Standard
January	CVE-1999-0511	10.110.0.222	IP Forwarding Enabled	0	tcp	Mac OS X 10.4
January	CVE-1999-0511	10.110.0.224	IP Forwarding Enabled	0	tcp	Mac OS X 10.4
January	CVE-1999-0511	10.110.0.225	IP Forwarding Enabled	0	tcp	Mac OS X 10.4

I want to compare if the vulnerability that we found in November still appears in January Scan. 

In this example, the result should be a table with the following results:

January	CVE-1999-0511	10.110.0.222	IP Forwarding Enabled	0	tcp	Mac OS X 10.4
January	CVE-1999-0511	10.110.0.224	IP Forwarding Enabled	0	tcp	Mac OS X 10.4
January	CVE-1999-0511	10.110.0.225	IP Forwarding Enabled	0	tcp	Mac OS X 10.4

Because those three are the only ones that repeat in the last and actual scan. 

[AlB]

@northward

OK, let's see if this helps. Create a new calculated column in your table (Table1 is the name of the table):

CountColumn =
COUNTROWS ( CALCULATETABLE ( Table1; ALL ( Table1[Month] ) ) )

That makes use of context transition to give you, for each row, the number of times the row appears in the table. You can then select to view the ones with a value greater than 1 in a table visual (and filter to show only January for instance).

[northward]

I think it worked, but he is considering every month (August, September, November, November, December and January), is it possible for me to specify the months I want to compare, or to always consider the last two months?

[v-cherch-msft]

Hi @northward

As AIB said, you may use slicer to specify the months.I would suggest you ay create below two measures:

CountMeasure = CALCULATE(COUNTROWS(Table1),ALLSELECTED(Table1[Month]))

LastMonth = IF(MAX(Table1[MonthNo])=MAXX(ALLSELECTED(Table1),Table1[MonthNo]),1)

Then use them in visual level filter. CountMeasure>1 and LastMonth =1. Attached the sample file for your reference.

Regards,

Cherie

[northward]

How I can make count only the ones that appear more than once? Should I use and "where"?

[v-cherch-msft]

Hi @northward

Could you tell me if your problem has been solved? If it is,kindly mark the helpful answer as a solution and welcome to share your own solution. More people will benefit from here. If not, please share more details for us so that we could help further on it.

Regards,

Cherie

[v-cherch-msft]

Hi @northward

You may create a measure to get the count.Then set the measure is greater than 1 in visual level filter.Here is the article about this feature for your reference.Please check page 2 in the sample file.

Measure = CALCULATE(COUNTROWS('Table1 (2)'),ALL('Table1 (2)'[Month]))

Regards,

Cherie

[AlB]

@northward

You can set Month in a slicer and select the two months you are interested in