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
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...