Remove part of values of a field without affecting its role.

[dsiob]

I have three colums Track, Flow and Job. I want to plot 'Track+Flow' vs 'Job' as 'Track+Flow' giving uniqueness. Eg: Track: TSC, Flow: A, Job: j1 Track:TCS, Flow:B, Job:j2. But I just want to show 'Track' part of 'Track+Flow'. Eg: 'TCS' instead of 'TCSA' without affecting the role of 'Track+Flow'. As 'Flow' part is irrelevent to the user, I want to hide this.

[dsiob · Accepted solution]

I tried rich7177's solution. But it didn't work out perfectly. May be that worked for bar charts, but for timeline it didn't. I just turn the labels into some useful info, but the problem is still not resolved. Here is what I tried and the final query.

tried: (Being same labels, time lines are getting merged again)

/*Sorce*/|rename "Start Time" AS start_date   | rename "End Time" AS end_date  | eval _time = strptime(start_date, "%m/%d/%Y %H:%M") | eval end_time = strptime(end_date, "%m/%d/%Y %H:%M") |eval combine= Track+Flow| eval duration = (end_time - _time) * 1000  |rename combine as Track| stats count   by _time, Track, duration,  "job" | table  _time "Track" "job"   duration |rex field=Track mode=sed "s/_\w+$//"

final: Here I changed info in 'Flow' column (something useful)

/*Sorce*/|rename "Start Time" AS start_date   | rename "End Time" AS end_date  | eval _time = strptime(start_date, "%m/%d/%Y %H:%M") | eval end_time = strptime(end_date, "%m/%d/%Y %H:%M") |eval combine= Track+Flow| eval duration = (end_time - _time) * 1000  |rename combine as Track| stats count   by _time, Track, duration,  "job" | table  _time "Track" "job"   duration 

(As it is not allowing to upload images in 'Add comment' from local, didn't show the timeline result)

[Richfez]

It sounds like you have a good answer you came up with on your own. Excellent!

Why don't you write that up into your own answer and accept it for yourself and collect the karma? It's OK to do that (within reason). Then upvote any comments or answers you found particularly helpful.

Also be sure to use the 'code' button (the 101010 button in the toolbar) to paste code so the editor doesn't eat your special characters.

[dsiob]

thanks @rich777 🙂

[woodcock]

Now click Accept.

[Richfez]

Thanks for that description of what you want. Sometimes it helps us to get our head around what to do. Now, you've already mentioned this answer hasn't worked - read to the end for a tip on that - but I think it's a "good enough answer" that someone may find it useful so I'm posting it as-is.

Here's a working example of everything except the magic bits (I'll add that later):

| makeresults 
| eval MyField="A-X 23,A-Y 33,A-Z 12" 
| makemv delim="," MyField
| mvexpand MyField
| rex field=MyField "(?<FieldLabel>\w-\w)\s+(?<Value>\d+)"

Those lines are there purely to make a set of answers that look enough like what you have that I can use them to illustrate the working example. The above code should work anywhere, though, so it's good for an example.

When set as a bar graph, it looks like the top screenshot here.

Now, to the end of that I add this line:

...
| rex field=FieldLabel mode=sed "s/-\w+$//"

And it removes the extra, trailing pieces of the labels but does not affect how many rows there are. The second screenshot shows this.

Happy Splunking!
-Rich

If you need additional help, can you post your actual search? We can look at that directly and either find a better way to do it, or make one of the mentioned answers work in your particular use case, or maybe even do both.

[dsiob]

thanks all for your response, I added some useful info as 'Flow' with track so its fine now:)

[woodcock]

So either post your final solution as an answer and click Accept or accept the answer that helped you the most and upvote the other good stuff.

[woodcock]

Generally the thing to do is to use fieldformat which causes the actual value to remain as-is but presents something else when the value is displayed to the user. However in this case, we can use stats instead of chart, fix the field values with regex, then use xyseries to do the contingency as follows:

If you are plotting Track_and_Flow on the Y-axis, then like this:

index=_* 
| rename sourcetype AS Track source AS Flow index AS Job 
| rex field=Job mode=sed "s/^_+//" 
| rex field=Flow mode=sed "s%^.*/%%" 
| table Track Flow Job
| eval Track_and_Flow = Track . ":" . Flow
| stats count BY Job Track_and_Flow
| rex field=Track_and_Flow mode=sed "s/^[^:]+://"
| xyseries Job Track_and_Flow count
| fillnull value=0

