Formatting output in table

[krusovice]

Hi all,

I've a request to come out with a table with information as below.

My query so far is to extract the required data from the log:

index=*  | search "$clicked_request$ - Country: $clicked_country$ - City: $City$ - Total: $total$"

Correct match with log data:

2018-02-21 01:50:22.600 INFO Request_A - Country: UK - City: LDN - Total: 120
2018-02-21 01:57:22.300 INFO Request_A - Country: UK - City: LDN - Total: 130
2018-02-21 02:00:22.390 INFO Request_A - Country: UK - City: MAN - Total: 100

However, I have the problem to format it into desired table format as below:

DATE         COUNTRY        CITY       DATE_HOUR          TOTAL
2018-02-21  UK          LDN              1         120
                                                1          130
                            MAN              2         100

How can I do this with the query?

Thanks in advance.

[deepashri_123 · Accepted solution]

Hey krusovoice,
Try something like this,
index=* | eval date=strftime(_time, "%Y-%m-%d") | stats values(total) AS total values(date_hour) AS date_hour by date,country,city

Let me know if this works!!
I am assuming that your fields are already extracted

[krusovice]

hi @deepashri_123,

You've given me some idea how to construct the stats command. I've added with eval prior to stats to extract the value of $$.

Would you mind to reply as answer to my post so that I've click "accept as answer"?

Thank you very much for your help.

[krusovice]

sorry, $$ should be :

$<token_name>$