Splunk Search

How to split four tables from different indexes into one?

ipoluda
Explorer

Hi,

I have four indexes with call data. Each index is populated with the data of the corresponding SIP operator, i.e. XML in one index, Key-Value in the second, CSV in the third, and JSON in the last. I need to get statistics on these calls: who called, how many times and what is the total time of these conversations. That is, as in the attached picture. The question is how to "glue" these statistics together. the main difficulty is that before getting normalized statistics (or a table), I have many transformations for each index. Please help me figure out the best way to do this.

Labels (2)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

Depending on how many events you have returned by your index searches, you could use append (subsearches are limited to 50,000 events)

index=xml_index
... xml extractions ...
| append [search index=kv_index
... kv extractions ...]
| append [search index=csv_index
... csv extractions ...]
| append [search index=json_index
... json extractions ...]
| stats count sum(duration) as duration by user

View solution in original post

somesoni2
Revered Legend

What kind of transformations you run on data for each index?

Are the fields all extracted and common fields are present on all indexes? If yes, then you can run something like this to get data from all indexes.

index=<xml_index> OR index=<kv_index> OR index=<csv_index> OR index=<json_index>
| eval count=coalesce('xml_index_count_field','kv_index_count_field','csv_index_count_field','json_index_count_field')
| eval duration=coalesce('xml_index_duration_field','kv_index_duration_field','csv_index_duration_field','json_index_duration_field')
| eval user=coalesce('xml_index_user_field','kv_index_user_field','csv_index_user_field','json_index_user_field')
| stats sum(count) as count sum(duration) as duration by user

 

ITWhisperer
SplunkTrust
SplunkTrust

Depending on how many events you have returned by your index searches, you could use append (subsearches are limited to 50,000 events)

index=xml_index
... xml extractions ...
| append [search index=kv_index
... kv extractions ...]
| append [search index=csv_index
... csv extractions ...]
| append [search index=json_index
... json extractions ...]
| stats count sum(duration) as duration by user

ipoluda
Explorer

Thank you!
It is the same I was needed!))

richgalloway
SplunkTrust
SplunkTrust

Process each index separately using the append command then combine the results with a final stats command.

<<your XML-processing query>>
| append [ <<your KV-processing query>> ]
| append [ <<your CSV-processing query>> ]
| append [ <<your JSON-processing query>> ]
| stats sum(count) as count, sum(duration_sec) as duration_sec by user

 

---
If this reply helps you, Karma would be appreciated.
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...