Pass a selected value to a join after calculations and other join with the same eventtype

[ricardocastille]

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?

[ricardocastille]

I solved the problem after analyzing how the groups work, and it is as follows:

eventtype="State"

| search loc="location_1"
| dedup id
| stats count
| rename count as total_records , values(hostname),
| rename values(hostname) as host
| 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 ]

| eventstats count(host) as host_number
| table host_number days total_records

Thanks for the idea, it served as my base

[p_gurav]

Hi,

can you try passing parameter before join query like:

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

[ricardocastille]

I tried the proposal and I still get the result:

host_number....days....total_records
174.... 2....376

The data that I have as an example are:

Eventttype=”State”

loc=”location_1” host_number=5

host_1=56
host_2=78
host_3=89
host_4=91
host_5=62 total_records=376

loc=”location_2” host_number=3

host_7=71
host_8=70
host_9=52 total_records=193

loc=”location_3” host_number=8

host_10=85
host_11=36
host_12=97
host_13=55
host_14=48
host_15=67
host_16=76
host_17=82
host_18=68 total_records=614

loc=”location_4” host_number=1

.
.
. .. total_records=62
loc=”location_5” host_number=2

.
.
… total_records=118
loc=”location_6” host_number=4
.
.
… total_records=235
.
.
etc

I can not make the sum of the host_number that is 5 and not the total of the hosts that are operating