topic Re: How to compare table columns from 2 different searches in Splunk Search

How to compare table columns from 2 different searches

mkranjec — Tue, 13 Oct 2015 16:39:08 GMT

So I have two similar searches that use two different indexes. The output of both searches are tables and what I want is to compare 1st column from table 1 (result of Search 1) and 1st column from table 2 (result of Search 2).

Result should be diff of those two columns. Both columns contain a list of users and what I need are all users from column 1 (table1) that are not contained in column 2 (table2). Both columns in both tables are named "user" and both contain same usernames but 1st table has all usernames and 2nd table have just few usernames.

Search 1:

index="A" host="XYZ" source="ASDF" user="*" | table user host  _time

Search 2:

index="B" host="XYZ" source="FDSA" user="*" | table user host  _time

I thought that It can be solved with:

| set diff [Search1][Search2]

but for some reason it doesn't give me result that I need.

I would appreciate any help with this problem.

Re: How to compare table columns from 2 different searches

somesoni2 — Tue, 13 Oct 2015 18:21:54 GMT

Try something like this

  ( index="A" source="ASDF") OR (index="B" source="FDSA") host="XYZ" source="ASDF" user="*" | table user host _time source | eventstats values(source) as source

Now to get the users which are not present in both the index/source, add this to above search

...| where mvcount(source) =1

For users only in index A,

..| where mvcount(source)=1 AND source="ASDF"

Re: How to compare table columns from 2 different searches

mkranjec — Wed, 14 Oct 2015 10:53:40 GMT

In a hurry I responded this as Answer earlier so I ask moderator to ignore it if possible.

Back to the question. Somesoni2 your answer got me some results and I thank you on that but still it's not quite what I need.

New search looks like this:

[search index="A" source="/var/log/splunkusers" host="XYZ" user="*"] OR [search index="B" source="/var/log/secure" host="XYZ" user="*" | dedup user] | table user host _time source

and as a result I get:

userA       XYZ     2015-09-16 16:11:16 /var/log/secure
userB     XYZ   2015-09-23 15:24:38 /var/log/secure
userC        XYZ    2015-10-12 14:00:54 /var/log/secure
userA     XYZ   2015-10-14 07:42:29 /var/log/splunkusers
userB     XYZ   2015-10-14 07:42:29 /var/log/splunkusers
userC     XYZ   2015-10-14 07:42:29 /var/log/splunkusers
userD       XYZ 2015-10-14 07:42:29 /var/log/splunkusers
userF     XYZ   2015-10-14 07:42:29 /var/log/splunkusers

and result what I need is:

    userD        XYZ    2015-10-14 07:42:29 /var/log/splunkusers
    userF     XYZ   2015-10-14 07:42:29 /var/log/splunkusers

So basically I need only users from source /var/log/splunkusers that are not in /var/log/secure

you suggested use of:

| eventstats values(source) as source

but it just groups me sources and there is no row with only one source so that I could use:

...| where mvcount(source) =1

as you suggested. I get what you wanted to achieve and it would be ok. Can you please check my new search and example of results I get and results I need? It might give you better idea for possible solution.