What do I need to fix with my inputlookup search?

[Mitchellsch]

I'm new in writing searches with a lookup table and need help knowing what's wrong with my logic. Here's my search so far.

index=wineventlog eventcode=4624 | eval hour_of_the_day=strftime(_time, "%H") | where hour_of_the_day >=17 or hour_of_the_day < 6 | eval hour_of_the_day=strftime(_time, "%H") | where hour_of_the_day >=17 or hour_of_the_day < 6 | table _time [| inputlookup domain_admins_lookup | fields lastLogonTimestamp] | table lastLogonTimestamp, displayName

I want to track all the after hours domain admin logins. Please help. Thank.

[javiergn]

Hi,

Keep in mind the following:

  table _time [| inputlookup domain_admins_lookup | fields lastLogonTimestamp] 

Is not an actual search. Table is just displaying the fields in a tabular format.

I don't know what your lookup fields are but in principle you would need to join the content of your lookup with the content of your query using a common field like displayName or user.

You can do this simply by using the lookup command and passing the relevant parameters to return the lastLogonTimestamp for your users:

index=wineventlog eventcode=4624 [| inputlookup domain_admins_lookup | table user]
| fields _time, user, displayName
| eval hour_of_the_day=strftime(_time, "%H") 
| where hour_of_the_day >=17 or hour_of_the_day < 6 
| lookup domain_admins_lookup displayName AS displayName OUTPUT lastLogonTimestamp as lastLogonTimestamp
| table lastLogonTimestamp, displayName

Line 1 is filtering by user names. The subsearch translates to "user=user1 OR user=user2 OR ... OR user=userN".
Line 2 is just to return those fields you are going to use later as this will improve the query performance a lot.

There are alternatives to the above by using the join command with inputlookup, you could use append and stats, you could do multiple things to be honest. If you give us more information about your lookup we might be able to help more.