Dashboards & Visualizations

How to create a dashboard with information coming from different sources?

auaave
Communicator

Hi Guys,

I need to create a dashboard with the below information. I am having a hard time doing it because the information are coming from 4 different sources. Please help. Thank you.

Table:
Order-------Planned-----Shipped------Ontime------%Ontime
Type 1----------8----------------6--------------- 5------------83.33%
Type 2---------10-------------- 10------------- 10---------- 100%
Total (1&2)---18---------------16--------------15-----------93.75%

Source:
Source 1 - Order & Processing
Source 2 - Order & Status & Shipped Time
Source 3- Order & Type
Source 4- Order & required time to ship

Req:
Planned= count all orders from source 1 with "processing"=ok by "type" from source 3
Finished= count all orders from source 2 with "status"=shipped by "type" from source 3
onTime= count the orders if "shiptime" from source 2 is less than "required ship time" from source 4  by "type" from source 3
%ontime= ontime/shipped
Then add the row with total
0 Karma
1 Solution

MuS
Legend

Hi auaave,

Your events contain time stamps that are not usable with any of the http://docs.splunk.com/Documentation/Splunk/7.0.2/SearchReference/Commontimeformatvariables switches (Upper case month is the major problem!) So, this needs to get fixed manually using rex and some eval before you can even think about using strtime() on the events. By cleaning the timestamps and normalising ORDERNO you can get a nice table with this search:

your base search here ( source="WCT_4DELIVERY_ORDER_STATUS.csv" STATUS="finished" ) OR ( source=WCT_4ORDER_PLANNED.csv PLANNINGRESULT=OK ) OR ( source="ORDER_PROCESSED.csv" ) OR ( source="ORDER_RECEIVED.csv" ) 
| fields DELIVERYORDER PLANNINGRESULT EVENTTS ORDERNO STATUS DELIVERYORDER SIOFLAG RFT source 
| rex field=DELIVERYORDER "-(?<ORDERNO>\d+)" 
| rex field="EVENTTS" "^(?<ts_1>\d{2}\/[A-Z])" 
| rex field="EVENTTS" "^\d{2}\/[A-Z](?<ts_2>[A-Z]{2})" 
| rex field="EVENTTS" "^\d{2}\/[A-Z][A-Z]{2}(?<ts_3>.+)" 
| rex field="RFT" "^(?<rft_ts_1>\d{2}\/[A-Z])" 
| rex field="RFT" "^\d{2}\/[A-Z](?<rft_ts_2>[A-Z]{2})" 
| rex field="RFT" "^\d{2}\/[A-Z][A-Z]{2}(?<rft_ts_3>.+)" 
| eval ts_2=lower(ts_2), ts_4=ts_1 ."". ts_2 ."". ts_3 
| eval rft_ts_2=lower(rft_ts_2), rft_ts_4=rft_ts_1 ."". rft_ts_2 ."". rft_ts_3 
| eval ftime=strptime(ts_4, "%d/%b/%y %H:%M:%S.%3N") 
| eval rft_time=strptime(rft_ts_4, "%d/%b/%y %H:%M:%S.%3N") 
| table ORDERNO PLANNINGRESULT RFT rft_time SIOFLAG STATUS ftime EVENTTS 
| stats values(*) AS * by ORDERNO ftime

The results look like this:

alt text

These results can be further processed by comparing rtf_time < ftime for example or what ever you want to do with them.

Hope this helps ...

cheers, MuS

View solution in original post

auaave
Communicator

@MuS, thanks a lot for help! Now I know why I am getting confused, I was trying to add the information from each source using "appendcols" when I can search all the sources and create the table in one go. I just appended the SIOFLAG because I need to chart the events by SIOFLAG. Appreciate all your help. 🙂

0 Karma

MuS
Legend

Hi auaave,

Your events contain time stamps that are not usable with any of the http://docs.splunk.com/Documentation/Splunk/7.0.2/SearchReference/Commontimeformatvariables switches (Upper case month is the major problem!) So, this needs to get fixed manually using rex and some eval before you can even think about using strtime() on the events. By cleaning the timestamps and normalising ORDERNO you can get a nice table with this search:

