Failing to pass an IP_Address from inner search to where cidrmatch(x,IP_Address) in the outer search

[pavel_ter]

Hello,

Need some help here. 

The goal is to pass one IP_Address found in inner search to outer search. IP is correctly extracted, but I'm getting following error from "where" command and clueless at this point. 

Here's the error: Error in 'where' command: The operator at '10.132.195.72' is invalid.

And here's the search:

index=ipam sourcetype=data earliest=-48h latest=now()
| where cidrmatch(name, IP_Address)
[ search index=networksessions sourcetype=microsoft:dhcp (Description=Renew OR Description=Assign OR Description=Conflict) earliest=-15min latest=now()
| head 1
| return ($IP_Address) ]

[pavel_ter]

Thank you, but no go here, unfortunately. appendcols has to be used after stats, timechart or chart(error generated by Splunk).

[ITWhisperer]

Try adding stats count by _raw?

[pavel_ter]

nope, error of course is gone, but no results from search and when running both searches separately, while manually passing results from inner to outer, I do get results.

[ITWhisperer · Accepted solution]

Try stats values(*) as * values(_*) as _* by _raw

[pavel_ter]

This worked! Thank you.

Now questions:

1.  For my education, what was conceptually wrong with my previous attempt to pass IP_Address value to outer search via return ?
2. Why, in order for appendcols to work, there is a need to create a stats table consisting of all cells in the outer search ? I tried to experiment by leaving only values(*) as * by _row or the opposite and it didn't work. 

Would appreciate some insight on these 2 questions.

Best regards, Pavel.

[ITWhisperer]

1. The where in the outer search seems to have expanded to

| where cidrmatch(name,IP_Address) 10.132.195.72​

 which is what the error message was saying was wrong. Perhaps

| where cidrmatch(name, [innser search | return ($IP_Address)] )​

might have worked but I haven't seen this syntax used before.

• According to the error you got, appendcols needs a table (returned by stats, timechart etc.). I am not sure whether just using table would have worked instead of the stats (perhaps you could try that too). The stats command will only pass the fields define in the stats command further down the pipeline. The by _raw clause is used to (hopefully) keep all the events separate. If any of your _raw were duplicated, then these events are effectively dedup'd and since the aggregations are values functions, these will dedup the fields too, so it is likely to fine. The values(*) will only pick up all the fields which don't start with _ i.e. _time would not be carried through, but values(_*) will pick it up.  Therefore all fields extracted up to the point will pass through the stats command.

[ITWhisperer]

Try something like this

index=ipam sourcetype=data earliest=-48h latest=now()
| appendcols
[ search index=networksessions sourcetype=microsoft:dhcp (Description=Renew OR Description=Assign OR Description=Conflict) earliest=-15min latest=now()
| head 1
| table IP_Address ]
| filldown IP_Address
| where cidrmatch(name, IP_Address)