How do you get all matching and non-matching rows from a Splunk search and lookup?

[Shashank_87]

Hi,

I am working on a query where I have to match the responseCode from the search to the responseCode in a lookup I created. That lookup contains the responseCode and its description.

Now there are a few cases where the responseCode in the search does not match to anything in the lookup table. I want the count of all responseCodes. If it matches in the lookup then with its description, and if it doesn't match, then the description would be null, but I want the count.

My current search is not giving the count of the unmatched responseCode —

index="test" sourcetype="test_log" 
| dedup time,host,source,_raw 
| lookup Response_Codes_Desc ResponseCode
| stats count by ResponseCode Description
| sort - count

Please could someone help on this?

[jvishwak]

Check if this helps:
index="test" sourcetype="test_log"
| dedup time,host,source,_raw
| lookup Response_Codes_Desc ResponseCode
| eval NoMatch = if(isnull(Description), 1, 0)
| eventstats sum(NoMatch) as NoMatch, sum(eval(if(NoMatch = 0, 1, 0))) as MatchAvailable

[chrisyounger · Accepted solution]

what about this:

index="test" sourcetype="test_log" 
| dedup time,host,source,_raw 
| lookup Response_Codes_Desc ResponseCode
| eval Description = coalesce(Description, "")
| stats count by ResponseCode Description
| sort - count

[Shashank_87]

That is perfect Chris. Thank you very much. 🙂