topic How do I print last 2 columns in a line and do a line chart on those values in Splunk Search

How do I print last 2 columns in a line and do a line chart on those values

chandukreddi — Mon, 09 Nov 2020 20:31:27 GMT

Hello Tem,

I have log like below and I want to extract 3 fields and its values like below and do a line chart for top 20 tables which has higher numbers.

TableName: test.table1

Ops:10

data:30

Log:

INFO [Service Thread] 2020-11-09 19:22:13,294 StatusLogger.java:98 - Table Memtable ops,data
INFO [Service Thread] 2020-11-09 19:22:13,294 StatusLogger.java:101 - test.table1 10,30
INFO [Service Thread] 2020-11-09 19:22:13,294 StatusLogger.java:101 -test.table2 10000,99999999

Re: How do I print last 2 columns in a line and do a line chart on those values

bowesmana — Mon, 09 Nov 2020 23:05:52 GMT

@chandukreddi

This should get you started

| makeresults | eval event="INFO [Service Thread] 2020-11-09 19:22:13,294 StatusLogger.java:98 - Table Memtable ops,data$INFO [Service Thread] 2020-11-09 19:22:13,294 StatusLogger.java:101 - test.table1 10,30$INFO [Service Thread] 2020-11-09 19:22:13,294 StatusLogger.java:101 - test.table2 10000,99999999" | eval event=split(event,"$") | mvexpand event | rex field=event "(?<level>\w+) (?<thread>\[[^\]]+\]) (?<date>\d+-\d+-\d+ \d+:\d+:\d+,\d+) (?<file>[^:]*):(?<line_number>\d+) - (?<table>[^ ]*) (?<ops>\w+),(?<data>\w+)" | where !isnull(table) | eval _time=strptime(date,"%F %T,%Q") | timechart span=1d limit=20 max(ops) as maxops by table

This is all setting up your example data and then running a timechart.

It was not clear if you wanted a report over time on the x axis, or some other x axis. For example if you want the table to be on the x axis, use this

| chart max(ops) as maxops max(data) as data by table | sort - maxops | head 20

Note that this will take the highest 20 ops, not data, so adjust as you need.

Also, if you are plotting ops and data on the same chart, you would need to use a second y axis for the second data point, given it is such a different scale to ops.

Re: How do I print last 2 columns in a line and do a line chart on those values

chandukreddi — Tue, 10 Nov 2020 15:46:36 GMT

Thanks @bowesmana

let me try and get back to you

Re: How do I print last 2 columns in a line and do a line chart on those values

chandukreddi — Tue, 10 Nov 2020 20:23:32 GMT

I have tried like below but it returned nothing

source=/var/log/cassandra/system.log index=cassdb_perf StatusLogger.java:101 | rex field=Event "(?<level>\w+) (?<thread>\[[^\]]+\]) (?<date>\d+-\d+-\d+ \d+:\d+:\d+,\d+) (?<file>[^:]*):(?<line_number>\d+) - (?<table>[^ ]*) (?<ops>\w+),(?<data>\w+)"| where !isnull(table)
| eval _time=strptime(date,"%F %T,%Q")
| timechart span=1d limit=20 max(ops) as maxops by table

All I want is tablename , maxops, data as table columns so that I can sort the table columns

Thanks

Chandra

Re: How do I print last 2 columns in a line and do a line chart on those values

bowesmana — Tue, 10 Nov 2020 21:46:37 GMT

@chandukreddi If it returns nothing, then it is most likely because your data does not match the rex statement.

In your original example post, you had slightly different formats (missing space after '-' in one line), so you will need to check your data to see how it matches the regex, or post your exact data here, so we can check it.

Re: How do I print last 2 columns in a line and do a line chart on those values

chandukreddi — Thu, 12 Nov 2020 16:45:49 GMT

Hi @bowesmana ,

Here is the sample data.

