How to display what values are missing in my lookup table comparing to actual data?

[yuvarajvelu]

How to display what values are missing in my lookup table comparing to actual data?

Table.csv
SERVER_A,DATA_A
SERVER_A,DATA_B
SERVER_A,DATA_C
SERVER_B,DATA_A
SERVER_B,DATA_D
SERVER_B,DATA_E
SERVER_C,DATA_B
SERVER_C,DATA_E
SERVER_C,DATA_C

Data:
SERVER=SERVER_A DATA=DATA_A
SERVER=SERVER_A DATA=DATA_C
SERVER=SERVER_B DATA=DATA_D
SERVER=SERVER_B DATA=DATA_E
SERVER=SERVER_C DATA=DATA_B
SERVER=SERVER_C DATA=DATA_E

Expected result:
SERVER_A,DATA_B
SERVER_B,DATA_A
SERVER_C,DATA_C

Thanks in Advance

[gcusello · Accepted solution]

Hi yuvarajvelu,
try this:

index=my_index
| eval SERVER=upper(SERVER), DATA=upper(DATA)
| stats count BY SERVER DATA
| append [ | inputlookup Table.csv | eval SERVER=upper(SERVER), DATA=upper(DATA), count=0 | fields SERVER DATA count ]
| stats sum(count) AS total BY SERVER DATA
| where total=0

Ciao.
Giuseppe

[yuvarajvelu]

Hi Giuseppe , Thank you so much for your query. It worked well and got the expected answer.

Thanks,
Yuvaraj

[gcusello]

you're welcome!
Ciao and next time.
Giuseppe

[richgalloway]

This should get you started.

| inputlookup Table.csv NOT [ search index=foo SERVER=* DATA=* | stats count by SERVER, DATA | fields SERVER DATA | format ]