Home

Sum Count of 2 different custom fields

%3CLINGO-SUB%20id%3D%22lingo-sub-149951%22%20slang%3D%22en-US%22%3ESum%20Count%20of%202%20different%20custom%20fields%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-149951%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20would%20like%20to%20sum%20two%20custom%20fields%20I%20created%20for%20failed%20logins%20in%20linux.%20Each%20field%20extracts%20a%20user%20name%20based%20on%20similar%20events%20(2%20separate%20events%20though)%20and%20then%20I%20want%20to%20have%20a%20count%20that%20sums%20both%20those%20events%20based%20off%20the%20username%20into%201%20count%20so%20I%20can%20get%20a%20better%20understanding%20of%20the%20total%20number%20of%20times%20that%20account%20performed%20the%20activity.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENote%3A%20sometimes%20one%20of%20the%20custom%20fields%20may%20not%20have%20data%2C%20while%20the%20other%20one%20does.%3C%2FP%3E%0A%3CP%3EThe%20query%20is%20as%20follows%20(this%20is%20looking%20for%20failed%20login%20attempts%20in%20Linux)%3A%3C%2FP%3E%0A%3CDIV%3E%0A%3CDIV%3E%3CSPAN%3ESyslog%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%20%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3EFacility%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E'authpriv'%3C%2FSPAN%3E%20%3CSPAN%3Eand%3C%2FSPAN%3E%3CSPAN%3E%20SyslogMessage%20%3C%2FSPAN%3E%3CSPAN%3Ehas%3C%2FSPAN%3E%20%3CSPAN%3E'sshd%3Aauth'%3C%2FSPAN%3E%20%3CSPAN%3Eand%3C%2FSPAN%3E%3CSPAN%3E%20SyslogMessage%20%3C%2FSPAN%3E%3CSPAN%3Ehas%3C%2FSPAN%3E%20%3CSPAN%3E'authentication%20failure'%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%20%3CSPAN%3Eor%3C%2FSPAN%3E%20%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3EFacility%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E'auth'%3C%2FSPAN%3E%20%3CSPAN%3Eand%3C%2FSPAN%3E%20%3CSPAN%3E((%3C%2FSPAN%3E%3CSPAN%3ESyslogMessage%20%3C%2FSPAN%3E%3CSPAN%3Ehas%3C%2FSPAN%3E%20%3CSPAN%3E'Failed'%3C%2FSPAN%3E%20%3CSPAN%3Eand%3C%2FSPAN%3E%3CSPAN%3E%20SyslogMessage%20%3C%2FSPAN%3E%3CSPAN%3Ehas%3C%2FSPAN%3E%20%3CSPAN%3E'ssh2'%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%20%3CSPAN%3Eor%3C%2FSPAN%3E%3CSPAN%3E%20SyslogMessage%20%3C%2FSPAN%3E%3CSPAN%3Ehas%3C%2FSPAN%3E%20%3CSPAN%3E'error%3A%20PAM%3A%20Authentication%20failure'%3C%2FSPAN%3E%3CSPAN%3E))%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20TimeGenerated%20%26gt%3B%20now%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3E-%3C%2FSPAN%3E%3CSPAN%3E7%3C%2FSPAN%3E%3CSPAN%3Ed%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%20%3CSPAN%3Ecount%3C%2FSPAN%3E%3CSPAN%3E()%3C%2FSPAN%3E%20%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%20Computer%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%20FailedPasswordUName_CF%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%20PAMUser_CF%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20isnotempty%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3EFailedPasswordUName_CF%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3CSPAN%3E%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3Etrue%3C%2FSPAN%3E%20%3CSPAN%3Eor%3C%2FSPAN%3E%3CSPAN%3E%20isnotempty%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3EPAMUser_CF%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3CSPAN%3E%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3Etrue%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3EIn%20the%20attached%20image%20are%20the%20results%20of%20the%20basic%20query.%20In%20the%20boxes%20in%20red%2C%20I%20would%20like%20to%20get%20a%20sum%20of%20the%20amount%20of%20times%20the%20username%20failed%20logon%20(10)%2C%20but%20as%20of%20now%2C%20it%20is%20splitting%20it%20and%20providing%202%20separate%20counts.%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3EThank%20you.%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-149951%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EQuery%20Language%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-152136%22%20slang%3D%22en-US%22%3ERe%3A%20Sum%20Count%20of%202%20different%20custom%20fields%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-152136%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20Meir%2C%20ill%20make%20the%20recommended%20changes%20and%20check%20out%20the%20other%20functions.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-151315%22%20slang%3D%22en-US%22%3ERe%3A%20Sum%20Count%20of%202%20different%20custom%20fields%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-151315%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOther%20than%20the%20elegant%20solution%20that%20Evgeny%20proposed%2C%20I%20have%20few%20other%20comments%20on%20your%20query%3A%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3ETry%20to%20make%20the%20time%20where%20condition%20the%20first%20filter%20in%20the%20query.%20In%20your%20query%20it%20comes%20after%20a%20very%20complex%20filter%3A%26nbsp%3B%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20TimeGenerated%20%26gt%3B%20now%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3E-%3C%2FSPAN%3E%3CSPAN%3E7%3C%2FSPAN%3E%3CSPAN%3Ed%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FLI%3E%0A%3CLI%3E%26nbsp%3BIt%20is%20more%20efficient%20to%20filter%20before%20the%20summarize%20than%20after%20the%20summarize.%3C%2FLI%3E%0A%3CLI%3EOther%20than%20the%20new%26nbsp%3B%3CSPAN%3Ecoalesce()%3C%2FSPAN%3E%20function%2C%20there%20is%20the%20more%20flexible%20condition%20functions%20like%3A%20%3CA%20href%3D%22https%3A%2F%2Fdocs.loganalytics.io%2Fdocs%2FLanguage-Reference%2FScalar-functions%2Fiif()%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Eiif()%3C%2FA%3E%20and%20%3CA%20href%3D%22https%3A%2F%2Fdocs.loganalytics.io%2Fdocs%2FLanguage-Reference%2FScalar-functions%2Fcase()%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ecase()%3C%2FA%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThanks%2C%3C%2FP%3E%0A%3CP%3EMeir%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-150084%22%20slang%3D%22en-US%22%3ERe%3A%20Sum%20Count%20of%202%20different%20custom%20fields%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-150084%22%20slang%3D%22en-US%22%3E%3CP%3EExactly%20what%20I%20needed%2C%20worked%20perfectly.%20Thank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-150080%22%20slang%3D%22en-US%22%3ERe%3A%20Sum%20Count%20of%202%20different%20custom%20fields%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-150080%22%20slang%3D%22en-US%22%3E%3CP%3ESean%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESounds%20like%20a%20great%20opportunity%20to%20use%20the%20fairly%20recently-introduced%20%3CA%20href%3D%22https%3A%2F%2Fdocs.loganalytics.io%2Fdocs%2FLanguage-Reference%2FScalar-functions%2Fcoalesce()%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ecoalesce()%3C%2FA%3E%20command!%20Try%20the%20following%20(or%20click%20%3CA%20href%3D%22https%3A%2F%2Fportal.loganalytics.io%2FDemo%3Fq%3DH4sIAAAAAAAAA3WQTQvCMAyG74P9h7DTBlPG%252FETwIIrgQRnCLopIZ4NU1lbaDp34421RUcElhL40T1%252FSUGJsFiVCOJX8XBlUI9BGMXGMYU5YiTQjWl%252BkovmKcNxP559%252BNlnmGtXP3UFWwuxHwISJfG%252Fre2AjmGzWSdoqykqpmtEghsDVqU0lWtGJG7E34vhBM%252BZKSWnsmabJsBl8Qe5Bv9dNfG%252Fne3fAq0FB7eycS5EvZjC2mpSoDxj%252BX8L35yNwHrrinCh2Q6fC5xoiKOqP6wM%252BJOaWbAEAAA%253D%253D%26amp%3Btimespan%3DP1D%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehere%3C%2FA%3E%20to%20open%20it%20in%20our%20demo%20environment)%3A%3C%2FP%3E%0A%3CDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CPRE%3Edatatable%20(Computer%3A%20string%2C%20FailedPasswordUName_CF%3A%20string%2C%20PAMUser_CF%3A%20string%2C%20count_%3A%20int)%0A%5B%0A%22AZR02-blurryid%22%2C%20%22%22%2C%20%22j.doe%22%2C%203%2C%0A%22AZR02-blurryid%22%2C%20%22j.doe%22%2C%20%22%22%2C%207%2C%0A%22AZR02-blurryid%22%2C%20%22%22%2C%20%22root%22%2C%202208%2C%0A%22AZR02-blurryid%22%2C%20%22root%22%2C%20%22%22%2C%206540%0A%5D%0A%7C%20extend%20commonUID%20%3D%20coalesce(FailedPasswordUName_CF%2C%20PAMUser_CF)%0A%7C%20summarize%20sum(count_)%20by%20commonUID%3C%2FPRE%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3EHope%20that%20works!%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3E-Evgeny%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Sean Hecht
Occasional 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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies