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

[crossap]

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!

[koshyk · Accepted solution]

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=*

[crossap]

Hi,

Koshyk's answer got me close to my end result

| dbquery "DATABASE" "SELECT * FROM softwareinventory" | fields Application | dedup Application | join type=left Application [ | dbquery "DATABASE" "SELECT * FROM authorisedsoftware" | fields Application | stats count by Application | where count = 1 ] | where NOT count>0

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

[richgalloway]

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 ]

[crossap]

Hi Rich,

Sadly this one doesn't seem to work.

It displays items showing on my whitelist

[richgalloway]

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

[crossap]

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.

[richgalloway]

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" | ...