- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Replace row value data and add new column with updated value
I am looking for specific query where I can alter the row values after the final output and create new column with new value.
For example,
I have written the below query :
index=csv sourcetype="miscprocess:csv" source="D:\\automation\\miscprocess\\output_acd.csv"
| rex field=_raw "\"(?<filename>\d*\.\d*)\"\,\"(?<filesize>\d*\.\d*)\"\,\"(?<filelocation>\S*)\""
| search filename="*" filesize="*" filelocation IN ("*cl3*", "*cl1*")
| table filename, filesize, filelocation
Which gives me the following output:
filename filesize filelocation
012624.1230 | 13253.10546875 | E:\totalview\ftp\acd\cl1\backup_modified\012624.1230 |
012624.1230 | 2236.3291015625 | E:\totalview\ftp\acd\cl3\backup\012624.1230 |
012624.1200 | 13338.828125 | E:\totalview\ftp\acd\cl1\backup_modified\012624.1200 |
012624.1200 | 2172.1640625 | E:\totalview\ftp\acd\cl3\backup\012624.1200 |
012624.1130 | 13292.32421875 | E:\totalview\ftp\acd\cl1\backup_modified\012624.1130 |
012624.1130 | 2231.9658203125 | E:\totalview\ftp\acd\cl3\backup\012624.1130 |
012624.1100 | 13438.65234375 | E:\totalview\ftp\acd\cl1\backup_modified\012624.1100 |
BUT, I like the row values to be replaced by "ACD55" where the file location is cl1 and "ACD85" where the file location is cl3 under filelocation column. So the desire output should be:
filename filesize filelocation
012624.1230 | 13253.10546875 | ACD55 |
012624.1230 | 2236.3291015625 | ACD85 |
012624.1200 | 13338.828125 | ACD55 |
012624.1200 | 2172.1640625 | ACD85 |
012624.1130 | 13292.32421875 | ACD55 |
012624.1130 | 2231.9658203125 | ACD85 |
012624.1100 | 13438.65234375 | ACD55 |
The raw events are like below:
"020424.0100","1164.953125","E:\totalview\ftp\acd\cl3\backup\020424.0100"
"020624.0130","1754.49609375","E:\totalview\ftp\acd\cl1\backup_modified\020624.0130"
please suggest :
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

