Creating a count for events that happend in a time period

[andres91302]

Hello you guys! Im new to splunk and I have a BIG question, thanks in advance to everyone who is willing to take on this challenge. My data: events that contain only two fields: 1) ID_CLIENT and a field named OP_CODE this last one contains numbers that represent where in a webpage a custumer is at the moment. F.I: The number 34 represents "candy products" and the number 18 represents "stuffed animals" what I want to do: I want to be able to count how many times an ID_CLIENT goes from OP_CPDE=34 to OP_CPDE=18 in a day, or last hour ect... IF YOU CAN HELP ME ABOUT WITH THIS I WILL BE FOREVER THANKFUL

[manjunathmeti]

hi @andres91302,
You are welcome. I am glad that my answers are helping you.
You are almost there, use either search (with AND/OR operators) or eval expressions for startswith and endswith options. Put search expressions in quotes if you are filtering on multiple fields.

index=index sourcetype=sourcetype earliest=-24h
| transaction ID_CLIENT startswith="ACTION=3 RESPONDE=1" endswith="ACTION=5 RESPONDE=0" 
| stats count by ID_CLIENT

This is same as the below query,

index=index sourcetype=sourcetype earliest=-24h
| transaction ID_CLIENT startswith="ACTION=3 AND RESPONDE=1" endswith="ACTION=5 AND RESPONDE=0"
| stats count by ID_CLIENT

[andres91302]

You did it again! 10/10 thank you so so so much

[scelikok]

Hi @andres91302,

Please try below;

| sort 0 ID_CLIENT - _time 
| autoregress ID_CLIENT p=1 
| autoregress OP_CODE p=1 
| where ID_CLIENT=ID_CLIENT_p1 AND OP_CODE="18" AND OP_CODE_p1="34" 
| timechart count cont=f

[andres91302]

Thank you so much for your help my friend, I'll see if this works out for me

[manjunathmeti · Accepted solution]

hi @andres91302,

You can use the transaction command. The below query runs for the last 24 hours and you can change it.

index=index sourcetype=sourcetype earliest=-24h
| transaction ID_CLIENT startswith="OP_CPDE=34" endswith="OP_CPDE=18"
| stats count by ID_CLIENT

 Check this link for more info on this kind of query: https://docs.splunk.com/Documentation/Splunk/8.1.2/SearchReference/Transaction#6._Transactions_with_...

If this reply helps you, an upvote/like would be appreciated.

[andres91302]

@manjunathmeti  and @scelikok 

I am sorry to be bothering you but... you are the guys that have helped me the most here and I am trying to solve this issue and I hope that you can please help me

Im trying for so many days to usa the values stored in a field as  values to search for in anoter subset of a multi search without any luck, I hope I am making myself understood.

What I want to do:

1) store the IDS from the first search and saved them in a field named START
2) use all the IDS I have in the field START to run another search which requires the  field id_user

what Im doing:

| multisearch

[|search index="medi" AND bloodp="high" AND id_user=* AND facility=5
| eval START=id_user]

[|search index="medi" AND bloodp="high" AND id_user=START AND facility=6 AND trx=*
| eval treatmentchose=trx]

I cannot seem to be using the ids in facility 5 to search for the medication that was giving to the patient in facilty 6 by using the IDS that I stored in the field START.


I hope that you can help me ut with this

[manjunathmeti]

hi @andres91302,

You can put the first search as a sub-search in the second search query.

index="medi" AND bloodp="high" AND [search index="medi" AND bloodp="high" AND id_user=* AND facility=5 | fields id_user] AND facility=6 AND trx=*
| eval treatmentchose=trx

If this reply helps you, an upvote/like would be appreciated.

[andres91302]

Dear manjunathmeti,

You are one talented Splunker! Thank you so much your code helped me a lot, but now I just would like to apply this code to another scenario where things are similar but rather than fields I have events...I tried to look this up on the link you gave me about the transaction function but I could not find an answer.

I have an unstructed database where the events have FIELDS: action, response, code, ID
Now I want to count all IDS that in the last 24hs when from haven an event 
--- that started with fields: ACTION=3, RESPONDE=1     
----and ended with fields ACTION=5, RESPONDE=0

What Im doing:

Im trying to use the code:
  

index=index sourcetype=sourcetype earliest=-24h
| transaction ID_CLIENT startswith=(ACTION=3, RESPONDE=1) endswith=(ACTION=3, RESPONDE=1) 
| stats count by ID_CLIENT

  but is not working... I will be so thankful if you could help me please! THANK YOU SO SO SO SO MUCH in advance

[manjunathmeti]

hi @andres91302,

Ends with condition is ACTION=5, RESPONDE=0 but in query you provided endswith=(ACTION=3, RESPONDE=1) . Change this to endswith=(ACTION=5, RESPONDE=0) .

And also you are saying the events have these fields action, response, code, ID. You need to provide the exact field names in startwith and endswith as field names are case sensitive in this case.