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
Path Finder

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!

0 Karma
1 Solution

koshyk
Super Champion

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

View solution in original post

koshyk
Super Champion

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

View solution in original post

crossap
Path Finder

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
SplunkTrust
SplunkTrust

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 ]
---
If this reply helps you, an upvote would be appreciated.
0 Karma

crossap
Path Finder

Hi Rich,

Sadly this one doesn't seem to work.

It displays items showing on my whitelist

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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

---
If this reply helps you, an upvote would be appreciated.
0 Karma

crossap
Path Finder

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.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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" | ...
---
If this reply helps you, an upvote would be appreciated.
0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!