Splunk Search

timechart command changing my column data

prathapkcsc
Explorer

HI Team,

I am facing some weird thing. Upto table command, am getting whatever i want. After doing timechart values are changing automatically. Same thing successfully implemented in one environment, in another environment its behaving weird.
my data looks like

2017-09-02,17:00,14.6G

2017-09-02,16:30,18.2G

2017-09-02,16:00,15.3G

2017-09-02,15:30,15.2G

2017-09-02,15:00,16.0G

2017-09-02,14:30,16.2G

my search look like

base search 
|rex "^\s*(?<date>\d{4}-\d{2}-\d{2}),\s*(?<time>\d{2}\:\d{2}),\s*(?<data>[^,]*)" 
| table _time,data
| timechart span=30m max(data) 

Before calling timechart everything fine. Please look at below image.(Before_timechart.png)

alt text

After doing timechart my data changing to some value. Please look at the below image(After_timechart.png)

alt text

Can anyone provide me the reason as well as solution for this problem.

Thank you

Tags (2)
0 Karma
1 Solution

niketnilay
Legend

Your data is string not a number. So, if your intent is just to pull last value and display in table as string (not on chart which requires numeric value), you can use last() aggregating function.

| timechart span=30m last(data) as LatestData

If your intent is show maximum of value max(), you would need to convert from String to Number. This should be done if your intent is to show data on chart. One of the ways could be the following where you can add cases as per your need. Since your sample data only contains G suffixed to number, I have removed it using replace().

 | eval data=case(match(data,"G"),replace(data,"G",""))
 | timechart span=30m max(data)

PS: Ideally you should push the contents of above eval to macro or calculated field knowledge object if same logic needs to be re-used at several places in your dashboards.

Following is a run anywhere search to test out with some sample data that you might have:

| makeresults
| eval data= " 2017-09-02, 17:00, 14.6G;
 2017-09-02, 16:30, 18.2G;
 2017-09-02, 16:00, 15.3G;
 2017-09-02, 15:30, 15.2G;
 2017-09-02, 15:00, 16.0G;
 2017-09-02, 14:30, 16.2G;"
 | makemv delim=";" data 
 | mvexpand data
 | rex field=data "\s+(?<date>\d{4}-\d{2}-\d{2},\s+\d{2}\:\d{2}),\s*(?<data>[^,]+)"
 | eval _time=strptime(date,"%Y-%m-%d, %H:%M")
 | fields - date
 | eval data=case(match(data,"G"),replace(data,"G",""))
 | timechart span=30m max(data)
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

woodcock
Esteemed Legend

As others indicated, because some of your numbers have units attached, timechart's aggregation functions do not recognize them as numbers. You must first convert them to numbers and then everything will work.

This comes from a previous Q&A here:
https://answers.splunk.com/answers/542883/calculations-on-fields-with-multiplier-abbriviatio.html

Like this:

| makeresults 
| eval data="0,345,456K,789M,20G" 
| makemv delim="," data 
| mvexpand data

| rename COMMENT AS "Everything above creates test events; everything below is your solution"

| eval strip_and_multiplier = case(
    match(data, "[kK]$"),     "1,1024",
    match(data, "[kK][bB]$"), "2,1024",
    match(data, "[mM]$"),     "1,1048576",
    match(data, "[mM][bB]$"), "2,1048576",
    match(data, "[gG]$"),     "1,1073741824",
    match(data, "[gG][bB]$"), "2,1073741824",
    match(data, "[tT]$"),     "1,1099511627776",
    match(data, "[tT][bB]$"), "2,1099511627776",
         true(),               "0,1")
| rex field=strip_and_multiplier "^(?<strip>[^,]+),(?<multiplier>.*)$"
| fields - strip_and_multiplier
| eval len=len(data), data= tonumber(substr(data, 0, len - strip)) * multiplier

I would make this a macro.

0 Karma

prathapkcsc
Explorer

HI Sir,
My file test.out contain this below data

    2017-09-02, 17:00, 14.6G
    2017-09-02, 16:30, 18.2G
    2017-09-02, 16:00, 1545.3M
    2017-09-02, 15:30, 15.2G
    2017-09-02, 15:00, 0.1T
    2017-09-02, 14:30, 16.2G

I am reading this file. My requirement is , i want to create a timechart which shows graph in GB's.
My search looks like

source="test.out" 
|rex "^\s*(?<date>\d{4}-\d{2}-\d{2}),\s*(?<time>\d{2}\:\d{2}),\s*(?<data>[^,]*)" 
 | table _time,data
 | timechart span=30m max(data) 
|eval data=case(match(data,"G"),replace(data,"G","")) 
| timechart span=30m max(data) as data

My problem is the above search reading only G values. It is unable to read T and M values.

0 Karma

woodcock
Esteemed Legend

Just add this to the bottom of my solution:

| timechart span=30m max(data)
0 Karma

niketnilay
Legend

Your data is string not a number. So, if your intent is just to pull last value and display in table as string (not on chart which requires numeric value), you can use last() aggregating function.

| timechart span=30m last(data) as LatestData

If your intent is show maximum of value max(), you would need to convert from String to Number. This should be done if your intent is to show data on chart. One of the ways could be the following where you can add cases as per your need. Since your sample data only contains G suffixed to number, I have removed it using replace().

 | eval data=case(match(data,"G"),replace(data,"G",""))
 | timechart span=30m max(data)

PS: Ideally you should push the contents of above eval to macro or calculated field knowledge object if same logic needs to be re-used at several places in your dashboards.

