Alerting

How do you compare two results?

Explorer

I have a table(main table).csv with field location.

I have raw logs that includes field location

main table.csv
location_field
A
B
C

Raw logs 
location_field
A 
B

Please, I need help with a query that will check raw logs against main table and give a result of C missing

search result should return C

0 Karma
1 Solution

Explorer

I got it done. Thanks
| inputlookup main_table.csv
| fields location
| eval count=0
| append
[ search index=someindex*
| fields location
| stats count BY location ]
| stats sum(count) AS missing by location
| where missing= 0

Whenever results return 0 that means those locations are not in main table

View solution in original post

0 Karma

Explorer

I got it done. Thanks
| inputlookup main_table.csv
| fields location
| eval count=0
| append
[ search index=someindex*
| fields location
| stats count BY location ]
| stats sum(count) AS missing by location
| where missing= 0

Whenever results return 0 that means those locations are not in main table

View solution in original post

0 Karma

Influencer

@btawiah-can you extract the filed that has location from your raw logs? If not then you will have to provide a log sample.
Once you extract the field say loc , the query can be something like this-

 |inputlookup maintable.csv| join type=outer location[search index=* | rename loc as location| fields source]| where ISNULL(source)
0 Karma

Explorer

I got it done. Thanks
| inputlookup main_table.csv
| fields location
| eval count=0
| append
[ search index=someindex*
| fields location
| stats count BY location ]
| stats sum(count) by location
| where count= 0

Whenever results return 0 that means those locations are not in main table

0 Karma

Explorer

@Vijeta i actually updated the question. I dont have to extract fields because that already exist. I only need to get the difference and output the one from raw logs since that is not in the main table location field.

0 Karma

Influencer

@btawiah Try running this query, write the name of your index instead of "yourindexname"

  |inputlookup maintable.csv| join type=outer location_field[search index="yourindexname"| fields source]| where ISNULL(source)
0 Karma