Forum Discussion

pho30's avatar
pho30
Copper Contributor
Jan 29, 2018

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.

 

azure monitor
Query Language

4 Replies

  • Meir_Mendelovich's avatar
    Meir_Mendelovich
    Icon for Microsoft rankMicrosoft
    Feb 01, 2018

    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

    • pho30's avatar
      pho30
      Copper Contributor
      Feb 03, 2018

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

  • Evgeny Ternovsky's avatar
    Evgeny Ternovsky
    Icon for Microsoft rankMicrosoft
    Jan 29, 2018

    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

     

    • pho30's avatar
      pho30
      Copper Contributor
      Jan 29, 2018

      Exactly what I needed, worked perfectly. Thank you!