Reporting

two searches in pivot

Explorer

I have index=webserverlogs and source=securitylogs and can search both in a single query:
index=webserverlogs | append [search source=securitylogs]
I get a table with all events and just select webserverlogs.status and securitylogs.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

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

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

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 webserverlogs but not in securitylogs the same time. And visualization works like a charm as well.

Thanks for helping!

0 Karma

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

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

Explorer

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

0 Karma

SplunkTrust
SplunkTrust

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

0 Karma

Explorer

looking for counts of both to compare and show a percentage

0 Karma