topic Re: Dynamic Table header by incremental values. in Dashboards & Visualizations

Dynamic Table header by incremental values.

email2vamsi — Fri, 08 May 2020 09:55:38 GMT

Hello Experts,

I have attached two images.
The following search returns results as shown in "CURRENT" image.
But i would like to have results as per "DESIRED" image.
Could you please help.

base search from JSON..
| stats values(date1) by hostname
| rename values(date1) AS date1
| stats list(hostname) AS hostname by date1
| xyseries hostname date1 date1

Thank you.

Re: Dynamic Table header by incremental values.

to4kawa — Fri, 08 May 2020 10:45:42 GMT

base search from JSON..
| stats values(_time) as date1 by hostname
| eval counter=mvrange(1,mvcount(date1)+1)
| mvexpand counter
| eval col{counter}=mvindex(date1,counter-1)
| stats values(*) as * by hostname
| foreach col* [eval <<FIELD>>=strftime('<<FIELD>>',"%F %T")]
| fields - counter date1

Re: Dynamic Table header by incremental values.

email2vamsi — Fri, 08 May 2020 11:14:42 GMT

Each date value should come in a separate column as shown in the "DESIRED" image above.
Your query is showing all the dates in once single column.
Thank you.

Re: Dynamic Table header by incremental values.

to4kawa — Fri, 08 May 2020 12:18:44 GMT

I see. check update my answer

Re: Dynamic Table header by incremental values.

email2vamsi — Fri, 08 May 2020 17:29:28 GMT

Hi,
Your query returns all the dates in header and all hotnames in one single long row.
It wouldn't suit the requirement.

As shown in the "DESIRED" image of the original message,i would like to have all hostname is first column and later followed by dates columns. The column header for dates should be like col1,col2,col3 etc generated dynamically based on count of dates.

Thank you.

Re: Dynamic Table header by incremental values.

to4kawa — Fri, 08 May 2020 22:50:43 GMT

Sorry, I'm not going to do the quiz, so please fix it.

Re: Dynamic Table header by incremental values.

niketn — Mon, 11 May 2020 09:58:57 GMT

@email2vamsi as per the data/details provided seems like your use case is that Daily you will have one host reported only once and you want per host the time at which the host reported daily.

If the above is your use case, can you try the following

base search from JSON..
| eval Col=strftime(_time,"%Y-%m-%d"),date1=strftime(_time,"%Y-%m-%d %H:%M:%S")
| chart latest(date1) as date1 by hostname Col

Following is a run anywhere search based on Splunk's _internal index:

index=_internal sourcetype=splunkd
| eval Col=strftime(_time,"%Y-%m-%d"),date1=strftime(_time,"%Y-%m-%d %H:%M:%S")
| chart latest(date1) as date1 by log_level Col

Please try out and confirm!

Re: Dynamic Table header by incremental values.

email2vamsi — Mon, 11 May 2020 10:47:29 GMT

Hi,

Thank you for your reply.
As shown in the image in my original post, i want to transform results from "PRESENT" to "DESIRED".
There will will 100's of hostname and each hostname will be have date entries for 30 days.
Each host can have date/time from same day and for the last 30 days needs to fetched and showed in the in "DESIRED" format.

If a hostname has 45 dates for the last 30 days, then we need to generate 45 columns dynamically .

Thank you.

Re: Dynamic Table header by incremental values.

niketn — Mon, 11 May 2020 11:18:15 GMT

@email2vamsi your comment above If a hostname has 45 dates for the last 30 days is not obvious as per the original question or screenshot. More than one event per host per day means you will have multi-value result.

All you need to change in my query is latest(date1) should be changed with values(date1).

Also whether you have 1 host, 100s or 1000s or more hosts they will be handled by by hostname clause of chart command.

On second note, using Col as the Date, you will always have 1 column per day, if you query last 30 days you will get 30 columns and if you query last 7 days you will get 7 columns.

Just change latest() aggregation with values() aggregation in my comment above and confirm if your issue is resolved.

Re: Dynamic Table header by incremental values.

email2vamsi — Wed, 30 Sep 2020 05:28:54 GMT

Hi niketnilay,

Please find the actual problem below:
With limit=0 in chart , the chart displays all the available values of taken_date in each row by hostname.
The header will have the corresponding taken_date value for each column.
But I would like to display 30 columns (limit=30) . Columns header should be in descending order of taken_date.
Now unlimited columns are displayed from oldest of taken_date to the latest of the taken_date .

Current search with problem:
base search from JSON..
| eval row1=strptime(taken_date,"%b %d %Y %H:%M:%S:%3N%p")
| eval col1=strptime(taken_date,"%b %d %Y %H:%M:%S")
| chart limit=0 values(row1) as date1 by hostname col1 
| fillnull value=NULL

So from the below query, i am trying to get 30 days older to latest value of taken_date in chart.
Here the latest of taken_date can be a week old. The 30 days old date can six months old.
The date&time functions which work only on _time and current time(now()) etc, would not work in this context.

In the below query i am expecting the values from 30 days old of taken_date to latest of taken_date.
30 days old date is derived by substracting 30 days from latest of taken_date.
latest of taken_date can be a week old,2 weeks old or any.

base search from JSON..
| eval row1=strptime(taken_date,"%b %d %Y %H:%M:%S:%3N%p")
| eval col1=strptime(taken_date,"%b %d %Y %H:%M:%S")
| stats max(row1) as max_row1 by row1 hostname max(col1) as max_col1 by col1
| eval max_row1_30= max_row1-2629743 
| eval max_col1_30= max_col1-2629743 
| where row1 > max_row1_30 AND where col1 > max_col1_30
| chart limit=0 values(row1) as date1 by hostname col1 
| fillnull value=NULL

Thank you.