your base search here ( source="WCT_4DELIVERY_ORDER_STATUS.csv" STATUS="finished" ) OR ( source=WCT_4ORDER_PLANNED.csv PLANNINGRESULT=OK ) OR ( source="ORDER_PROCESSED.csv" ) OR ( source="ORDER_RECEIVED.csv" ) 
| fields DELIVERYORDER PLANNINGRESULT EVENTTS ORDERNO STATUS DELIVERYORDER SIOFLAG RFT source 
| rex field=DELIVERYORDER "-(?<ORDERNO>\d+)" 
| rex field="EVENTTS" "^(?<ts_1>\d{2}\/[A-Z])" 
| rex field="EVENTTS" "^\d{2}\/[A-Z](?<ts_2>[A-Z]{2})" 
| rex field="EVENTTS" "^\d{2}\/[A-Z][A-Z]{2}(?<ts_3>.+)" 
| rex field="RFT" "^(?<rft_ts_1>\d{2}\/[A-Z])" 
| rex field="RFT" "^\d{2}\/[A-Z](?<rft_ts_2>[A-Z]{2})" 
| rex field="RFT" "^\d{2}\/[A-Z][A-Z]{2}(?<rft_ts_3>.+)" 
| eval ts_2=lower(ts_2), ts_4=ts_1 ."". ts_2 ."". ts_3 
| eval rft_ts_2=lower(rft_ts_2), rft_ts_4=rft_ts_1 ."". rft_ts_2 ."". rft_ts_3 
| eval ftime=strptime(ts_4, "%d/%b/%y %H:%M:%S.%3N") 
| eval rft_time=strptime(rft_ts_4, "%d/%b/%y %H:%M:%S.%3N") 
| table ORDERNO PLANNINGRESULT RFT rft_time SIOFLAG STATUS ftime EVENTTS 
| stats values(*) AS * by ORDERNO ftime

The results look like this:

alt text

These results can be further processed by comparing rtf_time < ftime for example or what ever you want to do with them.

Hope this helps ...

cheers, MuS

MuS
Legend

Should I answer it again ? 😉

0 Karma

auaave
Communicator

@MuS, this is a different case. now I need to look at the order to see if I should be counting it.
Here is what I did and it's giving me a wrong count.

So first, I chart everything to populate the information per order, then I did the count. I got wrong result 😞
Order - planningresult-rft-sioflag-finish-ftime. Also, I can't add a column that will tell me if ftime < rft.

| rex field=DELIVERYORDER "(\-(?<ORDERNO>[0-9]+))" 
| table ORDERNO PLANNINGRESULT 
| appendcols 
    [| search source="WCT_4DELIVERY_ORDER_STATUS" 
    | eval ftime=strptime(EVENTTS,"%Y-%m-%d %H:%M:%S") 
    | eval finish="OK" 
    | table ORDERNO ftime finish] 
| appendcols 
    [ search source="ORDER_PROCESSED.csv" 
    | rename DELIVERYORDER as ORDERNO 
    | table ORDERNO SIOFLAG] 
| appendcols 
    [ search source="ORDER_RECEIVED.csv" 
    | table ORDERNO RFT] 
| dedup ORDERNO | chart count(eval(PLANNINGRESULT)) as plan count(eval(finish)) as finish by SIOFLAG
0 Karma
Get Updates on the Splunk Community!

Introducing the 2024 SplunkTrust!

Hello, Splunk Community! We are beyond thrilled to announce our newest group of SplunkTrust members!  The ...

Introducing the 2024 Splunk MVPs!

We are excited to announce the 2024 cohort of the Splunk MVP program. Splunk MVPs are passionate members of ...

Splunk Custom Visualizations App End of Life

The Splunk Custom Visualizations apps End of Life for SimpleXML will reach end of support on Dec 21, 2024, ...