Hi community,
I have 2 data sources, 1 from a csv to get the list of district (include number of population according to each district). Other sources come from PostgreSQL. The common info is the district.
After a lookup csv, I have the list of district, for ex 6 districts.
Knowing that 5 districts have the equivalent population (ex 500), another district has only 100 people living there.
I want to do the span later, to count the activities of each district and send an alert if there is no activity of a district. But the difficulty is the span is not the same amongs all the districts.
I want to let span =1 day for 5 districts which have 500 people, and 5 days for the district with 100 population.
In a same search, can I do a case or if else to separate 2 cases?
Here is what I'm doing:
|dbxquery connection="database" query=" SELECT * FROM table"
|lookup lookup.csv numero OUTPUT DISTRICT
|eval list_district = "1,2,3,4,5,6"
|eval split_list_district= split(list_district,",")
|mvexpand split_list_district
|where DISTRICT=split_list_district
|eval _time=strptime(time_receive,"%Y-%m-%dT%H:%M:%S.%N")
|eval _comment="Can we do something here to separate 2 cases"
|bin _time span=1h
|chart sum(count_activity) as count by _time DISTRICT
......
Assuming there's a field "population" in the data, you can use relative_time. Something like
|dbxquery connection="database" query=" SELECT * FROM table"
|lookup lookup.csv numero OUTPUT DISTRICT
|eval list_district = "1,2,3,4,5,6"
|eval split_list_district= split(list_district,",")
|mvexpand split_list_district
|where DISTRICT=split_list_district
|eval _time=strptime(time_receive,"%Y-%m-%dT%H:%M:%S.%N")
| eval cutoff = if(population < 120, relative_time(now(), "-1d"), relative_time(now(), "-5d")) ``` use 120 to allow margin ```
| where _time > cutoff
|bin _time span=1h
|chart sum(count_activity) as count by _time DISTRICT