If you are plotting Track_and_Flow on the X-axis, then use swap the first 2 arguments of xyseries like this:

index=_* 
| rename sourcetype AS Track source AS Flow index AS Job 
| rex field=Job mode=sed "s/^_+//" 
| rex field=Flow mode=sed "s%^.*/%%" 
| table Track Flow Job
| eval Track_and_Flow = Track . ":" . Flow
| stats count BY Job Track_and_Flow
| rex field=Track_and_Flow mode=sed "s/^[^:]+://"
| xyseries Track_and_Flow Job count
| fillnull value=0

[dsiob]

I tried using Fielformat but not able to achieve.
Here is the complete scenario of problem...pls take a look
-I have created Timeline Visualization of Track( of jobs) Vs RunTime-duration (of Jobs). Eg: In above pic Ralie is the Track, it has three jobs.
-Problem is, these three jobs belongs to Ralie, so in visualization, all three are showing in same line under Ralie (overlapping), which is not preferred.
-So I added a new column 'Flow' which decide flow of each job in the same Track.
And now I created timline of 'Track+Flow' vs 'RunTime-duration', which is showing all three jobs in different line as above pic.
-Now the problem remains is y-axis labels, this is 'Track+flow' name, but I want label as 'Track' only. Eg: Instead of 'Ralie A' 'Ralie B' 'Ralie C' I need 'Ralie' as all label names, but in different lines.
-As I tried to replace the labels using " eval result= Track + flow |eval new=replace(result,.flow."$","")", it removed last part of result (flow), but again joined the three lines (as their names became same)

I have given Complete description of the problem, pls don't be confused. Any alternate method at any step will be helpful!!

[woodcock]

Did you even try my solution (it has nothing to do with fieldformat)?

[Richfez]

From your description, you have multiple lines of TCS data (one for TCS A, one for TCS B, etc...) and would like to display them as a series of TCS lines without the A and B and whatnot?

So instead of

TCS-A 73
TCS-B 69
...

You want

TCS 73
TCS 69
....

(I have to ask, this is better how? Either Flow is important, or Flow isn't important, but how can it be relevant enough to keep but not relevant enough to tell them you are keeping it by displaying it? )

So, you might be able to do this with a ... Well, specifics matter here. If you "track+flow" using a dash between them, so you always end up with things like "TCS-G" or "DDHF-AAR", and call the field "TrackFlow", then you could do this:
... | rex field=TrackFlow mode=sed "s/-.*//" | ...
You'll need that AFTER all your other work, but before you display it. That sed-mode regex strips the dash and anything that follows it from the field TrackFlow, so TCS-G would become TCS and DDHF-AAR would become DDHF.

Give that a try, let us know.

[dsiob]

your assumption is correct @rich7177, I tried -> rex field=TrackFlow mode=sed "s/-.*//" , it is replacing but not after all work done. All three lines joining, after replacing.

[Richfez]

(Note that was added as a comment instead of an answer because I can't imagine this is the right solution. But it was worth a shot).

[adonio]

hello there,
please elaborate, i find it difficult to understand what is the problem you are trying to solve here.
can you also share a sample data and search you currently using?

[dsiob]

Here is the complete scenario...pls take a look
-I have created Timeline Visualization of Track( of jobs) Vs RunTime-duration (of Jobs). Eg: In above pic Ralie is the Track, it has three jobs.
-Problem is, these three jobs belongs to Ralie, so in visualization, all three are showing in same line under Ralie (overlapping), which is not preferred.
-So I added a new column 'Flow' which decide flow of each job in the same Track.
And now I created timline of 'Track+Flow' vs 'RunTime-duration', which is showing all three jobs in different line as above pic.
-Now the problem remains is y-axis labels, this is 'Track+flow' name, but I label as 'Track' only. Eg: Instead of 'Ralie A' 'Ralie B' 'Ralie C' I need 'Ralie' as all label names, but in different lines.
-As I tried to replace the labels using " eval result= Track + flow |eval new=replace(result,.flow."$","")", it removed last part of result (flow), but again joined the three lines (as their names became same)

I have given Complete description of the problem, pls don't be confused. Any alternate method at any step will be helpful!!