Assistance required in log file ingestion

Tags: log file to csv

#1 navdeepsingh83 5 years ago

Hi,

We have a following log file from open source password manager solution. It runs on tomcat. We have graylog server where we would like to send the log data and parse it. Now, we can send the log file to graylog however the entire line comes as one message block, instead of parsing into fields automatically. I am wondering how can i convert the file into csv and send to graylog.

Here is sample log. It doesn't come with any header.

2018-08-25T07:40:14Z, ERROR, http.PwmResponse, {117412} 5028 ERROR_BAD_SESSION (client unable to reply with session key) [xx.xx.47.82]
2018-08-25T07:40:15Z, ERROR, filter.SessionFilter, {117413} 5028 ERROR_BAD_SESSION (client unable to reply with session key) [xx.xx.47.82]
2018-08-25T07:40:15Z, ERROR, http.PwmResponse, {117413} 5028 ERROR_BAD_SESSION (client unable to reply with session key) [xx.xx.47.82]
2018-08-25T07:40:17Z, ERROR, filter.SessionFilter, {117415} 5028 ERROR_BAD_SESSION (client unable to reply with session key) [xx.xx.47.82]
2018-08-25T07:40:17Z, ERROR, http.PwmResponse, {117415} 5028 ERROR_BAD_SESSION (client unable to reply with session key) [xx.xx.47.82]
2018-08-25T10:04:28Z, ERROR, filter.RequestInitializationFilter, {117422} 5063 ERROR_SECURITY_VIOLATION (current network address 'yy.yy.185.123' has changed from original network address 'yy.yy.173.181') [yy.yy.173.181]
2018-08-25T10:04:28Z, ERROR, http.PwmResponse, {117422} 5063 ERROR_SECURITY_VIOLATION (current network address 'yy.yy.185.123' has changed from original network address 'yy.yy.173.181') [yy.yy.173.181]
2018-08-25T11:08:03Z, INFO , auth.LDAPAuthenticationRequest, {117467} authID=130, successful ldap authentication for UserIdentity{"userDN":"CN=UserA,CN=Users,DC=org,DC=com","ldapProfile":"default"} (606ms) type: AUTHENTICATED, using strategy BIND, using proxy connection: false, returning bind dn: CN=UserA,CN=Users,DC=org,DC=com [yy.yy.32.238]
2018-08-25T11:08:03Z, INFO , event.AuditService, audit event: {"perpetratorID":"UserA","perpetratorDN":"CN=UserA,CN=Users,DC=org,DC=com","perpetratorLdapProfile":"default","sourceAddress":"yy.yy.32.238","sourceHost":"yy.yy.32.238","type":"USER","eventCode":"AUTHENTICATE","guid":"941aa151-8998-4c89-b690-484e623429d8","timestamp":"2018-08-25T05:38:03Z","message":"type=AUTHENTICATED, source=LOGIN_FORM","narrative":"UserA (CN=UserA,CN=Users,DC=org,DC=com) has authenticated","xdasTaxonomy":"XDAS_AE_AUTHENTICATE_ACCOUNT","xdasOutcome":"XDAS_OUT_SUCCESS"}
2018-08-25T11:08:48Z, INFO , operations.PasswordUtility, {117467,UserA} user 'UserIdentity{"userDN":"CN=UserA,CN=Users,DC=org,DC=com","ldapProfile":"default"}' successfully changed password [yy.yy.32.238]
2018-08-25T11:08:49Z, INFO , event.AuditService, audit event: {"perpetratorID":"UserA","perpetratorDN":"CN=UserA,CN=Users,DC=org,DC=com","perpetratorLdapProfile":"default","sourceAddress":"yy.yy.32.238","sourceHost":"yy.yy.32.238","type":"USER","eventCode":"CHANGE_PASSWORD","guid":"00c158d5-0ea5-46aa-8c8c-cd279f783ecd","timestamp":"2018-08-25T05:38:49Z","narrative":"UserA (CN=UserA,CN=Users,DC=org,DC=com) has changed their password","xdasTaxonomy":"XDAS_AE_SET_CRED_ACCOUNT","xdasOutcome":"XDAS_OUT_SUCCESS"}
2018-08-25T11:10:04Z, ERROR, filter.RequestInitializationFilter, {117471} 5063 ERROR_SECURITY_VIOLATION (current network address 'yy.yy.112.147' has changed from original network address 'xx.xx.243.3') [xx.xx.243.3]

I wrote the following nxlog conf but it doesn't seems to be working.

<Extension tomcat>
        Module 			xm_csv
        Fields 			$DateTime,$Type,$Category,$Details
        FieldTypes 		string,string,string,string
        Delimiter		","
</Extension>
   
<Input in_pwm>
        Module    	im_file
        File    	"C:\\Users\\Documents\\TempOut\\PWM\\PWM.log"
	    PollInterval 1
		ReadFromLast False
   	    #Recursive True
    	SavePos False
	    Exec tomcat->parse_csv();
</Input>

Appreciate your assistance in getting this working.

Permalink

#2 Zhengshi Nxlog ✓ 5 years ago

#1 navdeepsingh83

