Splunk Search

How to produce stats with based on a field clientId

shashank_24
Path Finder

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.

clientIdSuccessCountFailedAttemptCount
client110050
client225070
client35500450

 

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.

Labels (2)
Tags (3)
0 Karma
1 Solution

niketn
Legend

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

 

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

0 Karma

niketn
Legend

@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).

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

shashank_24
Path Finder

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

 

  

0 Karma

niketn
Legend

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

 

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

shashank_24
Path Finder

@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 🙂

 

0 Karma

to4kawa
Ultra Champion
| 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.

Get Updates on the Splunk Community!

Improve Data Pipelines Using Splunk Data Management

  Register Now   This Tech Talk will explore the pipeline management offerings Edge Processor and Ingest ...

3-2-1 Go! How Fast Can You Debug Microservices with Observability Cloud?

Register Join this Tech Talk to learn how unique features like Service Centric Views, Tag Spotlight, and ...

Thank You for Celebrating CX Day with Splunk!

Yesterday the entire team at Splunk &#43; Cisco joined the global celebration of CX Day - celebrating our ...