Dashboards & Visualizations

Stacked Column Chart w/more than 3 criteria challenge

lbrhyne
Path Finder

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!

splunk_graphic.png

 

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]

 

Labels (1)
0 Karma

lbrhyne
Path Finder

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")

2023-11-07_05-48-41.png 

0 Karma

fredclown
Contributor

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.

image.png

0 Karma

lbrhyne
Path Finder

Thank you for the response but the suggestion does not resolve the problem. The dashboard needs to be delineated in a stacked format by District with each District showing weekly results over a 4week period.

0 Karma

fredclown
Contributor

Are you saying you want it both stacked and a weekly bar chart overlaying the stack?

0 Karma

lbrhyne
Path Finder

Yes. So imagine the first stack with the markup being applied across the chart.

0 Karma

fredclown
Contributor

Ah got it. Unfortunately, there is no way to get that kind of a chart with the built in visualizations.

Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...