Sum Count of 2 different custom fields

Copper Contributor

Hello,

 

I would like to sum two custom fields I created for failed logins in linux. Each field extracts a user name based on similar events (2 separate events though) and then I want to have a count that sums both those events based off the username into 1 count so I can get a better understanding of the total number of times that account performed the activity.

 

Note: sometimes one of the custom fields may not have data, while the other one does.

The query is as follows (this is looking for failed login attempts in Linux):

Syslog
| where (Facility == 'authpriv' and SyslogMessage has 'sshd:auth' and SyslogMessage has 'authentication failure') or (Facility == 'auth' and ((SyslogMessage has 'Failed' and SyslogMessage has 'ssh2') or SyslogMessage has 'error: PAM: Authentication failure'))
| where TimeGenerated > now(-7d)
| summarize count() by Computer, FailedPasswordUName_CF, PAMUser_CF
| where isnotempty(FailedPasswordUName_CF) == true or isnotempty(PAMUser_CF) == true
 
In the attached image are the results of the basic query. In the boxes in red, I would like to get a sum of the amount of times the username failed logon (10), but as of now, it is splitting it and providing 2 separate counts.
 
Thank you.

 

4 Replies

Sean,

 

Sounds like a great opportunity to use the fairly recently-introduced coalesce() command! Try the following (or click here to open it in our demo environment):

 
datatable (Computer: string, FailedPasswordUName_CF: string, PAMUser_CF: string, count_: int)
[
"AZR02-blurryid", "", "j.doe", 3,
"AZR02-blurryid", "j.doe", "", 7,
"AZR02-blurryid", "", "root", 2208,
"AZR02-blurryid", "root", "", 6540
]
| extend commonUID = coalesce(FailedPasswordUName_CF, PAMUser_CF)
| summarize sum(count_) by commonUID
 
 
Hope that works!
-Evgeny

 

Exactly what I needed, worked perfectly. Thank you!

Hi,

 

Other than the elegant solution that Evgeny proposed, I have few other comments on your query:

  • Try to make the time where condition the first filter in the query. In your query it comes after a very complex filter: | where TimeGenerated > now(-7d)
  •  It is more efficient to filter before the summarize than after the summarize.
  • Other than the new coalesce() function, there is the more flexible condition functions like: iif() and case()

 

Thanks,

Meir

Thanks Meir, ill make the recommended changes and check out the other functions.