two searches in pivot

[julz0815]

I have index=webserver_logs and source=security_logs and can search both in a single query:
index=webserver_logs | append [search source=security_logs]
I get a table with all events and just select webserver_logs.status and security_logs.id

But If I want to build a column chart with split rows = _time and column values count of status and count of id the table looks exactly what I need. The column chart will only display one of the values. As soon I try to add another colour to the Y-Axis only one of the values is graphed. Using both as a single value the graph is just fine.

How do I get them both displayed together?

[DalJeanis · Accepted solution]

I suspect you are not actually using a pivot. Also, you don't have to use append for that query, just use "OR" in the initial search. This strategy avoids the limit of 50K records returned from a subsearch.

(index=webserver_logs) OR (index=* AND source=security_logs)
| eval status=case(index==webserver_logs,status) 
| eval id=case(source==security_logs,id) 
| table _time status id
| timechart count(status) as statuscount count(id) as idcount

This returns data summed by time that you can use in a stacked bar chart or a line chart.

---

Below is some code that generates test data that I used to check my work above.

| gentimes start="01/25/2017:23:00:00" end="01/27/2017:01:00:00" increment=23m 
| eval status="A"
| append
    [| gentimes start="01/26/2017:03:00:00" end="01/26/2017:21:00:00" increment=47m 
    | eval status="B"]
| append
    [| gentimes start="01/26/2017:01:17:00" end="01/26/2017:23:18:00" increment=21m 
    | eval status="C"]
| append
    [| gentimes start="01/26/2017:03:00:00" end="01/26/2017:14:00:00" increment=19m 
    | streamstats count as id ]
| append
    [| gentimes start="01/26/2017:01:41:00" end="01/26/2017:23:18:00" increment=55m 
    | streamstats count as id ]
| bin starttime span=1h 
| rename starttime as _time
| timechart count(status) as statuscount count(id) as idcount

[julz0815]

interestingly statuscount and idcount always show 0, the amount of events found is ok as well the time column seem to show the correct values.
Removing the eval parts seems to do the trick, I guess as there is most probably data in webserver_logs but not in security_logs the same time. And visualization works like a charm as well.

Thanks for helping!

[DalJeanis]

np. if removing the evals worked, then great! that case statement should work with a table command and stats... but technically, by my own test code below, you didn't need any of those three lines 2-4 in my first sample.

[rjthibod]

It is unclear where you doing the search you describe. Can you share the actual pivot query you are running.

The raw search query you posted is not part of Pivot or a datamodel in Splunk and could not be allowed as one because you cannot use things like append in a datamodel definition.

[julz0815]

from search index=webserver_logs | append [search source=security_logs] | table _time status id I was going to tab visualisation and then choosing column bar

[DalJeanis]

Are you looking for the values of id and status, or counts, or what?

[julz0815]

looking for counts of both to compare and show a percentage