Hello, We currently have the following chart created. I would like to show each district split by week over a 4wk time period as shown in the Administration bar below. However, I'm struggling to get the results we want.
Any assistance would be greatly appreciated!
Base search:
```Grab badging data for the previous week```
index=* sourcetype="ms:sql" CARDNUM=* earliest=-4w@w latest=-0@w
| bin span=1d _time
| eval week_number=strftime(_time,"%U")
| dedup _time CARDNUM
| rename CARDNUM as badgeid
```lookup Ceridian and Active Directory and return fields associated with employeeID and badgeid```
| join type=left badgeid
```Use HR records to filter on only Active and LOA employees```
[search index=identities sourcetype="hr:ceridian" ("Employee Status"="Active" OR "Employee Status"="LOA*") earliest=-1d@d latest=@d
| eval "Employee ID"=ltrim(tostring('Employee ID'),"0")
| stats count by "Employee ID" | rename "Employee ID" as employeeID | fields - count
```Filter on Hybrid Remote users in Active Directory that are not Board Members and are in the Non-Branch region```
| lookup Employee_Data_AD_Extract.csv employeeID OUTPUT badgeid badgeid_1 RemoteStatus District employeeID Region]
| where like(RemoteStatus,"%Hybrid%") AND NOT like(District,"Board Members") AND Region="Non-Branches"
```Calculate the number of badge check-ins in a given week by badgeid```
|stats latest(Region) as Region latest(employeeID) as employeeID latest(District) as District latest(RemoteStatus) as status count as "weekly_badge_in" by badgeid week_number
```Compensate for temporary badge check-in (primary badge= badgeid temporary badge =badgeid_1```
| append
[|stats latest(Region) as Region latest(employeeID) as employeeID latest(District) as District latest(RemoteStatus) as status count as "weekly_badge_in" by badgeid_1 week_number | rename badgeid_1 as badgeid ]
| eval interval=case('weekly_badge_in'>=3,">=3", 'weekly_badge_in'<3,"<3")
```Calulation to determine the number of employees within District that are Hybrid Remote but have not badged-in ```
| join District
[| inputlookup Employee_Data_AD_Extract.csv | fields badgeid badgeid_1 RemoteStatus District employeeID Region
| where like(RemoteStatus,"%Hybrid%") AND NOT like(District,"Board Members") AND Region="Non-Branches"
| stats count as total by District]
After @fredclown above stated there was no way to do a fourth dimension in a Bar Chart, I opened a case with Splunk. Low and behold, he was right, which lead to some changes in our SPL. I thought I would share, just in case somebody else runs into the same issue.
```Grab badging data for the previous week```
index=* sourcetype="ms:sql" CARDNUM=* earliest=-4w@w latest=-0w@w
| bin span=1d _time
| rename CARDNUM as badgeid
| stats count by badgeid _time
| join type=left badgeid
```Use HR records to filter on only Active and LOA employees```
[search index=identities sourcetype="hr:ceridian" ("Employee Status"="Active" OR "Employee Status"="LOA*") earliest=-1d@d latest=@d
| eval "Employee ID"=ltrim(tostring('Employee ID'),"0")
| stats count by "Employee ID" _time | fields - time | rename "Employee ID" as employeeID | fields - count
```Filter on Hybrid Remote users in Active Directory that are not Board Members and are in the Non-Branch region```
| lookup Employee_Data_AD_Extract.csv employeeID OUTPUT badgeid badgeid_1 RemoteStatus District employeeID Region]
| where like(RemoteStatus,"%Hybrid%") AND NOT like(District,"Board Members") AND Region="Non-Branches"
| eval badgeid=coalesce(badgeid,badgeid_1)
```Calculate the number of badge check-ins in a given week by badgeid```
| bin span=1w _time
|stats latest(Region) as Region latest(employeeID) as employeeID latest(District) as District latest(RemoteStatus) as status count as "weekly_badge_in" by badgeid _time
```Calulation to determine the number of employees within District that are Hybrid Remote but have not badged-in```
| join District
[| inputlookup Employee_Data_AD_Extract.csv | fields badgeid badgeid_1 RemoteStatus District employeeID Region
| where like(RemoteStatus,"%Hybrid%") AND NOT like(District,"Board Members") ```AND NOT like(District,"IT")``` AND NOT like(District,"Digital") AND Region="Non-Branches"
| stats count as total by District]
| eval interval=case('weekly_badge_in'>=3,">=3", 'weekly_badge_in'<3,"<3")
| table _time District interval total
```Modify District Here vvv```
| where District="Compliance"
| stats max(total) as total count as total_intervals by _time District interval | sort District - _time | fields - District
| chart max(total) as total_emp max(total_intervals) as total by _time interval
| rename "total: <3" as "<3" "total: >=3" as ">=3" "total_emp: <3" as total | fields - "total_emp: >=3"
| stats sum(eval(total-('<3'+'>=3'))) as no_badge_ins last("<3") as "<3" last(">=3") as ">=3" by _time | rename _time as week_of
| eval week_of=strftime(week_of,"%Y-%m-%d")
If you change the stack mode to none you should see them as bars rather then being stacked. Click Format and then select the left most selection under Stack Mode.