only data with cl1 is getting replaced. I also have data with cl3 which needs to be replaced by ACD85.
There is no possibility @ITWhisperer's search should give this half of replacement. But first, your search is very inefficient: The third line starting with search should be accomplished in the first line so fewer events are computed. Secondly, using regex on rigidly formatted data (CSV) is a waste and prone to errors. This is what I suggest, using exactly what @ITWhisperer proposed.
index=csv sourcetype="miscprocess:csv" source="D:\\automation\\miscprocess\\output_acd.csv" ("\cl3\" OR "\cl1\")
| eval filename = split(_raw, ",")
| eval filesize = mvindex(filename, 1), filelocation = mvindex(filename, 2)
| eval filename = mvindex(filename, 0)
| eval filelocation=if(like(filelocation,"%\cl1%"),"ACD55","ACD85")
Also important: Play with the following emulation and compare with your real data:
| makeresults
| fields - _*
| eval data=split("012624.1230,13253.10546875,E:\totalview\ftp\acd\cl1\backup_modified\012624.1230
012624.1230,2236.3291015625,E:\totalview\ftp\acd\cl3\backup\012624.1230
012624.1200,13338.828125,E:\totalview\ftp\acd\cl1\backup_modified\012624.1200
012624.1200,2172.1640625,E:\totalview\ftp\acd\cl3\backup\012624.1200
012624.1130,13292.32421875,E:\totalview\ftp\acd\cl1\backup_modified\012624.1130
012624.1130,2231.9658203125,E:\totalview\ftp\acd\cl3\backup\012624.1130
012624.1100,13438.65234375,E:\totalview\ftp\acd\cl1\backup_modified\012624.1100", "
")
| mvexpand data
| rename data AS _raw
| search (\\cl1\\ OR \\cl3\\)
``` the above emulates
index=csv sourcetype="miscprocess:csv" source="D:\\automation\\miscprocess\\output_acd.csv" ("\cl3\" OR "\cl1\")
```
| eval filename = split(_raw, ",")
| eval filesize = mvindex(filename, 1), filelocation = mvindex(filename, 2)
| eval filename = mvindex(filename, 0)
| eval filelocation=if(like(filelocation,"%\cl1%"),"ACD55","ACD85")
The output is
_raw | filelocation | filename | filesize |
012624.1230,13253.10546875,E:\totalview\ftp\acd\cl1\backup_modified\012624.1230 | ACD55 | 012624.1230 | 13253.10546875 |
012624.1230,2236.3291015625,E:\totalview\ftp\acd\cl3\backup\012624.1230 | ACD85 | 012624.1230 | 2236.3291015625 |
012624.1200,13338.828125,E:\totalview\ftp\acd\cl1\backup_modified\012624.1200 | ACD55 | 012624.1200 | 13338.828125 |
012624.1200,2172.1640625,E:\totalview\ftp\acd\cl3\backup\012624.1200 | ACD85 | 012624.1200 | 2172.1640625 |
012624.1130,13292.32421875,E:\totalview\ftp\acd\cl1\backup_modified\012624.1130 | ACD55 | 012624.1130 | 13292.32421875 |
012624.1130,2231.9658203125,E:\totalview\ftp\acd\cl3\backup\012624.1130 | ACD85 | 012624.1130 | 2231.9658203125 |
012624.1100,13438.65234375,E:\totalview\ftp\acd\cl1\backup_modified\012624.1100 | ACD55 | 012624.1100 | 13438.65234375 |
As you see, there is no such "partial replacement". You will need to illustrate and explain any discrepancy between real data and this mock data if you don't get the same results.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Also have another doubt. I have written below query to get the specific output.
index=xyz sourcetype="automation:csv" source="D:\\Intradiem_automation\\ACD_FILETRACKER.csv"
| rex field=_raw "^(?P<ACD>\w+\.\d+),(?P<ATTEMPTS>[^,]+),(?P<FAIL_REASON>[^,]*),(?P<INTERVAL_FILE>[^,]+),(?P<STATUS>\w+),(?P<START>[^,]+),(?P<FINISH>[^,]+),(?P<INGEST_TIME>.+)"
| eval field_in_hhmmss=tostring(INGEST_TIME, "duration")
| rename field_in_hhmmss AS INGESTION_TIME_HH-MM-SS
| search ACD="*" ATTEMPTS="*" FAIL_REASON="*" INTERVAL_FILE="*" STATUS="*" START="*" FINISH="*" INGESTION_TIME_HH-MM-SS="*"
| table ACD, ATTEMPTS, FAIL_REASON, INTERVAL_FILE,INTERVAL_FILE1, STATUS, START, FINISH, INGESTION_TIME_HH-MM-SS
| dedup INTERVAL_FILE
| sort -START
I like to extract the filename "020624.0500" from Interval_file column and create another column name "Filename" beside the Interval_file column and before status column. Please help
ACD ATTEMPTS FAIL_REASON INTERVAL_FILE STATUS START FINISH INGESTION_TIME_HH-MM-SS
acd.55 | 1 | NULL | C:\totalview\ftp\switches\customer1\55\020624.0500 | PASS | 2024-02-06 11:32:30.057 +00:00 | 2024-02-06 11:32:52.274 +00:00 | 00:00:22 |
acd.55 | 1 | NULL | C:\totalview\ftp\switches\customer1\55\020624.0530 | PASS | 2024-02-06 12:02:30.028 +00:00 | 2024-02-06 12:02:54.151 +00:00 | 00:00:24 |
acd.85 | 1 | NULL | C:\totalview\ftp\switches\customer1\85\020624.0500 | PASS | 2024-02-06 11:31:30.021 +00:00 | 2024-02-06 11:31:40.788 +00:00 | 00:00:10 |
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

| eval Filename=mvindex(split(INTERVAL_FILE,"\\"),-1)
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

| eval filelocation=if(like(filelocation,"%\cl1%"),"ACD55","ACD85")
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@ITWhisperer great!! it worked Thank you for quick solution
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@ITWhisperer seems its working partially. I can see only data with cl1 is getting replaced. I also have data with cl3 which needs to be replaced by ACD85.
cl1=ACD55
cl3=ACD85
Am I missing any thing here.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

That's hard to tell - please can you share your full search?