INFO [Service Thread] 2020-11-12 15:01:51,663 StatusLogger.java:98 - Table Memtable ops,data
INFO [Service Thread] 2020-11-12 15:01:51,663 StatusLogger.java:101 - pqs_ca_e2e.au_report 0,0
INFO [Service Thread] 2020-11-12 15:01:51,664 StatusLogger.java:101 - pqs_ca_e2e.au_product 0,0
INFO [Service Thread] 2020-11-12 15:01:51,664 StatusLogger.java:101 - pqs_ca_e2e.au_audience_type 0,0
INFO [Service Thread] 2020-11-12 15:01:51,664 StatusLogger.java:101 - pqs_ca_e2e.au_version 0,0
INFO [Service Thread] 2020-11-12 15:01:51,664 StatusLogger.java:101 - taskservice_dev.tasks_by_duedate 0,0
INFO [Service Thread] 2020-11-12 15:01:51,664 StatusLogger.java:101 - taskservice_dev.tasks_by_type 0,0
INFO [Service Thread] 2020-11-12 15:01:51,664 StatusLogger.java:101 - taskservice_dev.task_actions 0,0
INFO [Service Thread] 2020-11-12 15:01:51,664 StatusLogger.java:101 - taskservice_dev.tasks_by_client 0,0
INFO [Service Thread] 2020-11-12 15:01:51,664 StatusLogger.java:101 - taskservice_dev.tasks_by_createddate 0,0
INFO [Service Thread] 2020-11-12 15:01:51,664 StatusLogger.java:101 - taskservice_dev.configuration_parameter 0,0
INFO [Service Thread] 2020-11-12 15:01:51,664 StatusLogger.java:101 - taskservice_dev.tasks_by_noduedate 0,0
INFO [Service Thread] 2020-11-12 15:01:51,664 StatusLogger.java:101 - taskservice_dev.tasks_by_assigned 0,0

Thanks

Chandra

Re: How do I print last 2 columns in a line and do a line chart on those values

chandukreddi — Thu, 12 Nov 2020 16:47:24 GMT

some more data which

INFO [Service Thread] 2020-11-12 15:01:51,674 StatusLogger.java:101 - qa.lookup 91,53257

INFO [Service Thread] 2020-11-12 15:01:51,685 StatusLogger.java:101 - data_e2estatus 416,69936

Re: How do I print last 2 columns in a line and do a line chart on those values

chandukreddi — Thu, 12 Nov 2020 17:12:38 GMT

But this thing is working as you mentioned but when I query index it's not working as expected.

| makeresults
| eval event="INFO [Service Thread] 2020-11-12 15:01:51,674 StatusLogger.java:101 - qa.lookup 91,53257$INFO [Service Thread] 2020-11-12 15:01:51,685 StatusLogger.java:101 - data_e2estatus 416,69936"
| eval event=split(event,"$")
| mvexpand event
| rex field=event "(?<level>\w+) (?<thread>\[[^\]]+\]) (?<date>\d+-\d+-\d+ \d+:\d+:\d+,\d+) (?<file>[^:]*):(?<line_number>\d+) - (?<table>[^ ]*) (?<ops>\w+),(?<data>\w+)"
| where !isnull(table)
| eval _time=strptime(date,"%F %T,%Q")
| timechart span=1d limit=20 max(ops) as maxops by table

Re: How do I print last 2 columns in a line and do a line chart on those values

chandukreddi — Mon, 16 Nov 2020 21:25:36 GMT

Can anyone help me with this?

Re: How do I print last 2 columns in a line and do a line chart on those values

somesoni2 — Mon, 16 Nov 2020 21:42:41 GMT

Try something like this:

source=/var/log/cassandra/system.log index=cassdb_perf StatusLogger.java:101 | rex "StatusLogger\.java\:\d+\s+-\s+(?<TableName>\S+)\s+(?<ops>\d+),(?<data>\d+)$" | where isnotnull(data) | table TableName ops data