Forum Discussion
pho30
Jan 29, 2018Copper Contributor
Sum Count of 2 different custom fields
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.
- Meir_Mendelovich
Microsoft
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
- pho30Copper Contributor
Thanks Meir, ill make the recommended changes and check out the other functions.
- Evgeny Ternovsky
Microsoft
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- pho30Copper Contributor
Exactly what I needed, worked perfectly. Thank you!