topic Re: How can I aggregate information into rows based on a transaction ID? in Splunk Search

How can I aggregate information into rows based on a transaction ID?

rpecka — Wed, 18 May 2022 19:51:32 GMT

I have events which will all have an ID, stageID, stageDuration, as well as other information.

In the past I've used `chart sum(stageDuration) over ID by stageID` to create stacked bar charts, but now I’m more interested in the table produced by that command. It has a column for ID, then columns for the durations of each of the stageIDs that had the same ID.

I would like to create a similar table except I would like to include additional columns. For example, I would like to conditionally populate a column with event.startTime from the stage foo if the stageID foo exists for an ID.

I don't need to visualization, just the way of forming the table.

It seems like there might be a way to do this with transactions but I haven't been able to figure it out.

Re: How can I aggregate information into rows based on a transaction ID?

ITWhisperer — Wed, 18 May 2022 20:52:21 GMT

Try something like this

| eval foostart=if(stageID="foo", 'event.startTime',null()) | stats sum(stageDuration) as stageTotalDuration values(foostart) as foostart by ID stageID

Re: How can I aggregate information into rows based on a transaction ID?

rpecka — Wed, 25 May 2022 23:36:09 GMT

This isn't quite it.

When I run the chart command I get this table:

sessionID	stageID1	stageID2	stageID...
id1	99	86	34
id2	33	45	22

I want the same thing except without the chart and with additional columns populated depending on what the stages are

Re: How can I aggregate information into rows based on a transaction ID?

ITWhisperer — Thu, 26 May 2022 06:56:34 GMT

Can you give an example of what you are trying to achieve?

Re: How can I aggregate information into rows based on a transaction ID?

rpecka — Fri, 27 May 2022 21:52:15 GMT

I have events with stageIDs and IDs. I want to create one row for each ID which includes a column for each stageID to indicate whether it has or does not have a corresponding event for that stageID. Then I want to filter for only the rows (IDs) that have stageID=foo present, and then create a pie chart to count the occurrences of each stageID in the rows that remain

Re: How can I aggregate information into rows based on a transaction ID?

ITWhisperer — Fri, 27 May 2022 22:02:56 GMT

| chart count by id stageid | where foo!=0