Splunk Search

How to compare table columns from 2 different searches

mkranjec
New Member

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.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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"
0 Karma

mkranjec
New Member

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.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...