Need clarity on how to use multiple indices for creating an index-pattern

Hi all

INDEX 1 = sid_agent_276043_tsfm_idx (docs = 18842)

{
    "sid_agent_276043_tsfm_idx": {
        "mappings": {
            "_meta": {
                "created_by": "transform",
                "_transform": {
                    "transform": "sid_agent_276043",
                    "version": {
                        "created": "7.7.0"
                    },
                    "creation_date_in_millis": 1594324251360
                }
            },
            "properties": {
                "data": {
                    "properties": {
                        "sessionid": {
                            "properties": {
                                "keyword": {
                                    "type": "keyword"
                                }
                            }
                        },
                        "ts": {
                            "properties": {
                                "avg": {
                                    "properties": {
                                        "agent": {
                                            "type": "double"
                                        }
                                    }
                                },
                                "max": {
                                    "properties": {
                                        "agent": {
                                            "type": "date"
                                        }
                                    }
                                },
                                "min": {
                                    "properties": {
                                        "agent": {
                                            "type": "date"
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    }
}

INDEX 2 = sid_all_276043_tsfm_idx (docs = 110201)

{
    "sid_all_276043_tsfm_idx": {
        "mappings": {
            "_meta": {
                "created_by": "transform",
                "_transform": {
                    "transform": "sid_all_276043",
                    "version": {
                        "created": "7.7.0"
                    },
                    "creation_date_in_millis": 1594324096704
                }
            },
            "properties": {
                "data": {
                    "properties": {
                        "sessionid": {
                            "properties": {
                                "keyword": {
                                    "type": "keyword"
                                }
                            }
                        },
                        "ts": {
                            "properties": {
                                "avg": {
                                    "properties": {
                                        "all": {
                                            "type": "double"
                                        }
                                    }
                                },
                                "max": {
                                    "properties": {
                                        "all": {
                                            "type": "date"
                                        }
                                    }
                                },
                                "min": {
                                    "properties": {
                                        "all": {
                                            "type": "date"
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    }
}

INDEX-PATTERN USECASE 1
Index-Pattern Name = sid*
This index pattern matches 2 indices.

  • sid_agent_276043_tsfm_idx
  • sid_all_276043_tsfm_idx
    TimeFilter field name = data.ts.min.all

Resultant records = 110201

INDEX-PATTERN USECASE 2
Index-Pattern Name = sid*
This index pattern matches 2 indices.

  • sid_agent_276043_tsfm_idx
  • sid_all_276043_tsfm_idx
    TimeFilter field name = data.ts.min.agent

Resultant records = 18842

INDEX-PATTERN USECASE 3
Index-Pattern Name = sid*
This index pattern matches 2 indices.

  • sid_agent_276043_tsfm_idx
  • sid_all_276043_tsfm_idx
    TimeFilter field name = "I dont want to use the Time Filter"

Resultant records = 18842 + 110201 = 129043

I need a way to create an index-pattern with these two indices and be able to use a timestamp filter and get a total of 129043 records

How do I do this ?

Thanks
sanjay

Can you describe your use case in more detail? What type of data are you putting into Elasticsearch? What are you trying to get out of Kibana?

Thanks @Nathan_Reese for your reply. Here is an explanation of what I am trying to implement

ORIGINAL_INDEX
==============
sid,ts,stype,ttype
286512.3,1593592129660
286512.3,1593592129047
286512.3,1593592146070,c,a
286512.3,1593592190945,c,a
286512.3,1593593021897,a,c
286512.3,1593594831673
287340.5,1593893050151
287340.5,1593893049840
287340.5,1593894346350,a,c
287340.5,1593894535655,a,c
287340.5,1593895845499,a,c
287697.5,1593567171373
287697.5,1593567171615
287697.5,1593567379793,a,c
287697.5,1593567506018,c,a
287697.5,1593567478388,c,a
287697.5,1593567676544,a,c
287697.5,1593567898980,c,a
287697.5,1593568027621,a,c
287697.5,1593568018466,a,c
287697.5,1593568101659,a,c
293725.3,1593348655629
293725.3,1593348655888
293725.3,1593348712774,c,a
293725.3,1593348891164,a,c
293725.3,1593348910191,c,a
293725.3,1593348920776,a,c
293725.3,1593348936065,a,c
293725.3,1593348961760,c,a
293725.3,1593349171472,c,a
293725.3,1593350239979,c,a
293725.3,1593350211482,a,c
293725.3,1593350258849,c,a
293725.3,1593350292212,c,a
293725.3,1593350293782,c,a
293725.3,1593350276663,c,a
293725.3,1593350278506,c,a
293725.3,1593350480805,c,a
293725.3,1593350443535,a,c
293725.3,1593350510643,c,a
293725.3,1593350598408,a,c
293725.3,1593350563773,c,a
293725.3,1593350625343,c,a
293725.3,1593351391065,a,c
293725.3,1593351570099,a,c
294070.3,1593947030933,c,a
294070.3,1593947012647,c,a
294070.3,1593947618191,a,c
294070.3,1593948197406,c,a
294070.3,1593949014241,a,c
294070.3,1593946076441
294070.3,1593946076650
294070.3,1593946148086,c,a
294070.3,1593946156988,c,a
294070.3,1593946574804,a,c
294070.3,1593946591778,a,c
294070.3,1593946618206,a,c
294070.3,1593946656485,a,c
294070.3,1593946689398,a,c
294070.3,1593946995877,c,a
294070.3,1593947681353,c,a
294070.3,1593947784384,c,a
294070.3,1593949386474,a,c
294070.3,1593949397572,a,c
294070.3,1593950512772,a,c
294070.5,1593954191886,a,c
294070.5,1593954213113,a,c
294070.5,1593954074661
294070.5,1593954074930
294070.5,1593954185074,a,c
295536.3,1593308160628
295536.3,1593308182630,a,c
295536.3,1593308571190,a,c
300499.3,1594019343266
300499.3,1594019343520
300499.3,1594019955827,c,a
300499.3,1594020172994,a,c
300499.3,1594021218103,a,c
301573.5,1593263993673
301573.5,1593263993962
301573.5,1593264592697,a,c
301573.5,1593264597396,a,c
301573.5,1593264652019,c,a
301573.5,1593264682561,c,a
301573.5,1593265159237,a,c
301573.5,1593265505047,c,a
301573.5,1593265733289,a,c
301573.5,1593265746045,c,a
301573.5,1593266209504,a,c

INDEX_1
=======
SELECT sid, min(ts) as mts, max(ts) as mats 
from ORIGINAL_INDEX 
group by sid

sid,mts,mats
286512.3,1593592129047,1593594831673
287340.5,1593893049840,1593895845499
287697.5,1593567171373,1593568101659
293725.3,1593348655629,1593351570099
294070.3,1593946076441,1593950512772
294070.5,1593954074661,1593954213113
295536.3,1593308160628,1593308571190
300499.3,1594019343266,1594021218103
301573.5,1593263993673,1593266209504

INDEX_2
=======
SELECT sid, min(ts) as mts, max(ts) as mats 
from ORIGINAL_INDEX 
where stype = 'a' or ttype = 'a'
group by sid

asid,amts,amats
286512.3,1593592146070,1593593021897
287340.5,1593894346350,1593895845499
287697.5,1593567379793,1593568101659
293725.3,1593348712774,1593351570099
294070.3,1593946148086,1593950512772
294070.5,1593954185074,1593954213113
295536.3,1593308182630,1593308571190
300499.3,1594019955827,1594021218103
301573.5,1593264592697,1593266209504

DESIRED_INDEX_PATTERN
=====================
Combined INDEX_1 and INDEX_2 

INDEX_1_2
---------
asid,amts,amats,sid,mts,mats
286512.3,1593592146070,1593593021897,286512.3,1593592129047,1593594831673
287340.5,1593894346350,1593895845499,287340.5,1593893049840,1593895845499
287697.5,1593567379793,1593568101659,287697.5,1593567171373,1593568101659
293725.3,1593348712774,1593351570099,293725.3,1593348655629,1593351570099
294070.3,1593946148086,1593950512772,294070.3,1593946076441,1593950512772
294070.5,1593954185074,1593954213113,294070.5,1593954074661,1593954213113
295536.3,1593308182630,1593308571190,295536.3,1593308160628,1593308571190
300499.3,1594019955827,1594021218103,300499.3,1594019343266,1594021218103
301573.5,1593264592697,1593266209504,301573.5,1593263993673,1593266209504

I want to use "amts" as the time filter in the index_pattern

Define a scripted_field "deltatime" on INDEX_1_2 
------------------------------------------------
return (mats - mts); // Note that its not (amats - amts)
 

INDENDED_AGGREGATION
====================
Average time grouped by Column = "asid"

(deltatime_agentsid + deltatime_286512.3 + deltatime_287340.5 + deltatime_287697.5 + deltatime_293725.3 + deltatime_294070.3 + deltatime_294070.5 + deltatime_295536.3 + deltatime_300499.3 + deltatime_301573.5) / 9 (number of sids)

Why are you inserting grouped data into elasticsearch vs just inserting each row into elasticsearch? You can visualize elasticsearch aggregations in kibana.

Hi @Nathan_Reese

If you refer to the ORIGINAL_INDEX, how do you calculate deltatime for a sid ?
[max(ts) - min(ts)]

I am trying to achieve this by creating a transform. How would you achieve this in Kibana without a transform ?

Thanks
sanjay

Have you tried using the enrich processor? You could use this to add deltatime when inserting documents into elasticsearch.

Hi @Nathan_Reese

"DeltaTime" is not a property of each document. There is one "deltatime" value for each sid
In the docs below
the deltatime for sid=286512.3 is (1593594831673 - 1593592129047). Can this be done via an "enrich processor"?

Thanks
sanjay

sid,ts,stype,ttype
286512.3,1593592129660
286512.3,1593592129047 >>>>> min(ts)
286512.3,1593592146070,c,a
286512.3,1593592190945,c,a
286512.3,1593593021897,a,c
286512.3,1593594831673 >>>>>>>> max(ts)

I have never tried but I am pretty sure you can use enrich processor to derive a calculated field from one or more documents.

If that does not work, you could use logstash and ruby filter to enrich your document with a delta time field.

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.