Hi, We have a following log file from open source password manager solution. It runs on tomcat. We have graylog server where we would like to send the log data and parse it. Now, we can send the log file to graylog however the entire line comes as one message block, instead of parsing into fields automatically. I am wondering how can i convert the file into csv and send to graylog. Here is sample log. It doesn't come with any header. 2018-08-25T07:40:14Z, ERROR, http.PwmResponse, {117412} 5028 ERROR_BAD_SESSION (client unable to reply with session key) [xx.xx.47.82] 2018-08-25T07:40:15Z, ERROR, filter.SessionFilter, {117413} 5028 ERROR_BAD_SESSION (client unable to reply with session key) [xx.xx.47.82] 2018-08-25T07:40:15Z, ERROR, http.PwmResponse, {117413} 5028 ERROR_BAD_SESSION (client unable to reply with session key) [xx.xx.47.82] 2018-08-25T07:40:17Z, ERROR, filter.SessionFilter, {117415} 5028 ERROR_BAD_SESSION (client unable to reply with session key) [xx.xx.47.82] 2018-08-25T07:40:17Z, ERROR, http.PwmResponse, {117415} 5028 ERROR_BAD_SESSION (client unable to reply with session key) [xx.xx.47.82] 2018-08-25T10:04:28Z, ERROR, filter.RequestInitializationFilter, {117422} 5063 ERROR_SECURITY_VIOLATION (current network address 'yy.yy.185.123' has changed from original network address 'yy.yy.173.181') [yy.yy.173.181] 2018-08-25T10:04:28Z, ERROR, http.PwmResponse, {117422} 5063 ERROR_SECURITY_VIOLATION (current network address 'yy.yy.185.123' has changed from original network address 'yy.yy.173.181') [yy.yy.173.181] 2018-08-25T11:08:03Z, INFO , auth.LDAPAuthenticationRequest, {117467} authID=130, successful ldap authentication for UserIdentity{"userDN":"CN=UserA,CN=Users,DC=org,DC=com","ldapProfile":"default"} (606ms) type: AUTHENTICATED, using strategy BIND, using proxy connection: false, returning bind dn: CN=UserA,CN=Users,DC=org,DC=com [yy.yy.32.238] 2018-08-25T11:08:03Z, INFO , event.AuditService, audit event: {"perpetratorID":"UserA","perpetratorDN":"CN=UserA,CN=Users,DC=org,DC=com","perpetratorLdapProfile":"default","sourceAddress":"yy.yy.32.238","sourceHost":"yy.yy.32.238","type":"USER","eventCode":"AUTHENTICATE","guid":"941aa151-8998-4c89-b690-484e623429d8","timestamp":"2018-08-25T05:38:03Z","message":"type=AUTHENTICATED, source=LOGIN_FORM","narrative":"UserA (CN=UserA,CN=Users,DC=org,DC=com) has authenticated","xdasTaxonomy":"XDAS_AE_AUTHENTICATE_ACCOUNT","xdasOutcome":"XDAS_OUT_SUCCESS"} 2018-08-25T11:08:48Z, INFO , operations.PasswordUtility, {117467,UserA} user 'UserIdentity{"userDN":"CN=UserA,CN=Users,DC=org,DC=com","ldapProfile":"default"}' successfully changed password [yy.yy.32.238] 2018-08-25T11:08:49Z, INFO , event.AuditService, audit event: {"perpetratorID":"UserA","perpetratorDN":"CN=UserA,CN=Users,DC=org,DC=com","perpetratorLdapProfile":"default","sourceAddress":"yy.yy.32.238","sourceHost":"yy.yy.32.238","type":"USER","eventCode":"CHANGE_PASSWORD","guid":"00c158d5-0ea5-46aa-8c8c-cd279f783ecd","timestamp":"2018-08-25T05:38:49Z","narrative":"UserA (CN=UserA,CN=Users,DC=org,DC=com) has changed their password","xdasTaxonomy":"XDAS_AE_SET_CRED_ACCOUNT","xdasOutcome":"XDAS_OUT_SUCCESS"} 2018-08-25T11:10:04Z, ERROR, filter.RequestInitializationFilter, {117471} 5063 ERROR_SECURITY_VIOLATION (current network address 'yy.yy.112.147' has changed from original network address 'xx.xx.243.3') [xx.xx.243.3] I wrote the following nxlog conf but it doesn't seems to be working. <Extension tomcat> Module xm_csv Fields $DateTime,$Type,$Category,$Details FieldTypes string,string,string,string Delimiter "," </Extension> <Input in_pwm> Module im_file File "C:\\Users\\Documents\\TempOut\\PWM\\PWM.log" PollInterval 1 ReadFromLast False #Recursive True SavePos False Exec tomcat->parse_csv(); </Input> Appreciate your assistance in getting this working.

I would bet that there are some errors in your nxlog.log file about the number of fields or that it isn't valid input.
Reasoning: There will appear as more than 4 fields due to your $Details field on some lines having commas in their value. CN=UserA,CN=Users,DC=org,DC=com for instance.

Suggestions:
Check the logs to see errors and adjust accordingly. (should always be first step, I think) This will probably mean either changing the delimiter from your source to something other than comma or using another method like regex to parse the fields.

example using regex. Since your log is pretty straight forward, something like the following may work, though It has not been tested.

<Input in_pwm>
        Module      im_file
        File        "C:\\Users\\Documents\\TempOut\\PWM\\PWM.log"
        PollInterval 1
        ReadFromLast False
        #Recursive True
        SavePos False
	<Exec>
		if $raw_event =~ /^(\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}Z),\s([\w\s]+),\s([\w.\s]+),\s(.+)/
			{
				$DateTime = $1;
				$Type = $2;
				$Category = $3;
				$Details = $4;
			}
	</Exec>
</Input>

I like to throw on something like this to my tests to see them more clearly to_json();log_info($raw_event);.
It will put the fields into a nice JSON package and display it out.
While troubleshooting, I also like running NXLog in the foreground instead of as a service so that I can see messages realtime and make tweaks. nxlog -f