topic Re: two searches in pivot in Reporting

two searches in pivot

julz0815 — Tue, 29 Sep 2020 12:56:04 GMT

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?

Re: two searches in pivot

rjthibod — Mon, 20 Feb 2017 17:09:06 GMT

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.

Re: two searches in pivot

DalJeanis — Mon, 20 Feb 2017 18:51:15 GMT

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

Re: two searches in pivot

julz0815 — Tue, 29 Sep 2020 12:56:16 GMT

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

Re: two searches in pivot

julz0815 — Tue, 29 Sep 2020 12:56:22 GMT

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!

Re: two searches in pivot

DalJeanis — Mon, 20 Feb 2017 21:54:40 GMT

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

Re: two searches in pivot

julz0815 — Mon, 20 Feb 2017 22:14:46 GMT

looking for counts of both to compare and show a percentage

Re: two searches in pivot

DalJeanis — Tue, 21 Feb 2017 02:44:02 GMT

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.