Splunk Search

How can I count a field grouped by fields from 2 different Sources

taichii
New Member

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_idEmployee_IDDate
19941638/15/2020
21234568/15/2020
36543218/15/2020
19941638/16/2020
21234568/16/2020
36543218/16/2020
19912348/16/2020

 

Locations

Location_idEmployee_IDDate
L019941638/15/2020
L021234568/15/2020
L036543218/15/2020
L019941638/16/2020
L021234568/16/2020
L036543218/16/2020
L079912348/16/2020


desired output

dateLocationIDcountShift
8/15/2020L0212
8/15/2020L0313
8/15/2020L0111
8/16/2020L0212
8/16/2020L0313
8/16/2020L0711
8/16/2020L0111


what i am getting

dateLocationIDcountShift
8/15/2020L0222
8/15/2020L0323
8/15/2020L0121
8/16/2020L0222
8/16/2020L0323
8/16/2020L0721
8/16/2020L0121
Labels (2)
0 Karma

to4kawa
Ultra Champion

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

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...