Splunk Search

inputlookup |append [search] vs search | append [|inputlookup]

MonkeyK
Builder

I am getting different results for the following two queries and I cannot understand why

(index=windows) EventCode IN (4624,4625,4648) TargetAccountName!="-" ComputerName=*mydomain
| eval acctN=mvindex(Account_Name,1) 
| search acctN=* 
| bin _time span=1d as date 
| eval ComputerName=replace(ComputerName,".mydomain","") 
| eval user=upper(acctN) 
| eval domain=upper(TargetAccountDomain) 
| stats values(EventCode) as EventCodes values(date) as DaysSeen earliest(_time) as earliest latest(_time) as latest by ComputerName user Logon_Type 
| sort 0 user ComputerName 
| search user=myID
| append [| inputlookup user_device_logon.csv | search user=myID] 
| sort 0 user ComputerName 
| eval earliest=strftime(earliest,"%F"), latest=strftime(latest,"%F")

This returns 20 items. But If I revers the order of the component searches:

| inputlookup user_device_logon.csv | search user=myID
| append 
    [ search (index=windows) EventCode IN (4624,4625,4648) TargetAccountName!="-" ComputerName=*mydomain
    | eval acctN=mvindex(Account_Name,1) 
    | search acctN=* 
    | bin _time span=1d as date 
    | eval ComputerName=replace(ComputerName,".mydomain","") 
    | eval user=upper(acctN) 
    | eval domain=upper(TargetAccountDomain) 
    | stats values(EventCode) as EventCodes values(date) as DaysSeen earliest(_time) as earliest latest(_time) as latest by ComputerName user Logon_Type 
    | sort 0 user ComputerName 
    | search user=myID] 
| sort 0 user ComputerName 
| eval earliest=strftime(earliest,"%F"), latest=strftime(latest,"%F")

This returns 19 items.

If I run the two component searches separately, the lookup table returns 19 items and the windows event search returns 1 item.
The difference appears to be that the second search does not include the appended search results in the total results. What am I doing wrong here?

The second search is supposed to be better since the lookup table will get large and the appended search will usually be small. But it is not better if it prevents the windows search from returning data.

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi MonkeyK,
how many results you have from your main search and from your lookup?
in subsearches there's the limit of 50,000.
For this reason it's better to put always lookup (that usually has less than 50,000 rows) in subsearchs.

Bye.
Giuseppe

0 Karma

MonkeyK
Builder

Guiseppe,
The lookup will collect statistics over time, so I expect it to get very large. The subsearch will usually only summarize a small amount of the data.
In the case that I made for my question, the lookup held 19 record and the search (against an accelerated report query) returned 1 more record. As gpradeepkumarreddy guessed. The search was taking way longer than I would have guessed and consequentially timed-out

0 Karma

DalJeanis
SplunkTrust
SplunkTrust
... by ComputerName user Logon_Type 

Make sure that all the above fields are populated and spelled/capitalized exactly that way in the lookup table.

If any of them are null, they will "disappear" the lookup table input result.

0 Karma

MonkeyK
Builder

Yes, I've hit my head on that wall many times. The lookup table in my query is populated almost directly from the query part. The main difference being that the lookup table intends to summarize over all time, while the query is an accelerated report, so it summarizes over any arbitrary time range

0 Karma

pradeepkumarg
Influencer

Do you see any warnings in the job inspector? Particularly, some thing like sub search auto finalized due to max limit of time or max limit of number of records reached?

0 Karma

MonkeyK
Builder

good call gpradeepkumarreddy. I did not notice that append was taking so long.

I the append subsearch more and more time. I got out to 10 minutes and it was still hitting the limit.

Doubly weird since the subsearch used to run in about 11 seconds.

If you post your response as an answer, I will accept it

0 Karma

niketn
Legend

@MonkeyK, can you add result sample data? At least one that is present in both searches and the one that is present only in the first search.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

MonkeyK
Builder

niketnilay, for the sake of my company, and my job, I have changed the names to protect the innocent:

This record only shows up in the first search:

ComputerName    user    Logon_Type  EventCodes  DaysSeen    earliest            latest
SYSTEM1      myID   10             4624     1498798800  2017-06-30  2017-06-30

And this one shows up in both

ComputerName    user    Logon_Type  EventCodes  DaysSeen    earliest            latest
SYSTEM2      myID   11              4624        1507006800  2017-10-03  2017-10-03
0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...