Hi, I’m a new user of Splunk. From multi-site syslog-like data, I would like to get a table, each row is site-name(source file name) and each column is a stats result of the site. I tried appendcols [subsearch with “by source”] style search, but it shows an unexpected behavior. Help me to solve this problem. Below is a context (a simplified example sites-data and steps I took).
(1) DATA & SEARCH REQUIREMENT: A zip file consists of event data files from three sites, and is added to Spunk. Event data format includes a event time and Base station ID(BID). Below is an example of data from three sites (Site1, Site2, Site3).
Site1
Apr 18 02:17:31 | 48 |
Jul 23 08:04:09 | 4114 |
Mar 12 15:02:34 | 4116 |
Mar 12 16:53:16 | 4116 |
Site2
Jul 11 02:24:28 | 5221 |
Jul 11 23:31:03 | ==== Out of Service === |
Jul 11 23:31:25 | 36885 |
Jul 15 07:09:07 | 36888 |
Jul 15 07:09:48 | 36885 |
Mar 09 11:18:27 | ==== Out of Service === |
May 08 03:31:12 | 36885 |
Site3
Aug 01 04:13:24 | ==== Out of Service === |
Aug 01 04:13:45 | 5147 |
Jul 18 03:29:44 | ==== Out of Service === |
Jul 18 03:41:04 | 5144 |
Jun 07 15:09:32 | 5144 |
If BID has an integer, it means the site is “in-service (INS)” state and is connected through the BID base station of a cellular network. If AreaID has “Out of Service” phrase, it means the site is “Out of service(OUS)” state without a connection. Both INS and OUS state has its own duration, which is a time difference between its event time and the next earliest event time (therefore, for the last event, INS or OUS duration is undefined(=null).
I would like to calculate INS ratio (INS_ratio%) and perc50 of INS or OUS duration in each site.
(2) SEARCHES FOR ONE-SITE AND ITS RESULT: Before making a subsearch, I made Search-#1 that inputs from one site (file) and outputs a stats result. In Search-#1, by the table command , I checked an intermediate result of this site, that was what I expected.
Final output from the stats command was also correct (One example is shown as Result of Site3).
Search-#1 for Site3
index=testindex sourcetype=TestType source=*Site3*
| delta _time p=1 as EventDura | eval EventDura=abs(EventDura) | sort _time
| eval BSstatus=if(isint(BSID),"INS","OUS") | eval INSDura=if(BSstatus="INS",EventDura,null())
| eval OUSDura=if(BSstatus="OUS",EventDura,null())
| accum EventDura as TotalEventDura | accum INSDura as TotalINSDura | accum OUSDura as TotalOUSDura
| eval INS_Ratio%=if(isnull(OUSDura),100*TotalINSDura/TotalEventDura, 100*(1-TotalOUSDura/TotalEventDura))
| table _time BSID BSstatus EventDura TotalEventDura TotalINSDura INSDura TotalOUSDura OUSDura INS_Ratio%
| stats count as Sub_Count count(EventDura) last(TotalEventDura) last(TotalINSDura) last(TotalOUSDura) last(INS_Ratio%)
perc50(INSDura) perc50(OUSDura)
Example of stats output on Site3
Sub_Count count(EventDura) last(TotalEventDura) last(TotalINSDura) last(TotalOUSDura) last(INS_Ratio%)
5 4 4712653 4711952 701 99.985125
perc50(INSDura) perc50(OUSDura)
3500412 680
(3) TRANSFORMATION TO SUBSEARCH: By editing Search-#1 (delete the table command, add the search index=…, sourcetype=… at the beginning and by source at the end), it is added to the main search index=testindex sourcetype=TestType | stats count as Main_count by source with appendcols command (Search-#2).
Search-#2
index=testindex sourcetype=TestType | stats count as Main_count by source
| appendcols
[
search index=testindex sourcetype=TestType
| delta _time p=1 as EventDura | eval EventDura=abs(EventDura) | sort _time
| eval BSstatus=if(isint(BSID),"INS","OUS") | eval INSDura=if(BSstatus="INS",EventDura,null())
| eval OUSDura=if(BSstatus="OUS",EventDura,null())
| accum EventDura as TotalEventDura | accum INSDura as TotalINSDura
| accum OUSDura as TotalOUSDura
| eval INS_Ratio%=if(isnull(OUSDura),100*TotalINSDura/TotalEventDura,(1-TotalOUSDura/TotalEventDura)*100)
| stats count as Sub_count count(EventDura) last(TotalEventDura) last(TotalINSDura) last(TotalOUSDura)
last(INS_Ratio%) perc50(INSDura) perc50(OUSDura)
by source
]
(4) UNEXPECTED RESULT AND QUESTIONS: About output of Search-#2 (See attached image
), the structure of the table and values of “source”, “Main_count”, and “Sub_count” columns are fine, but the values of other columns are completely different from the correct result by search-#1 (for example, see the values of Site3).
Q1: It seems something is wrong in the sub search. How can I solve this problem?
Q2: Is there any debugging tools/command for the subsearch? For example, dumping table values source by source in the sub search.

