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
SplunkTrust
SplunkTrust

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!

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...

Splunkbase | Splunk Dashboard Examples App for SimpleXML End of Life

The Splunk Dashboard Examples App for SimpleXML will reach end of support on Dec 19, 2024, after which no new ...

Understanding Generative AI Techniques and Their Application in Cybersecurity

Watch On-Demand Artificial intelligence is the talk of the town nowadays, with industries of all kinds ...