How to split four tables from different indexes into one?

[ipoluda]

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.

[somesoni2]

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 · Accepted solution]

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]

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

[richgalloway]

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