appendcols and line dates from 2 searches

[HattrickNZ]

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

[HattrickNZ · Accepted solution]

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.

[chimell]

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

[HattrickNZ]

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     

[somesoni2]

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

[HattrickNZ]

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

[HattrickNZ]

@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)

[somesoni2]

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

[HattrickNZ]

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"

[chimell]

can you send me your two searches ?

[somesoni2]

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

[HattrickNZ]

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