How to convert values in a single row with missing fields?

[AZURAKAGAMI]

Hi,

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

I have one field with lists of users from my csv file.  I took a specific range of users and trying to find out which of users are missing from my csv file. I cannot find a solution, can someone help me out?

My query.

|inputlookup filename.csv

|search Username IN (user A,  user B, User C, etc.)

|dedup Username |fields Username

[AZURAKAGAMI · Accepted solution]

Hi, Giuseppe,

Thank you for your quick response but that didn't help. Your search is giving me list of all users that I have in my csv file (which is enormous amount) but I found a solution to this.

Thank you for your time anyways.

| makeresults
| eval Username="User A, User B, User C, User D, User E, User F"
| makemv delim="," Username
| mvexpand Username
| fields - _time
| lookup filename.csv Username
| search NOT fieldname=*

[gcusello]

Hi @AZURAKAGAMI,

sorry I didn't understand your need, probably this solution is easier:

| makeresults
| eval Username="User A, User B, User C, User D, User E, User F"
| makemv delim="," Username
| mvexpand Username
| search NOT [ | inputlookup filename.csv | fields Username]

Ciao.

Giuseppe

P.S.: Karma Points are appreciated 😉

[gcusello]

Hi @AZURAKAGAMI,

you can use the solution for missed hosts that you can find in many answers (also from me) in Community.

So, if you have the Username field both in your main search and in your lookup, you could try something like this:

<your_search>
| eval Username=lower(Username)
| stats count BY Username
| append [ | inputlookup filename.csv | eval Username=lower(Username), count=0 | fields Username count ]
| stats sum(count) AS total BY Username
| where total=0

Ciao.

Giuseppe