Get the output from a .csv file

[linuxteche]

Hi Friends, I have a requirement:

1. I need to read a .csv file.
2. The .csv file gets generated whenever there is a change in a particular file (its kind of monitoring)
3. When the .csv file gets generated, I need to read the .csv file and the contents of the .csv file is as shown below

Code: Select all

"Environment Name","Impact","Operation","Path","Parameter","Current","When","Previous","Flagged as Suspect"
"ww12423.com/Sam","Unclassified","Property Updated","E:/Sam/web.xml/web-app/servlet-mapping","servlet-name","HelloServlet.class","November 20, 2017 10:54:32","HelloServlet","false"

where the "Environment Name" is the "ww12423.com/Sam"
"Impact" is "Unclassified"
"Operation" is "Property Updated"
"Path" is "E:/Sam/web.xml/web-app/servlet-mapping"
"Parameter" is "servlet-name"
"Current" is "HelloServlet.class"
"When" is "November 20, 2017 10:54:32"
"Previous" is "HelloServlet"
"Flagged as Suspect" is "false"

Now I want to map this parameter to respective variables in another executable so that I can send this as an alert.

I did map it but I am getting the "system environment variables", I have depicted in a image format

2017-11-26_22-09-01.jpeg (99.44 KiB) Viewed 8342 times

Could you help or guide me how to get this output?

Thanks,
Dinesh

[aGerman]

Sorry but no chance to see anything on your uploaded image.

Steffen

[Squashman]

Would it not be easier to copy and paste text instead of creating an image and uploading it.

[ShadowThief]

The output in your image matches the text given in the description above. You say that you managed to map it, so I am confused as to what your problem is.

[linuxteche]

Let me explain.

1. The .bat file should parse the .csv file

Code: Select all

setlocal EnableDelayedExpansion

FOR /D %%a in ("E:\Evolven\Netcool\Reports\*.csv"
) do (

    echo full folder path: %%a
    echo Folder name: %%~nxa
ppi.exe -c ppi4vce.conf -e "Domain=%Environment Name%;OriginSeverity=MAJOR;FreeText=%Operation% %Path% %Parameter% %Current% %Previous% %Flagged as Suspect%%When%";OriginEvtClass=Evolven_Alert;ObjectClass=WIN_OS;OrigObject=High Risk Change;Region=CONSOLE;OriginType=N/A;ForwardFlag=N/A;OriginKey=N/A;OriginDateTime=November 20, 2017 10:46:56" -w  "c:\visa\ppi\"
goto :break 
)
:break

2. The .csv file contains the following entries.

"Change Summary for Environment: ","Sam:SW12345678"
"Created on","November 25, 2017 11:36:25"
"From","Beginning of time"
"To","November 25, 2017 11:36:25"
"Total Changes","1"
"Filter Used","Default Search"
"Total Changes","1"

"Environment Name","Impact","Operation","Path","Parameter","Current","When","Previous","Flagged as Suspect"
"SW12345678/Sam","Unclassified","Property Updated","E:/Sam/web.xml/web-app/servlet-mapping","servlet-name","HelloServlet.class","November 20, 2017 10:54:32","HelloServlet","false"

4. I want only the "Environment Name", "Impact","Operation","Path","Parameter","Current","When","Previous","Flagged as Suspect" mapped accordingly in the command

Code: Select all

ppi.exe -c ppi4vce.conf -e "Domain=%Environment Name%;OriginSeverity=MAJOR;FreeText=%Operation% %Path% %Parameter% %Current% %Previous% %Flagged as Suspect%%When%";OriginEvtClass=Evolven_Alert;ObjectClass=WIN_OS;OrigObject=High Risk Change;Region=CONSOLE;OriginType=N/A;ForwardFlag=N/A;OriginKey=N/A;OriginDateTime=November 20, 2017 10:46:56" -w  "c:\visa\ppi\"

5. But, when I run the code which I wrote I don't get it write, I get as shown below: seems its taking the 'system environment' variables but not the contents inside the .csv files.

Code: Select all

c:\VISA\PPI>setlocal EnableDelayedExpansion