Following is a run anywhere search to test out with some sample data that you might have:

| makeresults
| eval data= " 2017-09-02, 17:00, 14.6G;
 2017-09-02, 16:30, 18.2G;
 2017-09-02, 16:00, 15.3G;
 2017-09-02, 15:30, 15.2G;
 2017-09-02, 15:00, 16.0G;
 2017-09-02, 14:30, 16.2G;"
 | makemv delim=";" data 
 | mvexpand data
 | rex field=data "\s+(?<date>\d{4}-\d{2}-\d{2},\s+\d{2}\:\d{2}),\s*(?<data>[^,]+)"
 | eval _time=strptime(date,"%Y-%m-%d, %H:%M")
 | fields - date
 | eval data=case(match(data,"G"),replace(data,"G",""))
 | timechart span=30m max(data)
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

prathapkcsc
Explorer

Awesome. Worked perfectly.
I have another doubt.
If the case matches to "MB", i need to convert this by dividing with 1024, then it will come into GB form.
If the case matches to "T", i need to multiply the data by 1000, then it will come into GB form.
After that everything could be in GB form. I can proceed same procedure whatever you told.
How could i achieve this?

0 Karma

niketnilay
Legend

As suggested you should handle this in your case statement. Make sure you migrate your Byte size normalization to macro or calculated field to be used across your app and cover all unit conversion in case statement.

I have modified your regular expression as per your requirement to capture bytes and unit as separate fields. In case the rex does not work you would need to provide more sample data or come up with your own through regex101.com.

 | makeresults
 | eval data= " 2017-09-02, 17:00, 14.6G;
  2017-09-02, 16:30, 18.2G;
  2017-09-02, 16:00, 1545.3M;
  2017-09-02, 15:30, 15.2G;
  2017-09-02, 15:00, 0.1T;
  2017-09-02, 14:30, 16.2G;"
  | makemv delim=";" data 
  | mvexpand data
  | rex field=data "\s+(?&lt;date&gt;\d{4}-\d{2}-\d{2},\s+\d{2}\:\d{2}),\s+(?&lt;orig_byte_size&gt;(\d|\.)+)(?&lt;orig_byte_unit&gt;[^\;])"
  | eval _time=strptime(date,"%Y-%m-%d, %H:%M")
  | fields - date, data
  | eval byte_size_gb=case(orig_byte_unit="T",orig_byte_size*1024,orig_byte_unit="M",round(orig_byte_size/1024,1),true(),orig_byte_size)
  | timechart span=30m max(byte_size_gb)

If this works, please accept the answer and up vote comments that helped.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

prathapkcsc
Explorer

The 1545.3M is remained same, after performing changes also.

0 Karma

niketnilay
Legend

Have you tried run anywhere search? Can you post sample event/data for MB scenario? Also do you see orig_bytes_size and orig_byte_unit correctly for Data with size in MB?

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

prathapkcsc
Explorer

I am getting regex error

Error in 'rex' command: Encountered the following error while compiling the regex '\s+(?\d{4}-\d{2}-\d{2},\s+\d{2}\:\d{2}),\s+(?(\d|\.)+)(?[^\;])': Regex: unrecognized character after (? or (?-
0 Karma

niketnilay
Legend

I have re-posted, field category names got escaped while posting.

  | rex field=data "\s+(?<date>\d{4}-\d{2}-\d{2},\s+\d{2}\:\d{2}),\s+(?<orig_byte_size>(\d|\.)+)(?<orig_byte_unit>[^\;])"

If you are trying to paste the code directly in dashboard make sure you escape

 < as &lt; 
  > as &gt;

I have pasted the code in my updated answer with these characters escaped fir dashboard. Please try out and confirm.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

prathapkcsc
Explorer
| makeresults
  | eval data= " 2017-09-02, 17:00, 14.6G;
   2017-09-02, 16:30, 18.2G;
   2017-09-02, 16:00, 1545.3M;
   2017-09-02, 15:30, 15.2G;
   2017-09-02, 15:00, 0.1T;
   2017-09-02, 14:30, 16.2G;"


i don't have hard coded data. My data coming from the script.
0 Karma

prathapkcsc
Explorer
source=test.out
 |rex "^\s*(?<date>\d{4}-\d{2}-\d{2}),\s*(?<time>\d{2}\:\d{2}),\s*(?<data>[^,]*)" 
 | table _time,data
 | timechart span=30m max(data)

Above is my search command. Can you make above command according to my requirement.
0 Karma

niketnilay
Legend

@prathapkcsc, I think you might have figured out yourself. As stated earlier, while answering we propose run anywhere searches because of two reasons...
1) So that fellow Splunkers can also run and validate search
2) Since we have mocked data, we just showcase concept with the same. If any tweaking/tuning required you can perform at your end by plugging required query from proposed answer to your use case.

   source=test.out
   | rex field=data "\s+(?&lt;date&gt;\d{4}-\d{2}-\d{2},\s+\d{2}\:\d{2}),\s+(?&lt;orig_byte_size&gt;(\d|\.)+)(?&lt;orig_byte_unit&gt;[^\;])"
   | eval _time=strptime(date,"%Y-%m-%d, %H:%M")
   | fields - date, data
   | eval byte_size_gb=case(orig_byte_unit="T",orig_byte_size*1024,orig_byte_unit="M",round(orig_byte_size/1024,1),true(),orig_byte_size)
   | timechart span=30m max(byte_size_gb)
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma