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
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:
@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.
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
@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
can you take same events and draw expected results.
@thambisetty Hi, I want a table like this -
Time | Count |
2020-09-08 17:00 | 9878 |
2020-09-07 14:00 | 4567 |
2020-09-06 12:00 | 2345 |
Total successful count with time in hours. Basically how many successful logins are happening in an hour for let's say last 30 days.
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
@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.
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:
@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.
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
@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.
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
@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")
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())
@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.