Splunk Search

How to compare two searches and find a missing values in each search

AlexeySh
Communicator

Hello,

I try to compare the Active Directory (AD) logs with the antivirus (AV) logs in order to find two things:
- Assets with a (potential) functional problem of antivirus (asset present in AD, but not in AV logs)
- Assets with an AD connection problem (asset present in AV, but not in AD logs)

So I need to find assets missing in both searches and not only the difference between them (that's why I can't use set diffquery), something like that:

The result of initial searches:

AD   AV
A    A
B    B
C    C
D    F
E    G

The result i'd like to have:

miss_in_ad_logs   miss_in_av_logs
       F                 D
       G                 E

I tried to wright a query like in this question, but failed 😞 Could you please help with the search?
Here are the initial searches (almost identical and both are pretty simple):

index=antivirus_index | fields asset_name
| dedup asset_name
| table asset_name

-

index=ad_index | fields src_dns
| dedup src_dns
| table src_dns

Regards,
Alex.

0 Karma

oscar84x
Contributor

Could you try the search below?
The table should list both fields, with the field missing a value blank and the index listed should be the index containing the value.

index=antivirus_index 
| fields asset_name index 
| dedup asset_name 
| table asset_name index 
| append 
    [ search index=ad_index 
    | fields src_dns index 
    | dedup src_dns 
    | table src_dns index] 
| table asset_name src_dns index 
| where asset_name!=src_dns

AlexeySh
Communicator

Hello oscar84x,

Appreciate your help!

Unfortunately, it doesn't seem to work. Everything works perfectly except for the last line: for some reson it shows "no results found". I tried to replace where by search and search NOT, but with no success 😞

Finally I decided to perform to independent searches and show the results in a dashboard.

0 Karma
Get Updates on the Splunk Community!

Developer Spotlight with William Searle

The Splunk Guy: A Developer’s Path from Web to Cloud William is a Splunk Professional Services Consultant with ...

Major Splunk Upgrade – Prepare your Environment for Splunk 10 Now!

Attention App Developers: Test Your Apps with the Splunk 10.0 Beta and Ensure Compatibility Before the ...

Stay Connected: Your Guide to June Tech Talks, Office Hours, and Webinars!

What are Community Office Hours?Community Office Hours is an interactive 60-minute Zoom series where ...