How to list all the events from a transaction in a tabular format with specific fields

[xvxt006]

Hi,

we want to output only certain fields from a transaction in a tabular format. For example, we want only uri, status, referrer and req_time. I have tried something like this..but it was not showing status for all the uris, etc.

sourcetype=access_combined_wcookie host="qqqq*" | transaction SessionID | search status="404" uri="*checklogin*" | table req_time, Hybris_SessionID, status, uri

[Ayn]

My advice would be to rewrite the search so that it doesn't use transaction at all. Something like this.

sourcetype=access_combined_wcookie host="qqqq*" | stats list(req_time) as req_time, list(Hybris_SessionID) as Hybris_SessionID, list(status) as status, list(uri) as uri by SessionID | where mvfind(status,"^404$") AND mvfind(uri,"checklogin")

EDIT: So if you don't actually want to have aggregated events after all you could use eventstats.

sourcetype=access_combined_wcookie host="qqqq*" | eventstats list(status) as mvstatus, list(uri) as mvuri by SessionID | where mvfind(mvstatus,"^404$") AND mvfind(mvuri,"checklogin") | table req_time, Hybris_SessionID, status, uri

This will perform statistics that are written to individual events, then filter out the events that do not belong to a session with status 404 and uri checklogin. Original events and their respective original fields are retained though, as is the order, so you might want to sort your results by something so different sessions' results aren't mixed with each other (like for instance throwing a "sort SessionID" in before table).

[Ayn]

Well "SessionID" is what you specified yourself when you showed us the search you were using, so... 😛

If you really meant Hybris_SessionID then yes, it would make no sense to both list and split by it. In that case just remove the list(Hybris_SessionID).

[xvxt006]

Thanks Ayn. The first query you have is not giving any data. You have SessionID which is not a filed in our system. You meant Hybris_SessionID? if i have that, it is complaining that "The output field 'Hybris_SessionID' cannot have the same name as a group-by field."

sourcetype=access_combined_wcookie host="prlws*" | stats list(req_time) as req_time, list(Hybris_SessionID) as Hybris_SessionID, list(status) as status, list(uri) as uri by SessionID where mvfind(status,"^404$") AND mvfind(uri,"checklogin")

[Ayn]

Well as soon as you're running stats or transaction you really remove the concept of the original events. You might want to look into using eventstats instead if you want to filter events but not aggregate anything. Editing my answer with a proposed approach.

[xvxt006]

Thank you Ayn. This is helpful. But we are trying to see all the events along with those when there are failures happend in a cleaner fashion. Any suggestions?