I am trying to count the employees per location during a particular shift and date. I'm pretty new to SPLUNK and I am approaching the searches like a SQL query. I can display them if I am not going to count the employees using this.
source=something1OR source=something2 host=somethinghost index=somethingindex sourcetype=csv
| stats values(Location_id) as LocationID, values(Employee_ID) as EmpID, values(Shift_id) as Shift by Employee_ID,Date
| table Date,LocationID,EmpID, Shift
I tried replacing values in Employee_ID to Count and playing around with the "by" but the count ends of as 2 for all instead of 1.
Shifts
Shift_id | Employee_ID | Date |
1 | 994163 | 8/15/2020 |
2 | 123456 | 8/15/2020 |
3 | 654321 | 8/15/2020 |
1 | 994163 | 8/16/2020 |
2 | 123456 | 8/16/2020 |
3 | 654321 | 8/16/2020 |
1 | 991234 | 8/16/2020 |
Locations
Location_id | Employee_ID | Date |
L01 | 994163 | 8/15/2020 |
L02 | 123456 | 8/15/2020 |
L03 | 654321 | 8/15/2020 |
L01 | 994163 | 8/16/2020 |
L02 | 123456 | 8/16/2020 |
L03 | 654321 | 8/16/2020 |
L07 | 991234 | 8/16/2020 |
desired output
date | LocationID | count | Shift |
8/15/2020 | L02 | 1 | 2 |
8/15/2020 | L03 | 1 | 3 |
8/15/2020 | L01 | 1 | 1 |
8/16/2020 | L02 | 1 | 2 |
8/16/2020 | L03 | 1 | 3 |
8/16/2020 | L07 | 1 | 1 |
8/16/2020 | L01 | 1 | 1 |
what i am getting
date | LocationID | count | Shift |
8/15/2020 | L02 | 2 | 2 |
8/15/2020 | L03 | 2 | 3 |
8/15/2020 | L01 | 2 | 1 |
8/16/2020 | L02 | 2 | 2 |
8/16/2020 | L03 | 2 | 3 |
8/16/2020 | L07 | 2 | 1 |
8/16/2020 | L01 | 2 | 1 |
sample:
| makeresults
| eval _raw="Shift_id Employee_ID Date
1 994163 8/15/2020
2 123456 8/15/2020
3 654321 8/15/2020
1 994163 8/16/2020
2 123456 8/16/2020
3 654321 8/16/2020
1 991234 8/16/2020"
| multikv forceheader=1
| append [| makeresults
| eval _raw="Location_id Employee_ID Date
L01 994163 8/15/2020
L02 123456 8/15/2020
L03 654321 8/15/2020
L01 994163 8/16/2020
L02 123456 8/16/2020
L03 654321 8/16/2020
L07 991234 8/16/2020"
|multikv forceheader=1 ]
| fields - _* linecount
| selfjoin Date Employee_ID
| eventstats count by Date Employee_ID
try selfjoin and eventstats