Splunk Search

Replace row value data and add new column with updated value

ravir_jbp
Explorer

 

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 :

Labels (2)
Tags (1)
0 Karma

yuanliu
SplunkTrust
SplunkTrust
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

_rawfilelocationfilenamefilesize
012624.1230,13253.10546875,E:\totalview\ftp\acd\cl1\backup_modified\012624.1230ACD55012624.123013253.10546875
012624.1230,2236.3291015625,E:\totalview\ftp\acd\cl3\backup\012624.1230ACD85012624.12302236.3291015625
012624.1200,13338.828125,E:\totalview\ftp\acd\cl1\backup_modified\012624.1200ACD55012624.120013338.828125
012624.1200,2172.1640625,E:\totalview\ftp\acd\cl3\backup\012624.1200ACD85012624.12002172.1640625
012624.1130,13292.32421875,E:\totalview\ftp\acd\cl1\backup_modified\012624.1130ACD55012624.113013292.32421875
012624.1130,2231.9658203125,E:\totalview\ftp\acd\cl3\backup\012624.1130ACD85012624.11302231.9658203125
012624.1100,13438.65234375,E:\totalview\ftp\acd\cl1\backup_modified\012624.1100ACD55012624.110013438.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.

Tags (2)
0 Karma

ravir_jbp
Explorer

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.551NULLC:\totalview\ftp\switches\customer1\55\020624.0500PASS2024-02-06 11:32:30.057 +00:002024-02-06 11:32:52.274 +00:0000:00:22
acd.551NULLC:\totalview\ftp\switches\customer1\55\020624.0530PASS2024-02-06 12:02:30.028 +00:002024-02-06 12:02:54.151 +00:0000:00:24
acd.851NULLC:\totalview\ftp\switches\customer1\85\020624.0500PASS2024-02-06 11:31:30.021 +00:002024-02-06 11:31:40.788 +00:0000:00:10
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| eval Filename=mvindex(split(INTERVAL_FILE,"\\"),-1)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| eval filelocation=if(like(filelocation,"%\cl1%"),"ACD55","ACD85")
0 Karma

ravir_jbp
Explorer

@ITWhisperer  great!! it worked Thank you for quick solution

0 Karma

ravir_jbp
Explorer

@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.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

ICYMI - Check out the latest releases of Splunk Edge Processor

Splunk is pleased to announce the latest enhancements to Splunk Edge Processor.  HEC Receiver authorization ...

Introducing the 2024 SplunkTrust!

Hello, Splunk Community! We are beyond thrilled to announce our newest group of SplunkTrust members!  The ...