topic Re: Combine multiple events into a single row in Splunk Dev

Combine multiple events into a single row

jeevankumarg — Wed, 17 Oct 2018 07:55:45 GMT

Hello All,

Please could you help me with this. We are planning to setup our environment like a file transfer information will be logged at each stage. Usually any file transfer will have multiple stages in our environment and transfer can fail at any stage. If it fails, further or next stages wont occur and their events wont get generated. so I want to display each transfer status in a single line like source details, file name, filesize, transfer start time, transfer end time, target details, target server, path etc. this can only be acquired from multiple events. Please advise how can I combine multiple events into a single one.

thanks,
Uday

Re: Combine multiple events into a single row

sudosplunk — Wed, 17 Oct 2018 12:04:40 GMT

Hi, assuming file name field is extracted, you can group events by "filename" field using transaction command. Have a look at docs and see if it meets your requirement.

Re: Combine multiple events into a single row

inventsekar — Wed, 17 Oct 2018 12:23:11 GMT

Hi Jeevan,
Pls provide us more info..
is the logs loaded to splunk already?
do you want to combine multiple events into single row - before indexing or after indexing(just you want to query and see the results as a single row)?

Re: Combine multiple events into a single row

jeevankumarg — Wed, 17 Oct 2018 12:33:06 GMT

Hi,

Yes. We have already loaded the logs/events to Splunk. Now our requirement is to intelligently show each transfer in a single row.

Re: Combine multiple events into a single row

jeevankumarg — Wed, 17 Oct 2018 12:42:32 GMT

Hi,

Yes. I tried grouping them using Filename and other fields but it is still returning many rows. Also its showing different field values in the same field one by one.

Re: Combine multiple events into a single row

sudosplunk — Wed, 17 Oct 2018 12:48:42 GMT

Can you provide some sample events and let us know your expected output.

Re: Combine multiple events into a single row

jeevankumarg — Wed, 17 Oct 2018 13:27:40 GMT

Hi,
Please could you check the image at the below location:
https://i.postimg.cc/9FVQ5fhw/Capture.jpg

It has sample data along with expected output.
Many thanks!

Re: Combine multiple events into a single row

echalex — Mon, 22 Oct 2018 11:43:10 GMT

Hi jeevankumarg,

As was already suggested, I think you need to start from combining the data using the transaction command. I had a look at your picture, but it wasn't entirely clear what sort of data you already have in your index. I'm assuming that you already have the data in the top of your picture?

So, I suppose you could go along with something like:

...| transaction SrcFileName TgtFileName
|eval LastStatus=mvindex(Status,-1)
|eval LastEndDateTime=mvindex(EndDateTime,-1)
|eval LastExecutionStep=mvindex(ExecutionStep,-1)
|eval LastDestinationServer=mvindex(DestinationServer,-1)
|rename LastStatus as Status
|rename LasteEndDateTime as EndDateTime
|rename LastExecutionStep as ExeceutionStep
|rename LastDestinationServer as DestinationServer
|table BusinessObject, SrcFileName, SourceServer, DestinationServer, Environment, ExecutionStep, Status, TgtFileName, StartDateTime, EndDateTime

The idea here is that within the transaction, the eval-command mvindex gets you the last value for that particular field, as transaction creates a multi-value field for fields that do not have identical values across the transaction. (If there is only one value, the field will not be multi-value, unless you give the option mvlist=true to transaction.)

Hope this helps you or at least gives you some idea for a solution.

Re: Combine multiple events into a single row

landen99 — Mon, 22 Oct 2018 12:34:19 GMT

Without seeing the raw source data in Splunk, the data presented in the image link provided (https://postimg.cc/grSW3mrG) reveals that there is insufficient information to reliably bring your events together. Even if source and destination files were used (similar source and file names in distinct transfers would mess with that method for grouping the transfers), the events do not hold the target filename constant throughout the transfer. Thus we must filter the ones out which are not constant.

| where SrcFileName!=TgtFileName OR Status=ERROR | stats last(Status) AS status by SrcFileName TgtFileName

from there you just add to the stats the other fields of interest using the same last command seen above on Status with the rest of the fields: BusinessObject, SrcFileName, SourceServer, DestinationServer, Environment, ExecutionStep, Status, TgtFileName, StartDateTime, EndDateTime