Hi there,
Here's the query I'm running, which generates the same results :
SELECT
TRUNC(CREATION_DATE_TIME),
COUNT(1) TOTAL_PROCESSES,
COUNT( distinct PROVIDER_name ) DISTINCT_PROVIDERS,
SUM(CASE WHEN FLAG = 'O' THEN 1 END ) OUTGOING,
SUM(CASE WHEN FLAG = 'I' THEN 1 END ) INCOMING,
FROM
GROUP BY TRUNC(CREATION_DATE_TIME)
here's what i am running in Splunk :
index="MyIndex" sourcetype="MySourceType" source="MySource"
| eval x_out=if((FLAG=="O"),1,0), x_in=if((FLAG=="I"),1,0)
| convert ctime(creation_date_time) as cdt timeformat=%m/%d/%y
| timechart span=7d dc(provider_name) by cdt
| stats sum(x_out) as Outbound sum(x_in) as Inbound dc(provider_name) as Providers
@niketn , thanks for your reply. I did find my solution ; it was hiding all along in plain sight. Some of my fields were in lowercase i.e. dc(provider_name) instead of dc(PROVIDER_NAME).
But all the info and the links you have provided is very useful, for someone who is starting off, like me. Cheers !
@niketn , thanks for your reply. I did find my solution ; it was hiding all along in plain sight. Some of my fields were in lowercase i.e. dc(provider_name) instead of dc(PROVIDER_NAME).
But all the info and the links you have provided is very useful, for someone who is starting off, like me. Cheers !
@mnikhil7692 hardcoding was only to generate sample data as per your question as we will not have same data as your system. Please provide some sample events (mock/anonymize any sensitive information).
Unless we know what your event looks like and what are your fields it will not be easy for us to assist, however, based on your query provided try the following and confirm:
index="MyIndex" sourcetype="MySourceType" source="MySource"
| eval _time=strptime(creation_date_time,"%m/%d/%Y")
| timechart count(eval(FLAG=="O")) as Outbound count(eval(FLAG=="I")) as Inbound dc(provider_name) as Providers
OR May be the following if you do not need Time as epoch time and your existing creation_date_time is in MM/DD/YYYY
format:
index="MyIndex" sourcetype="MySourceType" source="MySource"
| chart count(eval(FLAG=="O")) as Outbound count(eval(FLAG=="I")) as Inbound dc(provider_name) as Providers by creation_date_time
Following is the run anywhere example based on cooked up data as per my understanding of your data. Each event has creation_date_time in MM/DD/YYYY format. FLAG O and I denote Outbound and Inbound respectively and provider_name can repeat, where you want unique provider count.
PS: Intention of run anywhere search is to demo the required output. Instead of commands from | makeresults till | KV, you will use your main search using index="MyIndex" sourcetype="MySourceType" source="MySource", as mentioned above.
| makeresults
| fields - _time
| eval data="CREATION_DATE_TIME=10/24/2018,FLAG=O,provider_name=X;CREATION_DATE_TIME=10/24/2018,FLAG=O,provider_name=Y;CREATION_DATE_TIME=10/24/2018,FLAG=O,provider_name=X;CREATION_DATE_TIME=10/24/2018,FLAG=O,provider_name=X;CREATION_DATE_TIME=10/24/2018,FLAG=O,provider_name=Y;CREATION_DATE_TIME=10/24/2018,FLAG=I,provider_name=Y;CREATION_DATE_TIME=10/24/2018,FLAG=I,provider_name=Y;CREATION_DATE_TIME=10/24/2018,FLAG=I,provider_name=X;CREATION_DATE_TIME=10/24/2018,FLAG=I,provider_name=Y;CREATION_DATE_TIME=10/24/2018,FLAG=I,provider_name=Y;CREATION_DATE_TIME=10/15/2018,FLAG=O,provider_name=Y;CREATION_DATE_TIME=10/15/2018,FLAG=O,provider_name=Y;CREATION_DATE_TIME=10/15/2018,FLAG=O,provider_name=Y;CREATION_DATE_TIME=10/15/2018,FLAG=I,provider_name=X;CREATION_DATE_TIME=10/15/2018,FLAG=I,provider_name=Y;CREATION_DATE_TIME=10/24/2018,FLAG=I,provider_name=X;CREATION_DATE_TIME=10/15/2018,FLAG=I,provider_name=Y;CREATION_DATE_TIME=10/15/2018,FLAG=I,provider_name=X;CREATION_DATE_TIME=10/15/2018,FLAG=I,provider_name=Y;CREATION_DATE_TIME=10/15/2018,FLAG=I,provider_name=Y;"
| makemv data delim=";"
| mvexpand data
| rename data as _raw
| KV
| eval _time=strptime(CREATION_DATE_TIME,"%m/%d/%Y")
| timechart count(eval(FLAG=="O")) as Outbound count(eval(FLAG=="I")) as Inbound dc(provider_name) as Providers
@mnikhil7692, did you get a chance to try out the answer?
Hi Niket, i did. i replied back on the post that i found the solution.
Thanks for all your help.
@mnikhil7692 what is the source of your data? Are you indexing spreadsheet data or any other data to Splunk or is it coming from CSV which has been uploaded to Splunk lookup file.
If it is indexed to Splunk what do the events look like? Do you have fields already extracted?
If the data shows up exactly the way you have displayed in your table, and there is one event per day, just the table
command should work.
Following is a run anywhere example which uses command from | makeresult
till | KV
to generate some sample events as per data. Then uses table to plot the table. You can change visualization to Line Chart in Splunk to see the result:
| makeresults
| eval data="CREATION_DATE_TIME=10/24/2018,TOTAL_PROCESS=96,DISTINCT_PROVIDERS=21,OUTGOING=38,INCOMING=58;CREATION_DATE_TIME=10/25/2018,TOTAL_PROCESS=96,DISTINCT_PROVIDERS=21,OUTGOING=37,INCOMING=59;CREATION_DATE_TIME=10/26/2018,TOTAL_PROCESS=95,DISTINCT_PROVIDERS=21,OUTGOING=37,INCOMING=58;"
| makemv data delim=";"
| mvexpand data
| rename data as _raw
| KV
| table CREATION_DATE_TIME DISTINCT_PROVIDERS INCOMING OUTGOING TOTAL_PROCESS
PS: Just to add here is the Splunk documentation for transitioning from SQL to SPL: http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/SQLtoSplunk
Hi Nilay, thanks for your quick response and the reference link form Sql to SPL.
My source data is in Oracle. please don't let my screenshot of the excel mis-guide you. I used that to only show how i want my final visualization to look like in Splunk.
The query is the one i am trying to translate into Splunk, so hard-coding the incomding, outgoing,, providers etc wont work. It has to be something that's generated dynamically.