topic Re: appendcols and line dates from 2 searches in Splunk Search

appendcols and line dates from 2 searches

HattrickNZ — Thu, 24 Mar 2016 01:35:23 GMT

i have 2 searches

1 gives me this:

    _time   kpi1    predictkpi1
1   2010-09 179.539643   
2   2010-10 239.270968   
3   2010-11 307.206667  299.002293
4   2010-12 405.039032  370.322797954
...
96  2018-08     21813.7910836
97  2018-09     22041.520084

2 gives me this

    _time   kpi2    predictkpi2
1   2015-07 5761.043548  
2   2015-08 6226.990968  
3   2015-09 6439.014667 6692.938388
....
37  2018-07     27442.3650311
38  2018-08     28051.5290532
39  2018-09     28660.6930754

I am using appendcols to append them together but dates are not lining up. for instance 2010-09 lines up with 2015-07 row. ideall y want 2015-07 to line up with 2015-07. How can I get the dates to lineup?

EDID1

Re: appendcols and line dates from 2 searches

somesoni2 — Thu, 24 Mar 2016 05:54:20 GMT

Thats the default behaviour of appendcols (just appending columns row by row without any comparison/matching). For your scenario, try this

search1 | append [search search2] | stats values(*) as * by _time

More details on append/appendcols functionality
https://answers.splunk.com/answers/144351/what-are-the-differences-between-append-appendpipe.html

Re: appendcols and line dates from 2 searches

chimell — Thu, 24 Mar 2016 11:50:46 GMT

can you send me your two searches ?

Re: appendcols and line dates from 2 searches

chimell — Thu, 24 Mar 2016 12:36:13 GMT

Hi
put your search in this form using appendcols command

index=_internal|eval date1=strftime(_time, "%Y-%m")|stats count(sourcetype) as count1 by date1 , source|table date1 source count1 |appendcols[search index=_* and index!=_internal|eval date2=strftime(_time, "%Y-%m")|stats count(user) as count2 by date2 , sourcetype|table date2 sourcetype count2]

Look at result

Re: appendcols and line dates from 2 searches

HattrickNZ — Mon, 28 Mar 2016 21:36:39 GMT

this gives me the below: but maybe i am missing something. how do i allign dat1 and date2?

date1   source  count1  count2  date2   sourcetype
1   2016-02 C:\Apps\Splunk\var\log\splunk\conf.log  1   0   2016-03 kvstore
2   2016-02 C:\Apps\Splunk\var\log\splunk\metrics.log   63857            
3   2016-02 C:\Apps\Splunk\var\log\splunk\mongod.log    150          
4   2016-02 C:\Apps\Splunk\var\log\splunk\scheduler.log 39           
5   2016-02 C:\Apps\Splunk\var\log\splunk\splunkd-utility.log   13           
6   2016-02 C:\Apps\Splunk\var\log\splunk\splunkd.log   853          
7   2016-02 C:\Apps\Splunk\var\log\splunk\splunkd_access.log    396          
8   2016-02 C:\Apps\Splunk\var\log\splunk\splunkd_ui_access.log 24763            
9   2016-02 C:\Apps\Splunk\var\log\splunk\web_access.log    226          
10  2016-02 C:\Apps\Splunk\var\log\splunk\web_service.log   309          
11  2016-03 C:\Apps\Splunk\var\log\splunk\conf.log  29           
12  2016-03 C:\Apps\Splunk\var\log\splunk\metrics.log   1601158          
13  2016-03 C:\Apps\Splunk\var\log\splunk\metrics.log.1 293          
14  2016-03 C:\Apps\Splunk\var\log\splunk\mongod.log    2822             
15  2016-03 C:\Apps\Splunk\var\log\splunk\scheduler.log 1030             
16  2016-03 C:\Apps\Splunk\var\log\splunk\splunkd-utility.log   377          
17  2016-03 C:\Apps\Splunk\var\log\splunk\splunkd.log   15553            
18  2016-03 C:\Apps\Splunk\var\log\splunk\splunkd_access.log    4491             
19  2016-03 C:\Apps\Splunk\var\log\splunk\splunkd_ui_access.log 113037           
20  2016-03 C:\Apps\Splunk\var\log\splunk\web_access.log    922          
21  2016-03 C:\Apps\Splunk\var\log\splunk\web_service.log   6913

Re: appendcols and line dates from 2 searches

somesoni2 — Mon, 28 Mar 2016 21:47:29 GMT

The appendcols will only be useful if the order of the common column (and name) is same in both result set. Which clearly not the case for your resultset. You can use append-stats combination, as suggested in my answer OR can use less efficient option of | join type=outer

Re: appendcols and line dates from 2 searches

HattrickNZ — Mon, 28 Mar 2016 21:51:53 GMT

I could not get this to work.

the order of the searches give a different result, but ultimately the dates did not align. I try to explain the results below.

search1 - 2010-09 to future date
search2 - 2015-07 to future date

search1 | append [search search2] | stats values(*) as * by _time gives 2010-09 to future date values with 2015-07 from search 2 alligned with 2010-09 from search1

search2 | append [search search1] | stats values(*) as * by _time gives 2015-07 to future date values with 2010-09 from search 1 alligned with 2015-07 from search2

Re: appendcols and line dates from 2 searches

HattrickNZ — Mon, 28 Mar 2016 22:26:38 GMT

maybe I should use join? how do I join using the _time column from the 2 searches to line up?

Re: appendcols and line dates from 2 searches

HattrickNZ — Tue, 29 Mar 2016 00:43:57 GMT

@somesoni2

got this to work
search1 | join type=outer _time [search search2] |

the problem now that the chart is not doing a stacked area chart for all 4 series in the graph...sigh!!

(see my edit1 in question above)

Re: appendcols and line dates from 2 searches

somesoni2 — Tue, 29 Mar 2016 23:06:35 GMT

If the join has worked, the append-stats should've worked too 😞

Re: appendcols and line dates from 2 searches

HattrickNZ — Thu, 31 Mar 2016 20:37:16 GMT

tks, I got that to work now search1 | append [search search2] | stats values(*) as * by _time and the graphing for some reason works better compared with the join option(a q i asked here). which leads me to another question "can i have a chartoverlay, with 2 serieses, stacked in splunk graph"

Re: appendcols and line dates from 2 searches

HattrickNZ — Thu, 31 Mar 2016 20:56:07 GMT

credit to @somesoni2 and @chimell above for getting this to work

NOTE

search1 | append [search search2] | stats values(*) as * by _time gives (this seems more better at doing charting, stackeing for example, compared to the one below. It also does not show the year along the axis as well as the one below).

search1 | join type=outer _time [search search2] | gives

Interesting that they give the same results but the graphing capability seems a bit different.