Reporting

two searches in pivot

julz0815
Explorer

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?

0 Karma
1 Solution

DalJeanis
SplunkTrust
SplunkTrust

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

View solution in original post

DalJeanis
SplunkTrust
SplunkTrust

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

View solution in original post

julz0815
Explorer

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!

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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.

0 Karma

rjthibod
Champion

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.

0 Karma

julz0815
Explorer

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

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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

0 Karma

julz0815
Explorer

looking for counts of both to compare and show a percentage

0 Karma
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.