How to produce stats with based on a field clientId

[shashank_24]

Hi, I am stuck at a query problem. So what i need to do is join some events and get the result and for that I am using stats. I can't use join because of the sub-search limitation. Below is my query.

The common field in the events is id which i am extracting but what I want to do is produce a table based on clientId (like below) but the current query does not give what i require.

clientId	SuccessCount	FailedAttemptCount
client1	100	50
client2	250	70
client3	5500	450

The problem is my one event contains clientId (sourcetype=splunk_audit_log event=AUTHN_ATTEMPT clientId=* status=inprogress) and another event contain whether that client was successful during login attempt (source="server.log" "In processCredentials" )

The query which i created is -

index=test (sourcetype=splunk_log event=AUTHN_ATTEMPT clientId=* status=inprogress) OR (source="server.log" "In processPasswordCredential" NOT "not found!")
| rex field=_raw "user\=\[\d+\] (?<raw_status>.*)" 
| rex field=_raw "sessionid\=\"id\:(?<id>[^\"]+)"
| eval status_new=case(raw_status="found and success","Success", raw_status="found but failed","Fail")
| stats list(status_new) as aa_status by id 
| eval NumberOfSuccess=mvfilter(match(aa_status, "Success"))
| eval NumberOfFail=mvfilter(match(aa_status, "Fail"))
| eval SuccessCount = mvcount(NumberOfSuccess)
| eval FailedAttemptCount = mvcount(NumberOfFail)
| fields - NumberOfSuccess NumberOfFail count aa_status

let me know if someone can help please. Appreciate your support.

[niketn]

@shashank_24 for the community to assist you better you may have to provide some more details like mock/anonymized sample data from both sourcetype. Also what is the output of current query or till what point is your query working?

Based on the details provided your query should work, however following would have been better way to write the SPL

index=test (sourcetype=splunk_log event=AUTHN_ATTEMPT clientId=* status=inprogress) OR (source="server.log" AND "In processPasswordCredential" AND ("found but failed" OR "found and success"))
| rex field=_raw "sessionid\=\"id\:(?<id>[^\"]+)"
| eval status_new=case(searchmatch("found and success"),"Success", searchmatch("found but failed"),"Fail")
| stats count(eval(status_new=="Success")) as SuccessCount count(eval(status_new=="Fail")) as FailedAttemptCount by id

If above query or your SPL is not working at present please provide more details. Is the sessionid field present in both the sourcetypes? If not how can the id be extracted from server.log source? Is it automatically extracted? (as you have provided no details in your question and SPL does not include that extraction).

[shashank_24]

Hello @niketn, Thank you for the quick response. My query works perfectly fine when i split by using sessionid but what i want is to split by using clientId (consumer). There are multiple clientId's so what i want to know the count of failed attempt and count of success attempt from a particular clientId like a table mentioned in the question.

Also the sessionIs is present in both the events and getting extracted successfully and i am able to get the data by sessionId but my requirement is different.

These are the chain of events - User attempts and then we get the response whether it's successful or not. You can see the clientId field in first event but it's not there in 2nd and I want to split by that.
The only common field is sessionId

2020-08-09 23:59:56,455 sessionid:jhdjbmclsodhe3865dfd event=AUTHN_ATTEMPT ip=198.10.973.146 app= clientId=myApp1 status=inprogress adapterid=temp attributes="" description="" nmessagetype="Request" 

2020-08-09 15:10:57,674 sessionid:jhdjbmclsodhe3865dfd INFO "In processCredentials" VERIFIED user=temp@gmail.com userid=[1111111] found and success

[niketn · Accepted solution]

Based on sample data provided add the following to your existing query 

<yourCurrentSearch>
| rex field=_raw "sessionid\:(?<sessionid>[^\s]+)"
| stats values(clientId) as clientId count(eval(searchmatch("found and success"))) as SuccessCount count(eval(searchmatch("found but failed"))) as FailedAttemptCount by sessionid
| stats sum(SuccessCount) as SuccessCount sum(FailedAttemptCount) as FailedAttemptCount by clientId

Following is a run anywhere search which uses data provided by you. (commands till | KV generate the sample data). I have changed sessionid regular expression extraction as per your sample data. You may not need that change if data session id has double quotes for your actual data.

| makeresults 
| eval _raw="2020-08-09 23:59:56,455 sessionid:abc123 event=AUTHN_ATTEMPT ip=10.10.10.10 app=blah clientId=myApp1 status=inprogress adapterid=temp attributes=\"\" description=\"\" nmessagetype=\"Request\""
| append [| makeresults
| eval _raw="2020-08-09 15:10:57,674 sessionid:abc123 INFO \"In processCredentials\" VERIFIED user=temp@gmail.com userid= 
    [ 1111111] found and success"]
| KV
| rex field=_raw "sessionid\:(?<sessionid>[^\s]+)"
| stats values(clientId) as clientId count(eval(searchmatch("found and success"))) as SuccessCount count(eval(searchmatch("found but failed"))) as FailedAttemptCount by sessionid
| stats sum(SuccessCount) as SuccessCount sum(FailedAttemptCount) as FailedAttemptCount by clientId

[shashank_24]

@niketn @to4kawa Thank you both for your help and response. I have tried using @niketn query and it satisfies my requirement. I am able to split the data by using clientID and it gives me the sum of success and failed login attempts by clientId.

Thanks again both of you. You guys are stars 🙂

[to4kawa]

| makeresults 
| eval _raw="
2020-08-09 23:59:56,455 sessionid:jhdjbmclsodhe3865dfd event=AUTHN_ATTEMPT ip=198.10.97.146 app= clientId=myApp1 status=inprogress adapterid=temp attributes=\"\" description=\"\" nmessagetype=\"Request\" 
2020-08-09 23:58:56,455 sessionid:jhdjbmclsodhe3865df3 event=AUTHN_ATTEMPT ip=198.10.97.147 app= clientId=myApp2 status=inprogress adapterid=temp attributes=\"\" description=\"\" nmessagetype=\"Request\" 
2020-08-09 15:10:57,674 sessionid:jhdjbmclsodhe3865dfd INFO \"In processCredentials\" VERIFIED user=temp@gmail.com userid=[1111111] found and success
2020-08-09 15:10:57,674 sessionid:jhdjbmclsodhe3865df3 INFO \"In processCredentials\" VERIFIED user=temp1@gmail.com userid=[1111112] found but failed" 
| multikv noheader=t 
| fields _raw 
| eval _time=strptime(substr(_raw,1,23),"%F %T,%3Q") 
| rename COMMENT as "this is your sample. from here, the logic" 
| extract pairdelim=" " kvdelim=":=" 
| eval status_new=case(searchmatch("found and success"),"Success", searchmatch("found but failed"),"Fail") 
| stats values(clientId) as clientId count(eval(status_new="Success")) as Success count(eval(status_new="Fail")) as Fail by sessionid 
| fields - sessionid

When the log displays, it's easy to make the query.