Splunk Enterprise

Peak hourly count between 2 events for successful logins

shashank_24
Path Finder

Hi, I am stuck at a query problem. So what i need to do is join 2 events and get the hourly stats and peak hour successful login attempts.

The sample events are -

 

2020-09-07 23:59:59,641 trackingid="id:638rdchdfe7vhs" event=AUTHN_ATTEMPT subject="" ip=8.2.8.44 app= clientId=WEB protocol="" role=IdP status=inprogress  attributes="" description="" responsetime=39 messagetype="Request"

2020-09-07 23:59:59,641 trackingid="id:ljsdhff76duhj" event=AUTHN_ATTEMPT subject="" ip=8.2.8.24 app= clientId=MOBAPP protocol="" role=IdP status=inprogress  attributes="" description="" responsetime=39 messagetype="Request" 

2020-09-07 23:59:59,641 trackingid="id:8675hbcdksjdfub" event=AUTHN_ATTEMPT subject="" ip=8.6.8.24 app= clientId=SKYAPP protocol="" role=IdP status=inprogress  attributes="" description="" responsetime=39 messagetype="Request" 

 

as you can see the status right now is "inprogress" and different clientId's (WEB, MOBAPP, SKYAPP) and once customer logs in successfully the below event is logged.

 

2020-09-07 23:59:46,772 tid:638rdchdfe7vhs INFO  [org.class.MediaCredentialValidator] in processPasswordCredential VERIFIED user=test@gmail.com found and success

 

I want to calculate the hourly volume of successful logins from clientId=WEB

The common field in the events is id which i am extracting and what is want is a table with _time and count column on hourly basis.

Query -

 

index=test (sourcetype=splunk_log event=AUTHN_ATTEMPT clientId=web status=inprogress) OR (source="server.log" "In processPasswordCredential" "found and success") 
| rex field=_raw "sessionid\=\"id\:(?<id>[^\"]+)"
| stats count by tid

 

 

Let me know if someone can advice on this.

@richgalloway @gcusello 

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

thambisetty
SplunkTrust
SplunkTrust

s1.txt contains 

 

2020-09-07 23:59:59,641 trackingid="id:638rdchdfe7vhs" event=AUTHN_ATTEMPT subject="" ip=8.2.8.44 app= clientId=WEB protocol="" role=IdP status=inprogress  attributes="" description="" responsetime=39 messagetype="Request"
2020-09-07 23:59:59,641 trackingid="id:ljsdhff76duhj" event=AUTHN_ATTEMPT subject="" ip=8.2.8.24 app= clientId=MOBAPP protocol="" role=IdP status=inprogress  attributes="" description="" responsetime=39 messagetype="Request" 
2020-09-07 23:59:59,641 trackingid="id:8675hbcdksjdfub" event=AUTHN_ATTEMPT subject="" ip=8.6.8.24 app= clientId=SKYAPP protocol="" role=IdP status=inprogress  attributes="" description="" responsetime=39 messagetype="Request" 

 

s2.txt contains 

 

2020-09-07 23:59:46,772 tid:638rdchdfe7vhs INFO  [org.class.MediaCredentialValidator] in processPasswordCredential VERIFIED user=test@gmail.com found and success

 

 

below is the query joins based id found in s1.txt and s2.txt , there is only one match 

 

(source="s2.txt" host="abcd" sourcetype="test1" "In processPasswordCredential" "found and success") OR (source="s1.txt" event=AUTHN_ATTEMPT clientId=web status=inprogress host="abcd" sourcetype="test1")
| rex "tid:(?<id1>[^\s]+)"
| rex "\"id:(?<id2>[^\"]+)"
| eval id=coalesce(id1,id2)
| eventstats dc(source) as dc_source by id
| where dc_source=2
| stats latest(_time) as _time by id
| timechart span=1h count

 

 you will get results like below:

join.png

————————————
If this helps, give a like below.

View solution in original post

shashank_24
Path Finder

@ITWhisperer @thambisetty  I have edited the question and posted some sample events. This may help you to visualise what i need to do. Let me know if you could help.

0 Karma

thambisetty
SplunkTrust
SplunkTrust

assuming the query used from your query is working hence adding extra bit to give table with _time and count column on hourly basis.

index=test (sourcetype=splunk_log event=AUTHN_ATTEMPT clientId=web status=inprogress) OR (source="server.log" "In processPasswordCredential" "found and success") 
| rex field=_raw "sessionid\=\"id\:(?<id>[^\"]+)"
| timechart span=1h count 

 

————————————
If this helps, give a like below.

shashank_24
Path Finder

@thambisetty Well I havr thought of this but this will give me count of 1st event as well which is not right. isn't it?

I only want the count of 2nd event which says "found and success". The reason I am joining 2 events because I am not getting clientId field in the 2nd event and I only want the count for clientId=web

 

0 Karma

thambisetty
SplunkTrust
SplunkTrust

can you take same events and draw expected results.

