Find users based on a lookup list showing users that have and have not logged in

[florencegoh]

I have list of lookup list yyyy which I want to shown the latest login based on max login time and also user that did not login.

How to reconstruct the query to allow to show both in one table?

index=main sourcetype=xxxx [inputlookup yyyy |fields account_name|rename account_name as query] |search ACTION_NAME=LOGON RETURNCODE=0| stats max(_time) as login_time by user,source| eval login_time=strftime(login_time,"%Y/%m/%d %H:%M:%S")| table user,source,login_time

[gcusello]

Hi florencegoh
try something like this

index=main sourcetype=xxxx ACTION_NAME=LOGON RETURNCODE=0
| eval user=upper(user)
| stats max(_time) AS last_login count BY user
| append [ | inputlookup yyyy | eval user=upper(account_name), count=0 |fields user count]
| stats values(last_login) AS last_login sum(count) AS Total by user
| eval last_login=if(Total=0,"Never connected",strftime(last_login,"%Y/%m/%d %H:%M:%S"))
| table user last_login

I didn't understand what is source because in your requirements you speak about last login and user.

Bye.
Giuseppe

[florencegoh]

There are some user that has the sames name from different source. Have try the above query but it 's return all user login not based on the inputlookup yyyy list.

Any suggestion ?

[gcusello]

Hi florencegoh,
if you put in your lookup also the source field, you can run the above search using two fields, otherwise you cannot match search results with lookup.

So, having in lookup both username and source try something like this:

index=main sourcetype=xxxx ACTION_NAME=LOGON RETURNCODE=0
| eval user=upper(user), source=upper(source)
| stats max(_time) AS last_login count BY user source
| append [ | inputlookup yyyy | eval user=upper(account_name), count=0 |fields user source count]
| stats values(last_login) AS last_login sum(count) AS Total by user source
| eval last_login=if(Total=0,"Never connected",strftime(last_login,"%Y/%m/%d %H:%M:%S"))
| table user source last_login

Bye.
Giuseppe

[florencegoh]

Hi cusello, the suggested query still does append those user that has not login. Is there any other ways to put the value as 0 or '-' for the last_login and source when user does not login.

[gcusello]

Hi florencegoh,
Maybe there's another way but i usually use this one!
what's the problem using this solution?
This solution completes the search result adding all the users, so the ones without events are highlighted.
I usually use this solution to find missed hosts (device down alert) and trigger an alert.
Bye.
Giuseppe

[HiroshiSatoh]

Try this!

 (your search)| table user,source,login_time
 |append  [|inputlookup yyyy |fields account_name|rename account_name as user
      |eval source="-",login_time=null|table user,source,login_time]
 |stats max(login_time) as login_time,list(source) as source by user
 |mvexpand source
 |where NOT (NOT isnull(login_time) AND source="-")

[florencegoh]

I still getting not the full listing of the user from inputlookup yyyy file. Any other suggestions ?