Troubleshooting timebased lookup table

[samlinsongguo]

HI Everyone
Is there a way you can see how lookup table examed each value and make the call whether it is match or not. something like you read value A then go into lookup table check line one not match, check line two not match, check line three matched, return value B.

I have this time based lookup table looks like below

user1 gain_access 24/11/17 11:00
user2 gain_access 24/11/17 12:00
user1 gain_access 23/11/17 10:00
user1 gain_access 24/11/17 15:00
user2 gain_access 24/11/17 11:20

My data is standard Sysmon log

I run following queue

index=windowslog Image=*\\userinit.exe ParentImage=*\\winlogon.exe 
| eval AccountName=mvindex(User,1) 
| stats dc(_time) as eventNums by AccountName _time 
| rex field=AccountName "(?<Account_Name>\w*\.\w*)" 
| lookup swipe  FullUserName AS Account_Name OUTPUT Action AS hereitis 
| table Account_Name _time hereitis
| sort - _time

and the lookup result shows non of the record is match in the lookup table. but I know there are time and Account_Name is matching.
I test the lookup table by remove the timebased setting and only let it match the name and it works fine get following result

user1 24/11/17 11:00 gain_access
                     gain_access
                     gain_access
user2 24/11/17 12:00 gain_access
                     gain_access 

that make me think it is the time format failed the lookup but I cant tell the different between the lookup table and the actual search time field.

Any advice please?
Regards
Sam

[samlinsongguo · Accepted solution]

I finally figured out where the problem is.
first time based lookup only compare the time filed in lookup table with the default time stamp field _time
second when you do query you have to _time value is passed on all the way and that is where my problem occur. my search query didnt pass the _time filed down all the way. it lost the field somewhere so when I do the lookup search it does not have time field to compare with the lookup table, and that is why it only return no match result.
Thanks for your help and work with me to do the trouble shooting HiroshiSatoh

[samlinsongguo]

some update about what I have find.
Now I got the data into csv file looks like below

Account_Name, LogonAt
    user1 27/11/17 11:58
    user1 27/11/17 11:23
    user2 27/11/17 11:14
    user3 27/11/17 08:30

lookup table looks like below

Action,FullUserName,SwipeTime
granted entry user1 27/11/17 11:55
granted entry user2 27/11/17 10:55

when I import this csv into splunk as one off upload and run follow search it works as I wanted.

index= main sourcetype="csv" | lookup swipe  FullUserName AS Account_Name OUTPUT Action 

however if I use inputcsv commend with same date it does not work

| inputcsv LogonAct.csv | lookup swipe FullUserName AS Account_Name OUTPUT Action 

Can anyone tell me what the different of these two action please?
Thanks

[HiroshiSatoh]

Has the time field been recognized correctly?
Please confirm with [From the lookup timestamp _time log of the future].

If it is recognized correctly, it can be assumed that the timestamp of the log is in the past than the lookup time.

[samlinsongguo]

HI HiroshiSatoh
I am not quick understand what you mean by "Please confirm with [From the lookup timestamp _time log of the future]." do you mean there is a lookup log I can check what time stamp has been used in the time based lookup search?

I think the problem is the time field has not been recognized properly but I dont know why. in the search queue there are two time field, one is _time which is come with the event, another is LogonAt which is an eval field after initial search. I want the eval field LogonAt is the one use to match the time field in the lookup table. Does Splunk do that automatically or it only use the time come with initial event?

I tried with simple setup which I export the search query without lookup to a csv then import again, that way the LogonAt field already exist in the event then I do the lookup search is works as expected. do you think that is the problem?
Cheers

[HiroshiSatoh]

[From the lookup timestamp _time log of the future].
↑Please test with the log of _time that hits the lookup.

What can be thought of as a cause not to hit

・SwipeTime of time base lookup is illegal. Or format definition is invalid
・log:_time<SwipeTime
・_time does not exist

[HiroshiSatoh]

Can anyone tell me what the different of these two action please?

index= main sourcetype="csv"
_time exists.
| inputcsv LogonAct.csv
_time does not exist.

[samlinsongguo]

I think the different as below
index= main sourcetype="csv"
_time exists.
that is actual event so it would have the default time stamp,
| inputcsv LogonAct.csv
_time does not exist.
that is loading a csv so there is no default time stamp field _time.

for time based lookup does it only try to match the default time stamp?

[HiroshiSatoh]

I think that this is the result so far.

・log:_time<SwipeTime