c:\VISA\PPI>FOR / %a in ("E:\Evolven\Netcool\Reports\*.csv") do (
echo full folder path: %a
 echo Folder name: %~nxa
 ppi.exe -c ppi4vce.conf -e "Domain=;OriginSeverity=MAJOR;FreeText= C:\Program Files\avs\bin;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\    ";Or
iginEvtClass=Evolven_Alert;ObjectClass=WIN_OS;OrigObject=High Risk Change;Region=CONSOLE;OriginType=N/A;ForwardFlag=N/A;OriginKey=N/A;OriginDateTime=November 20, 2017 10:46:56" -w  "c:\visa\ppi\"
 goto :break
)

[aGerman]

Code: Select all

@echo off &setlocal
for %%a in ("E:\Evolven\Netcool\Reports\*.csv") do (
  for /f usebackq^ skip^=1^ tokens^=1^,3^,5^,7^,9^,11^,13^,15^,17^ delims^=^" %%A in ("%%~fa") do (
    echo %%A
    echo %%B
    echo %%C
    echo %%D
    echo %%E
    echo %%F
    echo %%G
    echo %%H
    echo %%I
  )
)
pause

Instead of ECHOing the FOR variables use them in your call of ppi.exe. FWIW Never(!) overwrite prdefined variables like PATH if you don't know exactly what you're doing.

Steffen

[linuxteche]

Thank you Steffan it works perfect.

But, I have one more question when I tried to execute the following it throws an error

Code: Select all

@echo on &setlocal
for %%a in ("E:\Evolven\Netcool\Reports\*.csv") do (
  for /f usebackq^ skip^=1^ tokens^=1^,3^,5^,7^,9^,11^,13^,15^,17^ delims^=^" %%A in ("%%~fa") do (

    echo %%A
    echo %%B
set Severity=%%B
set Property=%%C
set Path=%%D
set Parameter=%%E
set Current=%%F
set When=%%G
set Previous=%%H
set Flag=%%I

REM The below line is written so as to get the hostname and ignore the '/' that comes after the hostname

for /f "tokens=1,2 delims=/ " %%a in ("%%A") do set node=%%a&set domain=%%b


  )

)
echo.node: %node%
echo.Severity: %Severity%
echo.Property: %Property%
echo.Path=%Path%
echo.Parameter=%Parameter%
echo.Current=%Current%
echo.When=%When%
echo.Previous=%Previous%
echo.Flag=%Flag%

"C:\VISA\PPI\ppi.exe" -c vce.conf -e "Domain=%node%;OriginSeverity=MAJOR;FreeText=%Property% %Path% %Parameter% %Current% %When% %Previous% %Flag%;OriginEvtClass=Evolven_Alert;ObjectClass=WIN_OS;OrigObject=High Risk Change;Region=CONSOLE;OriginType=N/A;ForwardFlag=N/A;OriginKey=N/A;OriginDateTime=%When%" -w  "c:\visa\ppi\"

pause

I get the following output

'taskkill' is not recognized as an internal or external command,
operable program or batch file.
The batch file cannot be found.
'taskkill' is not recognized as an internal or external command,
operable program or batch file.
The batch file cannot be found.

Any idea why I get this error?

[Squashman]

Thank you Steffan it works perfect.

But, I have one more question when I tried to execute the following it throws an error

'taskkill' is not recognized as an internal or external command,
operable program or batch file.
The batch file cannot be found.
'taskkill' is not recognized as an internal or external command,
operable program or batch file.
The batch file cannot be found.

Any idea why I get this error?

As Steffan told you in his previous post.

FWIW Never(!) overwrite prdefined variables like PATH if you don't know exactly what you're doing.
Steffen

[aGerman]

If you need variables then use another variable name (e.g. myPath). But actually there is no reason to assign variables (at least not for the code you posted).

Code: Select all

@echo on &setlocal
pushd "C:\VISA\PPI"

