Hi Splunkers,
The partner of my company send me a new log file with more details.....
i do apologise for the inconvenience。 本当にごめんなさい!!!
On the new log file , I have an event to define the beginning :
LOG IN -- > Mar 1 21:45:41 XDSauth: 1488433541 |ConnectorSession.setNextServiceName |next service name = X
Mar 1 21:45:41 XDSauth: 1488433541 |ServiceHdlr.serviceTerminated |next service = X
LOG OUT --> Mar 1 21:47:05 XDSauth: 1488433625 |ServiceHdlr.serviceTerminated |next service = X
Where X is the user name.
But the problem is : ServiceHdlr.serviceTerminated
I have this event twice.
One after ConnectorSession.setNextServiceName and the next is to define the end of connection.
I tried a new approach :
sourcetype=XDSauth | fieldformat Epoch_Time = strftime(Epoch_Time, "%F %T.%3N")
| rex field=_raw ".*\d+\|.+\|\d+\|(?<fld_key>[^ ]+)\s.*"
| where User="WIN7-007" |table User, Status , Epoch_Time , fld_key
|streamstats count current=t reset_on_change=true by fld_key
Result :
User Status Epoch_Time fld_key count
WIN7-007 ServiceHdlr.serviceTerminated 2017-03-02 14:47:05.000 145c414 1
WIN7-007 ServiceHdlr.serviceTerminated 2017-03-02 14:45:41.000 3a4822 1
WIN7-007 ConnectorSession.setNextServiceName 2017-03-02 14:45:39.000 3a4822 2
If it's possible I would like to obtain this result :
User Status Epoch_Time fld_key count
WIN7-007 ServiceHdlr.serviceTerminated 2017-03-02 14:45:41.000 3a4822 1
WIN7-007 ConnectorSession.setNextServiceName 2017-03-02 14:45:39.000 3a4822 2
Delete the ServiceHdlr.serviceTerminated line where fld_key of ServiceHdlr.serviceTerminated is different than fld_key of ConnectorSession.setNextServiceName
Have a nice day
Laurent
Hi,
Not sure if this is exactly what you're looking for, but you could try using a transaction
for this.
This first bit of code is to simulate your data:
| makeresults
| eval _raw="Mar 1 21:45:39 XDSauth: 1488433539 |ConnectorSession.setNextServiceName |next service name = WIN7-007" | eval fld_key="3a4822"
| append [|makeresults | eval _raw="Mar 1 21:45:41 XDSauth: 1488433541 |ServiceHdlr.serviceTerminated |next service = WIN7-007" | eval fld_key="3a4822"]
| append [|makeresults | eval _raw="Mar 1 21:47:05 XDSauth: 1488433625 |ServiceHdlr.serviceTerminated |next service = WIN7-007" | eval fld_key="145c414"]
| fields - _time
| rex "^(?<org_time>\w{3}\s\d{1,2}\s\d{2}:\d{2}:\d{2})\s\w+:\s(?<epoch_time>\d+)\s\|(?<status>[^|]+)\s\|[^=]+=\s(?<user>.+)$"
Which give us:
Note: Your example logs and subsequent results are slightly different with regards to time. I've used the data where all of the times are different, as I suspect this is more likely to be the case.
As we're going to use transaction, we need a _time
field, so just eval
this with:
| eval _time=epoch_time
We'll also want to sort
the data by this _time
field, so that transaction is consistent with regards to start and end.
| sort - _time
Next, use a transaction where you define the startswith
and endswith
parameters. We also want to keep all of the fields as multi-value fields, so use mvlist=t
. Finally, we're saying that there will only be 2 events in each transaction
and that the user and fld_key will how we do the grouping.
| transaction mvlist=t maxevents=2 startswith=eval(status == "ConnectorSession.setNextServiceName") endswith=eval(status == "ServiceHdlr.serviceTerminated") fld_key user
We only want the transactions. I.e. we don't want that 3rd event which you were looking to get rid of. So:
| where eventcount=2
Finally, we can use stats
to get the results, format the times to be a bit more readable and drop the _time
field as we don't need it:
| stats first(eval(mvsort(epoch_time))) as earliest last(eval(mvsort(epoch_time))) as latest values(duration) as duration values(status) as status by _time,user,fld_key
| eval earliest=strftime(earliest,"%d-%m-%Y %H:%M:%S"), latest=strftime(latest,"%d-%m-%Y %H:%M:%S")
| fields - _time
Which should then look like this:
Again, I'm not 100% sure this is what you're looking for, but it's another approach. Just be careful with the parameters for the transaction
command, to ensure that it fits with your data.
Here's the whole thing, as it may be easier to copy and paste:
| makeresults
| eval _raw="Mar 1 21:45:39 XDSauth: 1488433539 |ConnectorSession.setNextServiceName |next service name = WIN7-007"
| eval fld_key="3a4822"
| append
[| makeresults
| eval _raw="Mar 1 21:45:41 XDSauth: 1488433541 |ServiceHdlr.serviceTerminated |next service = WIN7-007"
| eval fld_key="3a4822"]
| append
[| makeresults
| eval _raw="Mar 1 21:47:05 XDSauth: 1488433625 |ServiceHdlr.serviceTerminated |next service = WIN7-007"
| eval fld_key="145c414"]
| fields - _time
| rex "^(?<org_time>\w{3}\s\d{1,2}\s\d{2}:\d{2}:\d{2})\s\w+:\s(?<epoch_time>\d+)\s\|(?<status>[^|]+)\s\|[^=]+=\s(?<user>.+)$"
| eval _time=epoch_time
| sort - _time
| transaction mvlist=t maxevents=2 startswith=eval(status == "ConnectorSession.setNextServiceName") endswith=eval(status == "ServiceHdlr.serviceTerminated") fld_key user
| where eventcount=2
| stats first(eval(mvsort(epoch_time))) as earliest last(eval(mvsort(epoch_time))) as latest values(duration) as duration values(status) as status by _time,user,fld_key
| eval earliest=strftime(earliest,"%d-%m-%Y %H:%M:%S"), latest=strftime(latest,"%d-%m-%Y %H:%M:%S")
| fields - _time
... you said delete it where it is different, but your example has deleted the one that matched.
Which do you want?
Hi @DalJeanis
I made a mistake. I send a message but it didn't appear.
The last solution sned by @gvmorley works perfect.
Best regards
Laurent
Just add this to your base search to throw away any "singletons":
| eventstats count AS count_for_this_fld_key BY user fld_key | search count_for_this_fld_key>1 | fields - count_for_this_fld_key
Thanks for your message but i made a mistake on my question......
I have this :
Line 1 --> WIN7-007 ServiceHdlr.serviceTerminated 2017-03-02 14:47:05.000 145c414 1
Line 2 --> WIN7-007 ServiceHdlr.serviceTerminated 2017-03-02 14:45:41.000 3a4822 1
Line 3 --> WIN7-007 ConnectorSession.setNextServiceName 2017-03-02 14:45:39.000 3a4822 2
I want to delete
Line 2 -->WIN7-007 ServiceHdlr.serviceTerminated 2017-03-02 14:45:41.000 3a4822 1
And obtain the result :
Line 1 --> WIN7-007 ServiceHdlr.serviceTerminated 2017-03-02 14:47:05.000 145c414 1
Line 3 --> WIN7-007 ConnectorSession.setNextServiceName 2017-03-02 14:45:39.000 3a4822 2
Sorry @woodcock
Laurent
OK, then this:
| eventstats count AS count_for_this_fld_key BY user fld_key | search count_for_this_fld_key=1 OR ConnectorSession | fields - count_for_this_fld_key
You need to avoid transaction
if at all possible.
Hi,
Not sure if this is exactly what you're looking for, but you could try using a transaction
for this.
This first bit of code is to simulate your data:
| makeresults
| eval _raw="Mar 1 21:45:39 XDSauth: 1488433539 |ConnectorSession.setNextServiceName |next service name = WIN7-007" | eval fld_key="3a4822"
| append [|makeresults | eval _raw="Mar 1 21:45:41 XDSauth: 1488433541 |ServiceHdlr.serviceTerminated |next service = WIN7-007" | eval fld_key="3a4822"]
| append [|makeresults | eval _raw="Mar 1 21:47:05 XDSauth: 1488433625 |ServiceHdlr.serviceTerminated |next service = WIN7-007" | eval fld_key="145c414"]
| fields - _time
| rex "^(?<org_time>\w{3}\s\d{1,2}\s\d{2}:\d{2}:\d{2})\s\w+:\s(?<epoch_time>\d+)\s\|(?<status>[^|]+)\s\|[^=]+=\s(?<user>.+)$"
Which give us:
Note: Your example logs and subsequent results are slightly different with regards to time. I've used the data where all of the times are different, as I suspect this is more likely to be the case.
As we're going to use transaction, we need a _time
field, so just eval
this with:
| eval _time=epoch_time
We'll also want to sort
the data by this _time
field, so that transaction is consistent with regards to start and end.
| sort - _time
Next, use a transaction where you define the startswith
and endswith
parameters. We also want to keep all of the fields as multi-value fields, so use mvlist=t
. Finally, we're saying that there will only be 2 events in each transaction
and that the user and fld_key will how we do the grouping.
| transaction mvlist=t maxevents=2 startswith=eval(status == "ConnectorSession.setNextServiceName") endswith=eval(status == "ServiceHdlr.serviceTerminated") fld_key user
We only want the transactions. I.e. we don't want that 3rd event which you were looking to get rid of. So:
| where eventcount=2
Finally, we can use stats
to get the results, format the times to be a bit more readable and drop the _time
field as we don't need it:
| stats first(eval(mvsort(epoch_time))) as earliest last(eval(mvsort(epoch_time))) as latest values(duration) as duration values(status) as status by _time,user,fld_key
| eval earliest=strftime(earliest,"%d-%m-%Y %H:%M:%S"), latest=strftime(latest,"%d-%m-%Y %H:%M:%S")
| fields - _time
Which should then look like this:
Again, I'm not 100% sure this is what you're looking for, but it's another approach. Just be careful with the parameters for the transaction
command, to ensure that it fits with your data.
Here's the whole thing, as it may be easier to copy and paste:
| makeresults
| eval _raw="Mar 1 21:45:39 XDSauth: 1488433539 |ConnectorSession.setNextServiceName |next service name = WIN7-007"
| eval fld_key="3a4822"
| append
[| makeresults
| eval _raw="Mar 1 21:45:41 XDSauth: 1488433541 |ServiceHdlr.serviceTerminated |next service = WIN7-007"
| eval fld_key="3a4822"]
| append
[| makeresults
| eval _raw="Mar 1 21:47:05 XDSauth: 1488433625 |ServiceHdlr.serviceTerminated |next service = WIN7-007"
| eval fld_key="145c414"]
| fields - _time
| rex "^(?<org_time>\w{3}\s\d{1,2}\s\d{2}:\d{2}:\d{2})\s\w+:\s(?<epoch_time>\d+)\s\|(?<status>[^|]+)\s\|[^=]+=\s(?<user>.+)$"
| eval _time=epoch_time
| sort - _time
| transaction mvlist=t maxevents=2 startswith=eval(status == "ConnectorSession.setNextServiceName") endswith=eval(status == "ServiceHdlr.serviceTerminated") fld_key user
| where eventcount=2
| stats first(eval(mvsort(epoch_time))) as earliest last(eval(mvsort(epoch_time))) as latest values(duration) as duration values(status) as status by _time,user,fld_key
| eval earliest=strftime(earliest,"%d-%m-%Y %H:%M:%S"), latest=strftime(latest,"%d-%m-%Y %H:%M:%S")
| fields - _time
@gvmorley
Thanks for your message and solution.
I'm so sorry , i made a mistake on my question.
Mistake :
Delete the ServiceHdlr.serviceTerminated line where fld_key of ServiceHdlr.serviceTerminated is different than fld_key of ConnectorSession.setNextServiceName
Good question :
Delete the ServiceHdlr.serviceTerminated line where fld_key of ServiceHdlr.serviceTerminated is same than fld_key of ConnectorSession.setNextServiceName
I'M sorry @gvmorley
Laurent
Hi,
You could try:
| makeresults
| eval _raw="Mar 1 21:45:39 XDSauth: 1488433539 |ConnectorSession.setNextServiceName |next service name = WIN7-007"
| eval fld_key="3a4822"
| append
[| makeresults
| eval _raw="Mar 1 21:45:41 XDSauth: 1488433541 |ServiceHdlr.serviceTerminated |next service = WIN7-007"
| eval fld_key="3a4822"]
| append
[| makeresults
| eval _raw="Mar 1 21:47:05 XDSauth: 1488433625 |ServiceHdlr.serviceTerminated |next service = WIN7-007"
| eval fld_key="145c414"]
| fields - _time
| rex "^(?<orig_time>\w{3}\s\d{1,2}\s\d{2}:\d{2}:\d{2})\s\w+:\s(?<epoch_time>\d+)\s\|(?<status>[^|]+)\s\|[^=]+=\s(?<user>.+)$"
| eval _time=epoch_time
| sort + _time
| eval user_key=user."-".fld_key
| dedup user_key
| sort - _time
| table user status epoch_time fld_key
| eval epoch_time=strftime(epoch_time,"%d-%m-%Y %H:%M:%S")
It will keep the earliest event where fld_key and user are the same. It's hard to tell without seeing the data, but if this is always the "ConnectorSession" event, then you should be OK.
You'll definitely need to test this against a much larger set of data, to make sure that the assumptions are correct.
That's work perfectly !!!!!!!!!!!!!!!!!!!!!
Thanks !!!!!!