————————————
If this helps, give a like below.
0 Karma

shashank_24
Path Finder

@thambisetty Hi, I want a table like this -

TimeCount
2020-09-08 17:009878
2020-09-07 14:004567
2020-09-06 12:002345

 

Total successful count with time in hours. Basically how many successful logins are happening in an hour for let's say last 30 days.

0 Karma

thambisetty
SplunkTrust
SplunkTrust

based on which field you would like to join your two searches below, there should be common field between these two searches if you would like to join. simply you cannot join.

sourcetype=splunk_log event=AUTHN_ATTEMPT clientId=web status=inprogress
source="server.log" "In processPasswordCredential" "found and success"

 once you join your searches using common field, you need to use timechart to produce results grouping them by 1 hour span like I Posted earlier as below.

| timechart span=1h count 

 

————————————
If this helps, give a like below.

shashank_24
Path Finder

@thambisetty Thanks for your response but that is the problem. I can't use join command because of the sub-search limitation. I get this error "Subsearch produced 50000 results, truncating to maxout 50000"

So I have to use either stats or transaction command to join the queries.

0 Karma

thambisetty
SplunkTrust
SplunkTrust

s1.txt contains 

 

2020-09-07 23:59:59,641 trackingid="id:638rdchdfe7vhs" event=AUTHN_ATTEMPT subject="" ip=8.2.8.44 app= clientId=WEB protocol="" role=IdP status=inprogress  attributes="" description="" responsetime=39 messagetype="Request"
2020-09-07 23:59:59,641 trackingid="id:ljsdhff76duhj" event=AUTHN_ATTEMPT subject="" ip=8.2.8.24 app= clientId=MOBAPP protocol="" role=IdP status=inprogress  attributes="" description="" responsetime=39 messagetype="Request" 
2020-09-07 23:59:59,641 trackingid="id:8675hbcdksjdfub" event=AUTHN_ATTEMPT subject="" ip=8.6.8.24 app= clientId=SKYAPP protocol="" role=IdP status=inprogress  attributes="" description="" responsetime=39 messagetype="Request" 

 

s2.txt contains 

 

2020-09-07 23:59:46,772 tid:638rdchdfe7vhs INFO  [org.class.MediaCredentialValidator] in processPasswordCredential VERIFIED user=test@gmail.com found and success

 

 

below is the query joins based id found in s1.txt and s2.txt , there is only one match 

 

(source="s2.txt" host="abcd" sourcetype="test1" "In processPasswordCredential" "found and success") OR (source="s1.txt" event=AUTHN_ATTEMPT clientId=web status=inprogress host="abcd" sourcetype="test1")
| rex "tid:(?<id1>[^\s]+)"
| rex "\"id:(?<id2>[^\"]+)"
| eval id=coalesce(id1,id2)
| eventstats dc(source) as dc_source by id
| where dc_source=2
| stats latest(_time) as _time by id
| timechart span=1h count

 

 you will get results like below:

join.png

————————————
If this helps, give a like below.

shashank_24
Path Finder

@thambisetty Thank you. after some modification to the query as per my need, I was able to get the relevant data. I will accept this answer.

Thanks again.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

If you are only interested successful attempts, why are you bothering with the first search or extracting id?

index=test source="server.log" "In processPasswordCredential" "found and success" 
| bin span=1h
| stats count by _time
0 Karma

shashank_24
Path Finder

@ITWhisperer That's because I only need the data for clientId=web. There are other clientId's as well like mobile app and other. I don't want their count. What i want is successful attempts from clientId web. 

And in the 2nd event I don't get clientId field so I have to join 2 events.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Try something like this:

index=test (sourcetype=splunk_log event=AUTHN_ATTEMPT clientId=web status=inprogress) OR (source="server.log" "In processPasswordCredential" "found and success") 
| rex field=_raw "sessionid\=\"id\:(?<id>[^\"]+)"
| eval timeofsuccess=if(like(_raw, "%found and success%"),_time,NULL())
| stats values(clientId) as clientId, values(timeofsuccess) as _time by id
| fillnull value="ignore" clientId, _time
| where clientId != "ignore" AND _time != "ignore"
| bin span=1h _time
| stats count by _time

I am not sure if the fillnull on _time is required

0 Karma

shashank_24
Path Finder

@ITWhisperer It's not working. It is counting the other event as well which is giving me incorrect stats. I only want the count of this events but the other event is just to get the count only from clientId=web.

Really struggling to get the correct stats

(source="server.log" "In processPasswordCredential" "found and success") 

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Try extending the condition to include everything that makes the event you want to count unique. Without sample events it is difficult to suggest what you need but would this work?

| eval timeofsuccess=if(like(_raw, "%In processPasswordCredential%found and success%"),_time,NULL())

 

0 Karma

shashank_24
Path Finder

@ITWhisperer  I have edited the question and posted some sample events. This may help you to visualise what i need to do. Let me know if you could help.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...