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

[auaave]

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

[auaave]

@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. 🙂

[MuS · Accepted solution]

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:

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]

Should I answer it again ? 😉

[auaave]

@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