for %%a in ("E:\Evolven\Netcool\Reports\*.csv") do (
  for /f usebackq^ skip^=1^ tokens^=1^,3^,5^,7^,9^,11^,13^,15^,17^ delims^=^" %%A in ("%%~fa") do (
    for /f "delims=/ " %%J in ("%%A") do (
      ppi.exe -c vce.conf -e "Domain=%%J;OriginSeverity=MAJOR;FreeText=%%C %%D %%E %%F %%G %%H %%I;OriginEvtClass=Evolven_Alert;ObjectClass=WIN_OS;OrigObject=High Risk Change;Region=CONSOLE;OriginType=N/A;ForwardFlag=N/A;OriginKey=N/A;OriginDateTime=%%G" -w  "c:\visa\ppi\"
    )
  )
)

popd
pause

You may have to update the value for skip to the real number of lines to skip in your csv file. Your image indicates that there is more than only one line to ignore,

Steffen

[linuxteche]

I have a output that comes as follows as well


"Environment Path","Consistency","Impact","When","Flagged as Suspect","Operation","Path","Parameter","Source"
"AND/PROD/SP - Echo Notify Details/wl7301234/Microsoft Windows 2012","Inconsistent","Non critical","September 07, 2017 00:44:06","false","Element Exists in target","File System/C:/Windows/System32/drivers/etc/hosts/100.xx.xx.xx [HOST NAME=xxx.xx.com]"
"AND/PROD/SEC/wl5501234/Microsoft Windows 2008","Inconsistent","Non critical","September 07, 2017 00:44:06","false","Element Exists in target","File System/C:/Windows/System32/drivers/etc/hosts/100.xx.xx.xx [HOST NAME=xxx.xx.com]"
"wl9301234/Sam","Unclassified","Property Updated","E:/Sam/web.xml/web-app/servlet-mapping","servlet-name","HelloServlet.class","November 20, 2017 10:54:32","HelloServlet","false"


The below code is able to get if the take the following, but if if contains the top two it just takes "AND" alone but I want the items marked in bold which is the node name.


"wl9301234/Sam","Unclassified","Property Updated","E:/Sam/web.xml/web-app/servlet-mapping","servlet-name","HelloServlet.class","November 20, 2017 10:54:32","HelloServlet","false"


The code that I have now is given below:

Code: Select all

@echo on &setlocal
pushd "C:\VISA\PPI"

for %%a in ("E:\Evolven\Netcool\Reports\*.csv") do (
  for /f usebackq^ skip^=1^ tokens^=1^,3^,5^,7^,9^,11^,13^,15^,17^ delims^=^" %%A in ("%%~fa") do (
    for /f "delims=/ " %%J in ("%%A") do (
      ppi.exe -c vce.conf -e "Domain=%%J;OriginSeverity=MAJOR;FreeText=%%C %%D %%E %%F %%G %%H %%I;OriginEvtClass=Evolven_Alert;ObjectClass=WIN_OS;OrigObject=High Risk Change;Region=CONSOLE;OriginType=N/A;ForwardFlag=N/A;OriginKey=N/A;OriginDateTime=%%G" -w  "c:\visa\ppi\"
    )
  )
)

popd
pause

How to get the strings marked in bold alone?

[linuxteche]

From the below code, how to get the output of the 3rd delimiter

"ANET/PROD/WEB - MINT, PINT, RINT FrontEnd/wl55sms12345/Microsoft Windows 2008"

The 3rd output is the node name in the above case it is wl55sms12345

Code: Select all

@echo on &setlocal
pushd "C:\VISA\PPI"

for %%a in ("E:\Evolven\Netcool\Reports\*.csv") do (
  for /f usebackq^ skip^=1^ tokens^=1^,3^,5^,7^,9^,11^,13^,15^,17^ delims^=^" %%A in ("%%~fa") do (
    for /f "delims=/ " %%J in ("%%A") do (
      ppi.exe -c vce.conf -e "Domain=%%J;OriginSeverity=MAJOR;FreeText=%%C %%D %%E %%F %%G %%H %%I;OriginEvtClass=Evolven_Alert;ObjectClass=WIN_OS;OrigObject=High Risk Change;Region=CONSOLE;OriginType=N/A;ForwardFlag=N/A;OriginKey=N/A;OriginDateTime=%%G" -w  "c:\visa\ppi\"
    )
  )
)

popd
pause