How can I make this transaction command and table work when parsing a JSON document?

[jasongb]

I have data that looks like this:

{trans_id:"123abc" class:"cdedt" function:"bbb" marker:"A11111" elapsedms:"178" timestamp="12/18/17 06:01:100"}
{trans_id:"123abc" class:"adedt" function:"aaa" marker:"G11111" elapsedms:"178" timestamp="12/18/17 06:01:278"}
{trans_id:"123abc" class:"bdedt" function:"ddd" marker:"F11111" elapsedms:"295" timestamp="12/18/17 06:01:573"}
{trans_id:"123abc" class:"cdedt" function:"eee" marker:"T11111" elapsedms:"590" timestamp="12/18/17 06:02:163"}

The problem is that the query I write, which uses the transaction command on trans_id, and uses the table command to create a table of class, function, marker and elapsedms, produces data that looks like this:

class---function------marker----------elapsedms
adedt---aaa-----------A11111----------178
bdedt---bbb-----------F11111----------295
cdedt---ddd-----------G11111----------590
--------eee-----------T11111----------

I want the data to look like this:

class---function------marker----------elapsedms
cdedt---bbb-----------A11111----------178
adedt---aaa-----------G11111----------178
bdedt---ddd-----------F11111----------295
cdedt---eee-----------T11111----------590

How can I ensure that each event's JSON attribute/value sets are associated with each other, so that each event is reflected across each row in the resultant table?

[somesoni2 · Accepted solution]

Without seeing the transaction query you use, instead of transaction command (which is expensive anyways), you can try stats alternative of that.

your base search | stats list(class) as class list(function) as function list(marker) as marker list(elapsedms) as elapsedms by trans_id

[jasongb]

Thank you so much for your rapid response. This works!... I was unfamiliar with the list() command.

My original query looked something like this:

source=mySourceType | spath elapsedms | search elapsedms>1000 | transaction trans_id startswith="A11111" endswith="T11111" | table class function marker elapsedms

I read up on the list() command, and noticed that the docs point out that list() can consume a lot of memory. The actual data I'm working with will span upwards of 700 events per transaction, with as many as 90 transactions per minute.

I'm hoping to use the query in a variety of means: short-term (last 60 mins) diagnostic queries to demonstrate 'hotspots' where duration is running high, medium-term (last 7 days) queries for reporting, and then the odd long term (last 30 days, or even 90) for overall trend analysis. Is the use of list() going to make these queries untenable due to memory usage?

Lastly - I love that the results are sorted by time. The result reads in an intuitive fashion from a developer's standpoint. Is there a way to sort each result in a descending fashion by 'elapsedms', so the 'worst offenders' are at the top of the list?

Thanks again, you've saved my bacon!

[somesoni2]

From memory usage perspective, it would be bad but better than transaction command itself. You may see inconsistent results in both commands if the memory usage is very high. Besides, you'd agree that having ~700 entries in a single event (transaction) will not be easily readable, so you should rethink your logic here (increase threshold etc.)

The list commands just list the values in the order in which they appear in Splunk which is reverse chronological order (descending order of time). You can just sort the events before your stats..list command, per your need and your output will be sorted in that order. E.g.

source=mySourceType | spath elapsedms | search elapsedms>1000 | sort 0 trans_id -elapsedms | stats list(class) as class list(function) as function list(marker) as marker list(elapsedms) as elapsedms by trans_id