Not able to get response queries with special characters in If statement

[dharmeshbhavsar]

Hi,

I am trying to get response time between events using below query but for some reason i am not being returned any results. I assume it could be due to special characters in If statement. Can you take a look and let me know what is wrong?

index=* OR index=_* sourcetype=xxx.log | search interfaceName="xx" | eval Start_Time=if(message="START of receiving message - source = xxx , messageType = xx , correlationId = %correlationId%",_time,null()) | eval Finish_Time=if(message="END of receiving message (success) - source = xxx , messageType = xx , correlationId = %correlationId%",_time,null()) | transaction Rspns startswith="START of receiving message - source = xxx , messageType = xx , correlationId = %correlationId%" endswith="END of receiving message (success) - source = xxx , messageType = xx , correlationId = %correlationId%"| eval Response_Time=Finish_Time-Start_Time | stats values(interfaceName) as InterfaceName, avg(Response_Time) as Response by xxxx

[cmerriman]

When you break the syntax apart, where do you notice the problem? Does Start_Time and Finish_Time produce valid values?
The problem, I'm going to assume, is with the transaction and then the subsequent Response_Time calculation. If you run the transaction, does it work? How many Start_Time and Finish_Time values are in each transaction?
have you tried to use streamstats instead of transaction? perhaps something like

 index=* OR index=_* sourcetype=xxx.log | search interfaceName="xx" |table _time Rspns interfaceName message xxxxx|sort 0 Rspns _time|streamstats values(message) as prev_message values(_time) as prev_time by Rspns|eval response_time=if(message="END of receiving message (success) - source = xxx , messageType = xx , correlationId = %correlationId%" AND prev_message="START of receiving message - source = xxx , messageType = xx , correlationId = %correlationId%",_time-prev_time,null())|where isnotnull(response_time)|stats values(interfaceName) as InterfaceName, avg(response_time) as Response by xxxx

[dharmeshbhavsar]

I have similar queries working with other interfaceName and messages that do not have special characters in them so I am sure the query is working. I am able to search based on both the messages as well if i remove the transaction and response_time calculation but as a whole this fails for me.
Also, i am new to Splunk so not sure how to run a transaction or used streamstats.

[dharmeshbhavsar]

I changed the values of startswith and endswith to "START of receiving" and "END of receiving" respectively and it worked. Looks like it had an extra space or some other issue.

[cmerriman]

docs on transaction:
https://docs.splunk.com/Documentation/SplunkCloud/6.6.3/SearchReference/Transaction
docs on streamstats:
http://docs.splunk.com/Documentation/SplunkCloud/6.6.3/SearchReference/Streamstats

In my opinon, streamstats works better in a majority of cases, but it depends on what you're trying to do. in your transaction, try something like startswith=eval(message="START of receiving message - source = xxx , messageType = xx , correlationId = %correlationId%") endswith=eval(message="END of receiving message (success) - source = xxx , messageType = xx , correlationId = %correlationId%") where you put the message into an eval statement for startswith and endswith to see if that helps.