How to edit my search to get a tabular report?

[ravichandran]

When I try the following with last 30 days in the search I run into problems:

SourceName="sname" Message="**" | bucket span=1d _time | convert timeformat="%e %b" ctime(_time) AS c_time | chart count over SourceName by c_time useother=f

1. I get the results truncated. It shows only few dates. But when i reduce it to 7 days it works properly. Am I missing basic stuff.
2. Is there a way to count over combined columns? like count over(sourcename, Message)?

3. I want to Append multiple sourcename and message and have it piped into one search where I want to see the below results day-wise:

   ExceptionName Day1 day2 day3
   Exception1 10 100 200
   Exception2 10 100 200
   Total 30 200 400

Thank you in advance

[ravichandran]

The limit=0 column level total works. But when I add the eval for combining the columns the table is not showing up. Also trying to sort the dates not working.

SourceName="" Message="" | bucket span=1d _time | convert timeformat="%e %b" ctime(_time) AS c_time | chart count over SourceName by c_time limit=0 useother=f | addcoltotals labelfield=SourceName | sort _time

[ravichandran]

I am almost there. The eval is giving the result as
WinEventLog:Application-eventviwer. But I want it as Message.
SourceName="xxx" Message="ErrMsg" | bucket span=1d _time | convert timeformat="%m/%d" ctime(_time) AS c_time | eval sourcetype=sourcetype.coalesce("-".index,"Message") | chart count over sourcetype by c_time limit=0 useother=f

Desired Output:
Error Message 11/4 11/5
ErrMsg 100 100

[ravichandran]

I am able to work around the display message by
SourceName="" Message="msg" | bucket span=1d _time | convert timeformat="%m/%d" ctime(_time) AS c_time | strcat " " " " "msg" ErrorMessage| chart count over ErrorMessage by c_time limit=0 useother=f | addcoltotals
I have multiple Messages to search and i am doing the crude way of executing the above by replacing the Message="" and strcat. Is there a way to append searches. When I tried join it overwrites the first search.

SourceName="" Message="Msg1" | bucket span=1d _time | convert timeformat="%m/%d" ctime(_time) AS c_time | strcat " " " " "Msg1" ErrorMessage| chart count over ErrorMessage by c_time limit=0 useother=f | addcoltotals

join id [search SourceName="" Message="Msg2"| bucket span=1d _time | convert timeformat="%m/%d" ctime(_time) AS c_time | strcat " " " " "Msg2" ErrorMessage1| chart count over ErrorMessage1 by c_time limit=0 useother=f ]

[somesoni2]

Just do an append instead of join. Ensure that both searches return same field name(s).

first search | append [second search] 

[somesoni2]

For combining columns, ensure that both the fields that you're combining are available in all the events else concatenation of null values results in null. Another option would be to use coalesce function of eval to replace null values with blank.

e.g. (runanywhere sample)

index=_internal ERROR | bucket span=3m _time | convert timeformat="%e %b" ctime(_time) AS c_time  | eval sourcetype=sourcetype.coalesce("-".index,"") | chart count over sourcetype by c_time  | addcoltotals labelfield=sourcetype

For the date sorting, "| sort _time" will not work as there is no _time field available after your chart. Moreover, the dates have been transformed as columns so you can't sort them directly and your date format is "%e %b" where dates is followed by string month and the sorting will be inconsistent (if time range spread across months). Workaround for this would be to use numerical month, that too at the start. e.g. "%m %d". If you want to show month string as well try this "%m(%b) %d"

[somesoni2 · Accepted solution]

Try this

1) Truncation issue - add limit=0

SourceName="sname" Message="**" | bucket span=1d _time | convert timeformat="%e %b" ctime(_time) AS c_time | chart count over SourceName by c_time useother=f limit=0 

2) Combining multiple columns

SourceName="sname" Message="**" | bucket span=1d _time | convert timeformat="%e %b" ctime(_time) AS c_time  | eval SourceName=SourceName."-".Message| chart count over SourceName by c_time useother=f limit=0 

3) Adding column level total.

SourceName="sname" Message="**" | bucket span=1d _time | convert timeformat="%e %b" ctime(_time) AS c_time  | eval SourceName=SourceName."-".Message| chart count over SourceName by c_time useother=f limit=0  | addcoltotals labelfield=SourceName