combine events from index and inputlookup

[hvdtol]

Hello,

I would like some help

I am trying to combine 2 events from my index and 2 event coming from a lookup file, into 2 table lines.
Like this:

index=blala
..

| table env host start end duration week yymm
| append
[ | inputlookup mylookup.csv
| eval st_time=strptime(startdate, "%Y-%m-%d")
| eval en_time=strptime(enddate, "%Y-%m-%d")
| addinfo
| where info_min_time>= st_time AND info_max_time<=en_time
]
| table env host start end duration week yymm
| eval env = "DEV"
| table env host start end duration week yymm

This is the rough output

env  host    start                                     end                                         duration  week          yymm
DEV host1 02/06/2021:10:29:52 02/06/2021:11:20:16    50             2021-05
DEV                                                                                                                                                     2105
DEV host2 02/06/2021:10:29:33 02/06/2021:11:07:42     38           2021-05
DEV                                                                                                                                                     2105

And this is what i am trying to accomplise
DEV host1 02/06/2021:10:29:52 02/06/2021:11:20:16    50             2021-05   2105
DEV host2 02/06/2021:10:29:33 02/06/2021:11:07:42     38           2021-05    2105

I tried several commands, but i am unable to do so.

Thank you in advance

regards,

Harry

[scelikok]

Hi @hvdtol,

You can use list function on stats;

index=blala
..

| table env host start end duration week yymm
| append
[ | inputlookup mylookup.csv
| eval st_time=strptime(startdate, "%Y-%m-%d")
| eval en_time=strptime(enddate, "%Y-%m-%d")
| addinfo
| where info_min_time>= st_time AND info_max_time<=en_time
]
| fields env host start end duration week yymm
| eval env = "DEV"
| stats list(*) as * by env
| table env host start end duration week yymm

[richgalloway]

You're almost there!  With the two searches appended, the next step is to merge the results.  That's best done using (unintuitively) stats.  Think of it as a grouping command as well as a statistics command.

index=blala
..

| table env host start end duration week yymm
| append
[ | inputlookup mylookup.csv
| eval st_time=strptime(startdate, "%Y-%m-%d")
| eval en_time=strptime(enddate, "%Y-%m-%d")
| addinfo
| where info_min_time>= st_time AND info_max_time<=en_time
]
| fields env host start end duration week yymm
| eval env = "DEV"
| stats values(*) as * by env
| table env host start end duration week yymm

[hvdtol]

Hi,

Thank you for thinking along with me
Unfortunatly i now get 1 event where the second line for yymm is empty
Any help is appreciated

env          host     start                                     end                                        duration    week           yymm
DEV        host1  02/06/2021:10:29:52  02/06/2021:11:20:16  50                2021-05    2105
                 host2  02/06/2021:10:29:33  02/06/2021:11:07:42  38               2021-05

Regards,

Harry