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.
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
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
... 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.
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
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?
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
@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.
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