Splunk Search

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

AZURAKAGAMI
Engager

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

Labels (2)
Tags (3)
0 Karma
1 Solution

AZURAKAGAMI
Engager

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=*

 

View solution in original post

0 Karma

AZURAKAGAMI
Engager

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=*

 

0 Karma

gcusello
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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

0 Karma
Get Updates on the Splunk Community!

New in Splunk Observability Cloud: Automated Archiving for Unused Metrics

Automated Archival is a new capability within Metrics Management; which is a robust usage & cost optimization ...

Calling All Security Pros: Ready to Race Through Boston?

Hey Splunkers, .conf25 is heading to Boston and we’re kicking things off with something bold, competitive, and ...

What's New in Splunk Observability - July 2025

What’s New?  We are excited to announce the latest enhancements to Splunk Observability Cloud as well as what ...