Splunk lookup csv file contains multiple occurrences of items. Need to query these items in an index for each unique time stamp range in csv

Question

lookup Test2.csv in CSV format where EVENT_ID can have multiple SiteID fields and SiteID can have multiple EVENT_IDs. Only SiteID is a field in the splunk index.

YEAR, SiteID, earliest_date, latest_date, EVENT_ID
2019, AB111, 1560988800, 1562112000, ABSE00350
2019, AB111, 1562198400, 1563321600, ABSE00351
2019, AB111, 1548892800, 1550016000, ABSE00352
2019, AB112, 1548892800, 1550016000, ABSE00352

I use the lookup to query an index, to calculate a KPI for each row.

Ideal query Output (KPI computed for unique combination of SiteID & EVENT_ID) in index pm_busy_half_hour:

SiteID, KPI, EVENT_ID
AB111, 68.4, ABSE00350
AB111, 74.3, ABSE00351
AB111, 22.1, ABSE00352
AB112, 34.5, ABSE00352

This is the top of my code, where I do the inputlookup, before proceeding to calculate the KPI from data in the index. However it only gives me a result with a single aggregate of the SiteID, not unique per row as desired.

index=pm_busy_half_hour
[| inputlookup Test2.csv
| rename earliest_date as earliest, latest_date as latest
| table SiteID earliest latest
]
.....
.....
.....

Please advise

Answer 1

The reason that your search is not working is that your search has multiple earliest and latest times, which end up getting combined and difficult to calculate the results of each KPI.

What I suggest you look at is the map command.

| inputlookup Test2.csv
| map search="index=pm_busy_half_hour earliest=$earliest_date$ latest=$latest_date$ | where SiteId=$SiteId$ | stats count" maxsearches=100

This will run a separate search for each of the time periods in your lookup. You'll probably need to modify the stats command to be a different KPI calculation