How do I edit my search to join multiple search results for user authentication failure counts?

[kpavan]

Hi All,

Am trying to join multiple searches for authentication failures from UserType (i.e, Windows User, Linux User, DATABASE User) which are there in the lookup file which contains user name and usertype. I would like to create chart for authfailure based on usertype. From the search below, I am getting a result count by usertype which is count of usertype present in the lookup file.

But, it's not giving me the result of the user authentication failure count. Could you please help me with the search on how to get the user authentication failure counts only for the lookup file user with usertype?

|inputlookup usertype.csv| join type=outer user [search index="wineventlog" source="wineventlog:security" EventCode="4625" |stats count by user] | append [search index=os source=*sshd.log OR source=*secure* | rex "sshd\W\d+\W:\s(?\w+)\s\w+\s\w+\s(?\w+)\s\w+\s(?\d+.\d+.\d+.\d+)"| stats count by user] | append [search index="dba" source=*dba ACTION_NAME="AuthFail" | rename USERNAME as user |stats count by user ]| chart count by UserType

This search output is > result is no of user per usertype which is wrong
UserType count
DATABASE 9
Linux 1
Windows 3

Need output like actual authfailure count

[Richfez]

For what you've written, I don't know why you need the join. Join is a very expensive and slow operation. You also seem to be doing a lot with the field user, but then summarizing that out of the whole piece with the final chart count by UserType.

Try this:

index="wineventlog" source="wineventlog:security" EventCode="4625" | eval UserType="Windows" |stats count as Failure_Count by UserType
| append [search index=os source=*sshd.log OR source=*secure* | rex "sshd\W\d+\W:\s(?\w+)\s\w+\s\w+\s( ?\w+)\s\w+\s(?\d+.\d+.\d+.\d+)" | eval UserType="Linux" |stats count as Failure_Count by UserType ] 
| append [search index="dba" source=*dba ACTION_NAME="AuthFail" | rename USERNAME as user | eval UserType="Database" |stats count as Failure_Count by UserType ]

I MAY have mistyped something, but that ought to get you the output you wanted (well, with right numbers).

If the numbers aren't right, you should be able to test each piece separately, too, like the middle one is:

index=os source=*sshd.log OR source=*secure* | rex "sshd\W\d+\W:\s(?\w+)\s\w+\s\w+\s( ?\w+)\s\w+\s(?\d+.\d+.\d+.\d+)"| stats count as Failure_Count by user | eval UserType="Linux"

Let us know how that works!