Splunk Dev

Using IF with multiple conditions

LNebout
Path Finder

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

Tags (1)
0 Karma
1 Solution

gvmorley
Contributor

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:

alt text

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:

alt text

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

View solution in original post

DalJeanis
SplunkTrust
SplunkTrust

... you said delete it where it is different, but your example has deleted the one that matched.

Which do you want?

0 Karma

LNebout
Path Finder

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

0 Karma

woodcock
Esteemed Legend

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

LNebout
Path Finder

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

0 Karma

woodcock
Esteemed Legend

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
0 Karma

woodcock
Esteemed Legend

You need to avoid transaction if at all possible.

0 Karma

gvmorley
Contributor

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:

alt text

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:

alt text

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

LNebout
Path Finder

@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

0 Karma

gvmorley
Contributor

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.

0 Karma

LNebout
Path Finder

That's work perfectly !!!!!!!!!!!!!!!!!!!!!
Thanks !!!!!!

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