This is the question; In general, I have been able to resolve my doubts after the publications here, but I have had problems with this a bit since I am struggling to pass a value to a join.
What I am trying to do is the following: I have an eventtype called "State" that has locations, each of the locations have different host amounts assigned, and each host makes different amounts of records.
I want to obtain the total number of the host assigned to the locality, the days of the selected period of time and the sum of the records of the host of the locality to be searched. I have modified this query in different ways and I do not achieve the result I want.
eventtype="State"
| search loc="location_1"
| dedup id | stats count | rename count as total_records
| join loc type=left[| search eventtype="State" loc | eval day_of_week = strftime(_time,"%A") | where NOT (day_of_week="Saturday" OR day_of_week="Sunday")
| bin span=1d _time | stats count dc(_time) as days by day_of_week | stats sum(days) as days ]
| join loc type=left[| search eventtype="State" loc | dedup h | search loc | stats count(h) as host_number ]
| table host_number days total_records
The result I get is the following:
host_number.... days.... total_records
174.... 2.... 376
When modifying | search loc by | search loc = "$loc$", I get the following result:
host_number.... days.... total_records
0.... 2.... 376
The result of the host_number must be 5 and not 0, I have modified the query in different ways and I can not join the value of loc (which I have been able to do in other queries). What option do I have to solve the query?
... View more