Hi,
I am trying to make a table that shows the logins outside of business hours, and to show besides if the user had a reason to log in (change request, or incident).
search 1 results in login and failed login events from Windows.
(sourcetype=*:Security ((EventCode=4624 user32 ) OR EventCode=4625) host="*")
| search $t_user$
| eval hour = tonumber(strftime(_time,"%H"))
| eval wknd = tonumber(strftime(_time,"%w"))
| where (hour<=8 or hour>=19) OR (wknd==0 OR wknd==6 )
| table user, _time, src_nt_host, signature
| rename src_nt_host as "Server", signature as "Event details"
Result
user, _time, Server, Event details
john smith 2017-10-12 15:29:44 WIN-BFQE1D An account was successfully logged on
Search 2 results in change requests from a csv
sourcetype="changes"
| search $t_user$
| table changeid, startdate, enddate, changecoordinator
Result
changeid, startdate, enddate, changecoordinator
CR311,2017-10-12 09:30:00,2017-10-12 19:30:00,John Smith
CR312,2017-10-12 22:30:00,2017-10-12 23:00:00,John Smith
The 2 searches have common field user and Change Coordinator, and there can be more changes for 1 user. How can I append the search 2 to search 1 and see the changeids on the same row ?
Final Result:
user, _time, Server, Event details, changeid
john smith,2017-10-12 15:29:44 ,WIN-BFQE1D,An account was successfully logged on,CR311 and CR312
I see there are many commands in splunk to correlate data but I don't know which one would be better for my case.
Thanks!
Like you said, there are LOTS of ways that would be just fine to do this. Here's one...
(sourcetype=*:Security ((EventCode=4624 user32 ) OR EventCode=4625) host="*" user=$t_user$ )
OR (sourcetype="changes" changecoordinator=$t_user$)
| rename COMMENT as "Eliminate all fields we don't need"
| fields _time, user, src_nt_host, signature, changeid, startdate, enddate, changecoordinator,
| rename COMMENT as "Kill logon records that are not off-hours"
| eval hour = tonumber(strftime(_time,"%H"))
| eval wknd = tonumber(strftime(_time,"%w"))
| where (sourcetype="changes") OR (hour<=8 or hour>=19) OR (wknd==0 OR wknd==6 )
Now you have all the data in one file. You can take the user=
and the changecoordinator=
out of the two halves of the search if I've misunderstood the value there, leaving only the token.
It seems like you are looking for the last off-hours logon for each change request. We can sort the records into _time order, and use streamstats to copy down the last logon within an arbitrary window, for instance 8 hours. But first, we need to establish a single field for the user for both kinds of records. In this case, we'll use user
.
| rename COMMENT as "sort records and propagate logon time"
| eval user=coalesce(user,changecoordinator)
| sort 0 user _time
| streamstats time_window=8h last(eval(case(sourcetype!="changes",_time))) as lastLogon
last(eval(case(sourcetype!="changes",signature))) as lastSignature by user
| rename COMMENT as "roll together all change records"
| stats count values(changeid) as changeid by user lastLogon lastSignature
| rename lastLogon as _time
It might be better practice to do the elimination of on-hours records AFTER rolling the records together. That would make the time_window unnecessary, since your changecoordinator would ALWAYS have to have logged on sometime, and we can just kill the on-hours ones all at the same time.
(sourcetype=*:Security ((EventCode=4624 user32 ) OR EventCode=4625) host="*" user=$t_user$ )
OR (sourcetype="changes" changecoordinator=$t_user$)
| rename COMMENT as "Eliminate all fields we don't need"
| fields _time, user, src_nt_host, signature, changeid, startdate, enddate, changecoordinator,
| rename COMMENT as "sort records and propagate logon time"
| eval user=coalesce(user,changecoordinator)
| sort 0 user _time
| streamstats last(eval(case(sourcetype!="changes",_time))) as lastLogon
last(eval(case(sourcetype!="changes",signature))) as lastSignature by user
| rename COMMENT as "roll together all change records"
| stats count values(changeid) as changeid by user lastLogon lastSignature
| rename lastLogon as _time
| rename COMMENT as "Kill logon records that are not off-hours"
| eval hour = tonumber(strftime(_time,"%H"))
| eval wknd = tonumber(strftime(_time,"%w"))
| where (hour<=8 or hour>=19) OR (wknd==0 OR wknd==6 )
Like you said, there are LOTS of ways that would be just fine to do this. Here's one...
(sourcetype=*:Security ((EventCode=4624 user32 ) OR EventCode=4625) host="*" user=$t_user$ )
OR (sourcetype="changes" changecoordinator=$t_user$)
| rename COMMENT as "Eliminate all fields we don't need"
| fields _time, user, src_nt_host, signature, changeid, startdate, enddate, changecoordinator,
| rename COMMENT as "Kill logon records that are not off-hours"
| eval hour = tonumber(strftime(_time,"%H"))
| eval wknd = tonumber(strftime(_time,"%w"))
| where (sourcetype="changes") OR (hour<=8 or hour>=19) OR (wknd==0 OR wknd==6 )
Now you have all the data in one file. You can take the user=
and the changecoordinator=
out of the two halves of the search if I've misunderstood the value there, leaving only the token.
It seems like you are looking for the last off-hours logon for each change request. We can sort the records into _time order, and use streamstats to copy down the last logon within an arbitrary window, for instance 8 hours. But first, we need to establish a single field for the user for both kinds of records. In this case, we'll use user
.
| rename COMMENT as "sort records and propagate logon time"
| eval user=coalesce(user,changecoordinator)
| sort 0 user _time
| streamstats time_window=8h last(eval(case(sourcetype!="changes",_time))) as lastLogon
last(eval(case(sourcetype!="changes",signature))) as lastSignature by user
| rename COMMENT as "roll together all change records"
| stats count values(changeid) as changeid by user lastLogon lastSignature
| rename lastLogon as _time
It might be better practice to do the elimination of on-hours records AFTER rolling the records together. That would make the time_window unnecessary, since your changecoordinator would ALWAYS have to have logged on sometime, and we can just kill the on-hours ones all at the same time.
(sourcetype=*:Security ((EventCode=4624 user32 ) OR EventCode=4625) host="*" user=$t_user$ )
OR (sourcetype="changes" changecoordinator=$t_user$)
| rename COMMENT as "Eliminate all fields we don't need"
| fields _time, user, src_nt_host, signature, changeid, startdate, enddate, changecoordinator,
| rename COMMENT as "sort records and propagate logon time"
| eval user=coalesce(user,changecoordinator)
| sort 0 user _time
| streamstats last(eval(case(sourcetype!="changes",_time))) as lastLogon
last(eval(case(sourcetype!="changes",signature))) as lastSignature by user
| rename COMMENT as "roll together all change records"
| stats count values(changeid) as changeid by user lastLogon lastSignature
| rename lastLogon as _time
| rename COMMENT as "Kill logon records that are not off-hours"
| eval hour = tonumber(strftime(_time,"%H"))
| eval wknd = tonumber(strftime(_time,"%w"))
| where (hour<=8 or hour>=19) OR (wknd==0 OR wknd==6 )