Field diff search

[aikn061]

Hi guys,

I know this has been asked many times before but it just wont work for me hence the question.

I have one index with two sourcetypes.  Both sourcetypes have a field called host.  All I want to do is find hosts that exist in sourcetype 1 but not in sourcetype 2.

So index=eventlog.  sourcetype=security and sourcetype=application.  Both have fields called host.  I want to know host that exist in security souretype but not in application.

I have tried:

Index=eventlog sourcetype=serucurity | search NOT [ sourcetype=application | stats count by host]

Index=eventlog sourcetype=security NOT [search sourcetype=applcation | status count by host  ]

I was also looking in to the append method but haven't found luck there either.

So, how do I find hosts that are in sourceytpe=security but missing in sourcetype=application?

Any help is greatly appreciated in advance.

[sravankaripe]

please try this

index=someIndex sourcetype=sourcetype1 (NOT (index=someIndex sourcetype=sourcetype2 | return 100000 host)  )    | table host 

[aikn061]

Hello sravankaripe,

The NOT search is first thing I tried but it did not work.  Though I tried it without the () you used in your search.  That sounds interesting, I will give it a try.   For now, I was able to fix this as I mentioned below.

[ITWhisperer]

index=eventlog sourcetype="security" OR sourcetype="applcation" 
| status values(sourcetype) as allsourcetypes by host
| search allsourcetypes="security" allsourcetypes!="application"

[aikn061]

Hi ITWhisperer,

Thanks for quick response.  This did not help.  Changing status to stats in second line I am getting all hosts in security log.

I am looking for only hosts that are in security log but are missing in application log.  

[ITWhisperer]

Yes, status should have been stats. Here is a run-anywhere example showing how it works.

| makeresults
| eval _raw="sourcetype,host
security,A
application,A
security,B
security,C
application,C"
| multikv forceheader=1
| stats values(sourcetype) as allsourcetypes by host
| search allsourcetypes="security" allsourcetypes!="application"

Is the host in the security log exact the same format as the corresponding host in the application log? Can you share anonymised examples from the application log and security log for the same host?

[aikn061]

Hello ITWhisperer,

Just wanted to circle back.  We solved this with below.  So if you have two sources with field with same name and same data, you can find missing hosts in one of sources with below.

index=eventlog source=security host=naming_format*
| stats count by host
| append
[ search index=eventlog source=application host=naming_format*
| stats count as count_application by host]
| stats values(count_application) as count_application by host
| where isnull(count_application)

[aikn061]

yes the field for host is exactly as I type "host' in both logs.  

There is also a field called hostname which is the actual hostname as reported in EventViewer.  I tried this as well with the same search that you sent and no luck.

Let me see what I can do about sharing anonymized  log.