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
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
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
Hi Giuseppe , Thank you so much for your query. It worked well and got the expected answer.
Thanks,
Yuvaraj
you're welcome!
Ciao and next time.
Giuseppe
This should get you started.
| inputlookup Table.csv NOT [ search index=foo SERVER=* DATA=* | stats count by SERVER, DATA | fields SERVER DATA | format ]