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?
@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!
This is proper. Thanks @niketnilay for taking effort in writing the query for me.
Thanks
@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!
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
@ranjitbrhm1, what does the positive and negative values indicate? Do you need those to be represented as well?
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