topic Re: Splunk DB Connect 1: How to edit my search to compare a column from two databases and output anything that is different? in Splunk Search

Splunk DB Connect 1: How to edit my search to compare a column from two databases and output anything that is different?

crossap — Thu, 28 May 2015 13:55:57 GMT

Hi,

I am working on a search string to extract a specific column named Applications from 2 databases

I would then like to compare these 2 databases and output anything that is different

This is to allow us to gather a list of un-approved software within the environment

here is what I have so far

| dbquery "DATABASE" "SELECT * FROM softwareinventory" | fields Application | dedup Application | appendcols [ | dbquery "DATABASE" "SELECT * FROM authorisedsoftware" | fields Application | rename Application to whitelist ]

This returns all of the data, but I am unsure of how to compare the 2 columns and output those differences.

thanks!

Re: Splunk DB Connect 1: How to edit my search to compare a column from two databases and output anything that is different?

richgalloway — Thu, 28 May 2015 15:16:25 GMT

What you want is to discard those rows in softwareinventory that are also present in authorisedsoftware, leaving a list of unauthorised software. I think this is a job for the join command. This should get you started:

| dbquery "DATABASE" "SELECT FROM softwareinventory" | fields Application | dedup Application | join type=inner Application [ | dbquery "DATABASE" "SELECT FROM authorisedsoftware" | fields Application ]

Re: Splunk DB Connect 1: How to edit my search to compare a column from two databases and output anything that is different?

crossap — Mon, 01 Jun 2015 11:20:44 GMT

Hi Rich,

Sadly this one doesn't seem to work.

It displays items showing on my whitelist

Re: Splunk DB Connect 1: How to edit my search to compare a column from two databases and output anything that is different?

richgalloway — Mon, 01 Jun 2015 11:37:53 GMT

Try swapping the searches. Put authorisedsoftware in the main search and softwareinventory in the subsearch.

Re: Splunk DB Connect 1: How to edit my search to compare a column from two databases and output anything that is different?

crossap — Tue, 02 Jun 2015 12:36:23 GMT

Hi Rich,

sorry for slow reply I tried both ways and seem to get the same results its showing my authorisedlist each time as I can see applications that are allowed within our business.

Re: Splunk DB Connect 1: How to edit my search to compare a column from two databases and output anything that is different?

richgalloway — Tue, 02 Jun 2015 12:46:08 GMT

Your experience appears to contradict the documentation that says "results of an inner join will not include
any events from the main search with no matches". Perhaps there's another way. This is a bit more convoluted.

| dbquery "DATABASE" "SELECT FROM softwareinventory" | fields Application | dedup Application | [ | dbquery "DATABASE" "SELECT FROM authorisedsoftware" | outputlookup auth.csv ] | lookup auth.csv Application OUTPUT someColumnPresentOnlyInAuth | eval Auth=if (isnull(someColumnPresentOnlyInAuth), "NO","Yes") | where Auth="NO" | ...

Re: Splunk DB Connect 1: How to edit my search to compare a column from two databases and output anything that is different?

koshyk — Tue, 02 Jun 2015 13:46:20 GMT

Try doing a "LEFT" join with left column being "softwareinventory" , have atleast two columns from "authorisedSoftware" table and on final output compare whichever is NOT null.
(The logic is, left side part would be your softwareinventory and anything which is not matched on right-hand side will output null values)

Something like..

| dbquery "DATABASE" "SELECT * FROM softwareinventory"  | fields Application| dedup Application  | join type=left Application  [ | dbquery "DATABASE" "SELECT * FROM authorisedsoftware" |  fields Application | dedup Application| eval DummyColumn="Table2"| fields Application, DummyColumn]  | search NOT DummyColumn=*

Re: Splunk DB Connect 1: How to edit my search to compare a column from two databases and output anything that is different?

crossap — Tue, 02 Jun 2015 14:34:01 GMT

Hi,

Koshyk's answer got me close to my end result

Anything that's on the white list receives a 1 count, I just then filtered out any that did not have a count >0

thanks again Rich/Koshyk