count of events in dynamic time defined by token

[spisiakmi]

Hi can anybody help with this problem, please?

source1: lookup Tab (lookup.csv)

att1 att2 att3

F1 1100 12.09.2024
F2 1100 23.04.2024
F3 1100 15.06.2024
F4 1100 16.03.2024

att1 is also in  index=myindex

I want to have in a table for all att1 from lookup.csv count of all events from index=myindex att1=$att1$ AND earliest=strptime($att3$, "%d.%m.%Y")

output:

att1 count(from myindex) att2 att3

F1 count 1100 12.09.2024
F2 count 1100 23.04.2024
F3 count 1100 15.06.2024
F4 count 1100 16.03.2024

[gcusello]

Hi @spisiakmi ,

let me understand:

do you want to put in an input both the att1 and att3 tokens or do you want to pass all the att1 and att3 values of the lookup?

in the first case, you have

at first to create in a dashboard a dropdown using a search like the following:

| inputlookup lookup.csv
| eval token=att1.",".att3
| dedup token
| sort token
| table token

passing by value the token to te following search.

Then run this search (in the same dashboard)

index=myindex [ | makeresults | rex field=$token$ "^(?<att1>[^,]+),(?<att3>.*)" | eval earliest=strptime(att3, "%d.%m.%Y") | fields att1 att3 ]
| ...

Ciao.

Giuseppe

[spisiakmi]

Hi gcusello, thank you for your reply. In fact there should be a saved search running on daily basis.

Example:

for every row in lookup table should run a query.

index=myindex att1=F1 AND earliest=strptime("12.09.2024", "%d.%m.%Y")
| stats count as cnt

index=myindex att1=F2 AND earliest=strptime("23.04.2024", "%d.%m.%Y")
| stats count as cnt

index=myindex att1=F3 AND earliest=strptime("15.06.2024", "%d.%m.%Y")
| stats count as cnt

index=myindex att1=F4 AND earliest=strptime("16.03.2024", "%d.%m.%Y")
| stats count as cnt

result:
att1 cnt att2 att3
F1 234 1100 12.09.2024

F2 4235 1100 23.04.2024

F3 3763 1100 15.06.2024

F4 42314 1100 16.03.2024

[gcusello]

Hi @spisiakmi ,

ok, is the solution ok for you?

let us know if we can help you more, or, please, accept one answer for the other people of Community.

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated by all the contributors 😉

[spisiakmi]

Hi gcusello, thank you very much for supporting me. Unfortunately it is not, what I have to achieve.

1. I need saved search, so 1 query in fact.

2. your solution is using dropdown, where only 1 value is/can be chosen. I need to run a search through all elements of the token. Something like foreach item of the token, run query.