topic Re: How to list non common fields of two table in Splunk Search

How to list non common fields of two table

ashish9433 — Tue, 24 Jan 2017 10:43:07 GMT

Hi Team,

I am looking to find out a solution where in i have two tables and i am interested in listing out only those fields which are not common.

The below image list out the problem statement along with desired result.

Re: How to list non common fields of two table

gcusello — Tue, 24 Jan 2017 11:58:15 GMT

Hi ashish9433,

you should build something like this:

search1 
| eval count=0 
| append [ search search2 | stats count by Alfabet, No, TableName]
| stats sum(count) AS check by Alfabet, No, TableName
| where check=0

in this way you list all the groups (Alfabet, No, TableName) of the first search that aren't present in the second one.
If instead you want only the common groups you have tu use | where check>0 in your search.

Beware that the three fields used in stats command must have the same name in both the searches.
If values could have uppercases or lowercases differences, you have to transforms all values in the same case (upper or lower)

Bye.
Giuseppe

Re: How to list non common fields of two table

woodcock — Tue, 24 Jan 2017 17:48:56 GMT

For run-anywhere example and solution:

| makeresults
| eval raw="A,1,1:A,10,1:B,2,1:C,3,1:D,4,1:E,5,1:A,1,2:A,11,2:B,2,2:C,3,2:E,5,2:F,6,2:G,7,2"
| fields - _time
| makemv delim=":" raw
| mvexpand raw
| rex field=raw "^(?<Alphabet>[^,]*),(?<No>[^,]*),(?<TableName>[^,]*)$"
| eval TableName="Table" . TableName
| rename COMMENT AS "Everything above is spoofing events, everything below is the actual solution"
| eval AlphabetNo = Alphabet . ":" . No
| eval AlphabetNoTable = AlphabetNo . ":" . TableName
| eval AlphabetNoTableOther = AlphabetNo . ":" . if(TableName="Table1", "Table2", "Table1")
| eventstats values(AlphabetNoTable) AS AlphabetNoTables    
| where AlphabetNoTableOther != AlphabetNoTables
| table Alphabet No TableName
| sort 0 Alphabet TableName No

Re: How to list non common fields of two table

ashish9433 — Wed, 25 Jan 2017 08:43:01 GMT

This worked as required, Thanks @woodcock

Re: How to list non common fields of two table

ashish9433 — Wed, 25 Jan 2017 08:44:37 GMT

This option didn't worked for me for some reason the result contained everything of table 1, but i wanted only contents which are non common.

Thanks for your revert though!