Since your working example is only specific to one site, I'm assuming you want to do these calculations per site? So my suggestion would be to get Search 1 working across all site data, not just one site.
That said, I think you should be sorting by source before you do any of your delta calculations. Splunk will display results in reverse time by default. If you have data from all 3 sites in the result set, then you're likely calculating duration across different sites currently, not within one site. And that will throw off your numbers.
Along with sorting you'll also need to be aware of when your results move from one site to the next, because you won't want to use that delta. And i'm not sure logically if you need to sum up your duras by site as well. Maybe it would be easier to use stats or eventstats to sum them up by site?
So if I'm not completely misunderstanding the issue, something like this might be more what you expect. Probably not exactly right, but hopefully it'll point you in the right direction.
index=testindex sourcetype=TestType | stats count as Main_count by source
| appendcols
[
search index=testindex sourcetype=TestType
| sort source - _time
| delta _time p=1 as EventDura
| eval EventDura=abs(EventDura)
| autoregress source as prevSource p=1
| sort _time
| eval BSstatus=if(isint(BSID),"INS","OUS")
| eval INSDura=if(BSstatus="INS" AND source=prevSource,EventDura,null())
| eval OUSDura=if(BSstatus="OUS" AND source=prevSource,EventDura,null())
| accum EventDura as TotalEventDura | accum INSDura as TotalINSDura
| accum OUSDura as TotalOUSDura
| eval INS_Ratio%=if(isnull(OUSDura),100*TotalINSDura/TotalEventDura,(1-TotalOUSDura/TotalEventDura)*100)
| stats count as Sub_count count(EventDura) last(TotalEventDura) last(TotalINSDura) last(TotalOUSDura)
last(INS_Ratio%) perc50(INSDura) perc50(OUSDura)
by source
]
Since your working example is only specific to one site, I'm assuming you want to do these calculations per site? So my suggestion would be to get Search 1 working across all site data, not just one site.
That said, I think you should be sorting by source before you do any of your delta calculations. Splunk will display results in reverse time by default. If you have data from all 3 sites in the result set, then you're likely calculating duration across different sites currently, not within one site. And that will throw off your numbers.
Along with sorting you'll also need to be aware of when your results move from one site to the next, because you won't want to use that delta. And i'm not sure logically if you need to sum up your duras by site as well. Maybe it would be easier to use stats or eventstats to sum them up by site?
So if I'm not completely misunderstanding the issue, something like this might be more what you expect. Probably not exactly right, but hopefully it'll point you in the right direction.
index=testindex sourcetype=TestType | stats count as Main_count by source
| appendcols
[
search index=testindex sourcetype=TestType
| sort source - _time
| delta _time p=1 as EventDura
| eval EventDura=abs(EventDura)
| autoregress source as prevSource p=1
| sort _time
| eval BSstatus=if(isint(BSID),"INS","OUS")
| eval INSDura=if(BSstatus="INS" AND source=prevSource,EventDura,null())
| eval OUSDura=if(BSstatus="OUS" AND source=prevSource,EventDura,null())
| accum EventDura as TotalEventDura | accum INSDura as TotalINSDura
| accum OUSDura as TotalOUSDura
| eval INS_Ratio%=if(isnull(OUSDura),100*TotalINSDura/TotalEventDura,(1-TotalOUSDura/TotalEventDura)*100)
| stats count as Sub_count count(EventDura) last(TotalEventDura) last(TotalINSDura) last(TotalOUSDura)
last(INS_Ratio%) perc50(INSDura) perc50(OUSDura)
by source
]
Hi maciep,
thanks for your answer and sorry for the late response.
Your understanding is right and your suggestion, autoregress source as prevSource p=1, helps great!
By using it, I can easily find the source boundaries where I can select eval conditions.
It seems that appendcols [subsearch with “by source”] style search is not necessarily adequate for this
type of applications; calculations per site from per site source files.
I think I am almost near the goal and when I finish, I will report again.
Thanks again.