Dashboards & Visualizations

Pie charting data below

ranjitbrhm1
Communicator

Hello Guys, I have a query. I want to show the following data as pie where the pie visualisation should have a count of -ve value, +ve value and values showing pending with the projects to which they are attached to. Below is the data tabled.

    30  Trendmicro implementation
    30  Trendmicro implementation
    Pending Trendmicro implementation
    Pending Trendmicro implementation
    Pending Trendmicro implementation
    -18 Trendmicro implementation
    -18 Trendmicro implementation
    -18 Trendmicro implementation
    -18 Trendmicro implementation


426 Mcafee installation
426 Mcafee installation
426 Mcafee installation
Pending Mcafee installation
-37 Mcafee installation
-37 Mcafee installation
-37 Mcafee installation
-37 Mcafee installation

So ideally for me the pie chart would be having 3 slices where +ve values are 1 piece, --ve value is 1 piece and pending is also 1 pie. Is there a method where we can visualise the data?

0 Karma
1 Solution

niketn
Legend

@ranjitbrhm1, following is a run anywhere example with mocked up data based on your sample data and query provided.
I have added a transpose command at the end for creating the pie chart. Mock data has 2 On-Time, 1 Delayed and 1 Pending task for the Project.
PS: Command from | makeresults till | fields - data cook up dummy data, you would need to use your own query instead.

|  makeresults
|  fields - _time 
|  eval data="Splunk Implementation,Imran,2/2/2018,,3/2/2018,Install os,2/2/2018,3/5/2018;Splunk Implementation,Imran,2/2/2018,4/1/2018,3/2/2018,Assign host name,2/2/2018,3/5/2018;Splunk Implementation,Imran,2/2/2018,4/1/2018,3/2/2018,Assign IP address,2/2/2018,3/5/2018;Splunk Implementation,Imran,2/2/2018,4/1/2018,4/12/2018,Install splunk,2/2/2018,3/5/2018"
|  makemv data delim=";"
|  mvexpand data
|  eval data=split(data,",")
|  eval Project_Title=mvindex(data,0), Project_Manager=mvindex(data,1), task_start_date=mvindex(data,2), task_end_date=mvindex(data,3), task_deadline=mvindex(data,4), Tasks=mvindex(data,5), project_start_date=mvindex(data,6), project_end_date=mvindex(data,7), project_completed_date=mvindex(data,8)
|  fields - data
|  eval deadline_epoch=strptime(task_deadline,"%m/%d/%Y"), end_epoch=strptime(task_end_date,"%m/%d/%Y")
|  eval p_status=round(((end_epoch-deadline_epoch)/86400),0)
|  stats count(eval(isnull(p_status))) as "Pending" count(eval(isnotnull(p_status) AND p_status>=0)) as "On-time"  count(eval(isnotnull(p_status) AND p_status<0)) as "Delayed" by Project_Title
|  transpose header_field=Project_Title column_name=Project_Title

Please try out and confirm!

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

ranjitbrhm1
Communicator

This is proper. Thanks @niketnilay for taking effort in writing the query for me.
Thanks

0 Karma

niketn
Legend

@ranjitbrhm1, following is a run anywhere example with mocked up data based on your sample data and query provided.
I have added a transpose command at the end for creating the pie chart. Mock data has 2 On-Time, 1 Delayed and 1 Pending task for the Project.
PS: Command from | makeresults till | fields - data cook up dummy data, you would need to use your own query instead.

|  makeresults
|  fields - _time 
|  eval data="Splunk Implementation,Imran,2/2/2018,,3/2/2018,Install os,2/2/2018,3/5/2018;Splunk Implementation,Imran,2/2/2018,4/1/2018,3/2/2018,Assign host name,2/2/2018,3/5/2018;Splunk Implementation,Imran,2/2/2018,4/1/2018,3/2/2018,Assign IP address,2/2/2018,3/5/2018;Splunk Implementation,Imran,2/2/2018,4/1/2018,4/12/2018,Install splunk,2/2/2018,3/5/2018"
|  makemv data delim=";"
|  mvexpand data
|  eval data=split(data,",")
|  eval Project_Title=mvindex(data,0), Project_Manager=mvindex(data,1), task_start_date=mvindex(data,2), task_end_date=mvindex(data,3), task_deadline=mvindex(data,4), Tasks=mvindex(data,5), project_start_date=mvindex(data,6), project_end_date=mvindex(data,7), project_completed_date=mvindex(data,8)
|  fields - data
|  eval deadline_epoch=strptime(task_deadline,"%m/%d/%Y"), end_epoch=strptime(task_end_date,"%m/%d/%Y")
|  eval p_status=round(((end_epoch-deadline_epoch)/86400),0)
|  stats count(eval(isnull(p_status))) as "Pending" count(eval(isnotnull(p_status) AND p_status>=0)) as "On-time"  count(eval(isnotnull(p_status) AND p_status<0)) as "Delayed" by Project_Title
|  transpose header_field=Project_Title column_name=Project_Title

Please try out and confirm!

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

ranjitbrhm1
Communicator

Thank you all for the suggestions. I wrote a query very similar to @p_gurav answer
index="bsg" | eval deadline_epoch=strptime(task_deadline,"%m/%d/%Y")| eval end_epoch=strptime(task_end_date,"%m/%d/%Y") |eval diffrence=(end_epoch-deadline_epoch) |eval p_status=round((diffrence)/86400,0) | fillnull value=pending| stats count(eval(p_status>0)) as on_time, count(eval(p_status<0)) as delayed, count(eval(p_status=pending)) as deffered by Project_Title

But my challenge now is on the pie its not showing the delayed and on time correctly and deffered split by project name. My sample data looks like this. I uploaded it off a CSV that we created,

Project_Title   Project_Manager task_start_date task_end_date   task_deadline   Tasks   project_start_date  project_end_date    project_completed_date
Splunk Implementation   Imran   2/2/2018    4/1/2018    3/2/2018    Install os  2/2/2018    3/5/2018    
Splunk Implementation   Imran   2/2/2018    4/1/2018    3/2/2018    Assign host name    2/2/2018    3/5/2018    
Splunk Implementation   Imran   2/2/2018    4/1/2018    3/2/2018    Assign IP address   2/2/2018    3/5/2018    
Splunk Implementation   Imran   2/2/2018    4/1/2018    3/2/2018    Install splunk  2/2/2018    3/5/2018    
0 Karma

niketn
Legend

@ranjitbrhm1, what does the positive and negative values indicate? Do you need those to be represented as well?

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

p_gurav
Champion

You first need to extract those values(e.g. -18, pending etc) in field called count, then try something like :

| eval type=case(count<1, "-ve", count >= 1, "+ve", count=="Pending", "Pending") | stats count by type
0 Karma
Get Updates on the Splunk Community!

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...