MySQL notes - Revision history

Chubbard: /* In String */

2024-01-02T23:46:32Z

In String

@@ Line 111: / Line 111: @@
 +-------------------------------+---------------------------+
 rows in set (0.00 sec)
 </pre>
 [[Category:MySQL]]

Chubbard: /* In String */

2023-12-30T18:09:51Z

In String

← Older revision		Revision as of 11:09, 30 December 2023
Line 84:		Line 84:
	Looks like a substring match that can be easily used for filtering down result sets. Looks like the result is case insensitive, and we need a binary swtich of we want to be case sensitive		Looks like a substring match that can be easily used for filtering down result sets. Looks like the result is case insensitive, and we need a binary swtich of we want to be case sensitive

			<pre>
	mysql> select display_name, INSTR(display_name, 'lo') from trapEventMap where INSTR(display_name, 'lo') > 0;		mysql> select display_name, INSTR(display_name, 'lo') from trapEventMap where INSTR(display_name, 'lo') > 0;
	+-------------------------------+---------------------------+		+-------------------------------+---------------------------+

Chubbard: /* In String */

2023-12-30T18:09:23Z

In String

← Older revision		Revision as of 11:09, 30 December 2023
Line 82:		Line 82:
	</pre>		</pre>
	====In String ====		====In String ====
	Looks like a substring match that can be easily used for filtering down result sets. ~~With~~ the ~~way my database table~~ is ~~configured~~, ~~it appears~~ to be ~~case insensitive. I suspect an alter table would make this~~ case sensitive ~~if needed..~~		Looks like a substring match that can be easily used for filtering down result sets. Looks like the result is case insensitive, and we need a binary swtich of we want to be case sensitive
	~~<pre>~~

	mysql> select display_name, INSTR(display_name, 'lo') from trapEventMap where INSTR(display_name, 'lo') > 0;		mysql> select display_name, INSTR(display_name, 'lo') from trapEventMap where INSTR(display_name, 'lo') > 0;
Line 101:		Line 100:

	</pre>		</pre>
			<pre>
			mysql> select display_name, INSTR(display_name, 'lo') from trapEventMap where INSTR(binary display_name, 'lo') > 0;
			+-------------------------------+---------------------------+
			\| display_name \| INSTR(display_name, 'lo') \|
			+-------------------------------+---------------------------+
			\| network-port-block \| 15 \|
			\| stpInstanceTopologyChangeTrap \| 16 \|
			\| check_load \| 7 \|
			+-------------------------------+---------------------------+
			3 rows in set (0.00 sec)
			</pre>

	[[Category:MySQL]]		[[Category:MySQL]]

Chubbard: /* In String */

2023-12-30T18:07:00Z

In String

← Older revision		Revision as of 11:07, 30 December 2023
Line 82:		Line 82:
	</pre>		</pre>
	====In String ====		====In String ====
	Looks like a substring match that can be easily used for filtering down result sets.		Looks like a substring match that can be easily used for filtering down result sets. With the way my database table is configured, it appears to be case insensitive. I suspect an alter table would make this case sensitive if needed..
	<pre>		<pre>

Chubbard: /* Group and sorts */

2023-12-30T18:06:09Z

Group and sorts

← Older revision		Revision as of 11:06, 30 December 2023
Line 81:		Line 81:

	</pre>		</pre>
			====In String ====
			Looks like a substring match that can be easily used for filtering down result sets.
			<pre>

			mysql> select display_name, INSTR(display_name, 'lo') from trapEventMap where INSTR(display_name, 'lo') > 0;
			+-------------------------------+---------------------------+
			\| display_name \| INSTR(display_name, 'lo') \|
			+-------------------------------+---------------------------+
			\| network-port-block \| 15 \|
			\| failedLoginFromHost \| 7 \|
			\| invalidUserLoginAttempted \| 12 \|
			\| stpInstanceTopologyChangeTrap \| 16 \|
			\| failedUserLoginTrap \| 11 \|
			\| egpNeighborLoss \| 12 \|
			\| checkPortLocal-22 \| 10 \|
			\| check_load \| 7 \|
			+-------------------------------+---------------------------+
			8 rows in set (0.00 sec)

			</pre>


	[[Category:MySQL]]		[[Category:MySQL]]

Chubbard: /* Group and sorts */

2023-12-28T18:36:16Z

Group and sorts

← Older revision		Revision as of 11:36, 28 December 2023
Line 64:		Line 64:
	</pre>		</pre>
	====Group and sorts ====		====Group and sorts ====
			Just a simple example of grouping by some column along with a column rename to something more "pretty" and then returning in descending order

	<pre>		<pre>
	mysql> select eventSeverity, count(*) as count from history group by eventSeverity order by eventSeverity DESC;		mysql> select eventSeverity, count(*) as count from history group by eventSeverity order by eventSeverity DESC;

Chubbard: /* Notes for working with MySQL */

2023-12-28T18:33:03Z

Notes for working with MySQL

@@ Line 1: / Line 1: @@
 ===Notes for working with MySQL===
 Basic table join for update from one table to another with where clause
 <pre>

Chubbard at 18:24, 28 December 2023

2023-12-28T18:24:56Z

← Older revision		Revision as of 11:24, 28 December 2023
Line 53:		Line 53:
	1 row in set (0.00 sec)		1 row in set (0.00 sec)
	</pre>		</pre>
			====Group and sorts ====
			<pre>
			mysql> select eventSeverity, count(*) as count from history group by eventSeverity order by eventSeverity DESC;
			+---------------+-------+
			\| eventSeverity \| count \|
			+---------------+-------+
			\| 5 \| 27170 \|
			\| 4 \| 8376 \|
			\| 3 \| 19299 \|
			\| 2 \| 1496 \|
			\| 1 \| 1995 \|
			\| 0 \| 13 \|
			+---------------+-------+
			6 rows in set (0.19 sec)

			</pre>



	[[Category:MySQL]]		[[Category:MySQL]]

Chubbard at 00:25, 20 May 2023

2023-05-20T00:25:06Z

← Older revision		Revision as of 17:25, 19 May 2023
Line 31:		Line 31:
	</pre>		</pre>

	Simple JOIN clause using the event database on backup01		====Simple JOIN====
			Also called an INNER JOIN
			clause using the event database on backup01
			This assumes that there will ALWAYS be a match between the two tables. If there is not, then no rows returned.
	<pre>		<pre>
	SELECT d.hostname,d.address,p.Properties FROM Device d JOIN DeviceProperties p on p.DeviceId=d.id WHERE d.id=14 \G		SELECT d.hostname,d.address,p.Properties FROM Device d JOIN DeviceProperties p on p.DeviceId=d.id WHERE d.id=14 \G
Line 39:		Line 42:
	Properties: {"snmpEnable":"true","snmpVersions":["2","1","3"],"snmpVersion":"2","snmpPort":161,"snmpTries":2,"snmpMonitorTimeout":1000000,"snmpV3SecurityName":"123Fake","snmpV3Hash":"456StillFake","snmpV3Password":"789Fake","snmpCommunities":["public","private","foobar"],"snmpCommunity":"public","snmpInterfaceIgnore":"lo vmnet[0-9]","snmpFilesystemIgnoreType":"romram virtualMemory loop","snmpfilesystemIgnoreMatch":"\/snap \/blah\/lxd rando\/foobar","snmpMaxOidPerRequest":"40","winUser":"admin administrator","winPassword":"password password123","winDomain":"DC:\\Domain","winEventLogSeverity":"2","winEventLogMonitor":"False","winWmiMonitorIgnore":"True","nrpeMonitorTimeout":"20","nmapDiscoverPortArgs":"-p 1-10024 -sT --open -oG -","nmapUpdatePortList":"False","nrpeCommandPath":"\/usr\/local\/bin\/nrpe","nmapCommandPath":"\/usr\/bin","sshPort":"22","sshKeyBase64":"LS0tLS1CRUdJTiBSU0EgUFJJVkFURSBLRVktLS0tLQpNSUlFcEFJQkFBS0NBUUVBdHRON05jVmtl","sshUser":"failSafeUser","sshPassword":"notAGoodIdea","sshPassowrdEncrypted":"stillNotAGreatIdea","pingMonitorIgnore":"False","comments":"","links":"https:\/\/linux.org"}		Properties: {"snmpEnable":"true","snmpVersions":["2","1","3"],"snmpVersion":"2","snmpPort":161,"snmpTries":2,"snmpMonitorTimeout":1000000,"snmpV3SecurityName":"123Fake","snmpV3Hash":"456StillFake","snmpV3Password":"789Fake","snmpCommunities":["public","private","foobar"],"snmpCommunity":"public","snmpInterfaceIgnore":"lo vmnet[0-9]","snmpFilesystemIgnoreType":"romram virtualMemory loop","snmpfilesystemIgnoreMatch":"\/snap \/blah\/lxd rando\/foobar","snmpMaxOidPerRequest":"40","winUser":"admin administrator","winPassword":"password password123","winDomain":"DC:\\Domain","winEventLogSeverity":"2","winEventLogMonitor":"False","winWmiMonitorIgnore":"True","nrpeMonitorTimeout":"20","nmapDiscoverPortArgs":"-p 1-10024 -sT --open -oG -","nmapUpdatePortList":"False","nrpeCommandPath":"\/usr\/local\/bin\/nrpe","nmapCommandPath":"\/usr\/bin","sshPort":"22","sshKeyBase64":"LS0tLS1CRUdJTiBSU0EgUFJJVkFURSBLRVktLS0tLQpNSUlFcEFJQkFBS0NBUUVBdHRON05jVmtl","sshUser":"failSafeUser","sshPassword":"notAGoodIdea","sshPassowrdEncrypted":"stillNotAGreatIdea","pingMonitorIgnore":"False","comments":"","links":"https:\/\/linux.org"}
	1 row in set (0.00 sec)		1 row in set (0.00 sec)
			</pre>
			====LEFT Join:====
			This makes no assumption on the second table, just that there will be a match in the first one.
			<pre>
			SELECT d.hostname,d.address,p.Properties FROM Device d LEFT JOIN DeviceProperties p on p.DeviceId=d.id WHERE d.id=26 \G
			************************* 1. row *************************
			hostname: pipeload.com
			address: 172.92.156.161
			Properties: NULL
			1 row in set (0.00 sec)
			</pre>

	~~</pre>~~



	[[Category:MySQL]]		[[Category:MySQL]]

Chubbard: /* Notes for working with MySQL */

2023-05-20T00:18:41Z

Notes for working with MySQL

← Older revision		Revision as of 17:18, 19 May 2023
Line 33:		Line 33:
	Simple JOIN clause using the event database on backup01		Simple JOIN clause using the event database on backup01
	<pre>		<pre>
	~~select~~ d.hostname,d.address,p.Properties ~~from~~ Device d ~~join~~ DeviceProperties p on p.DeviceId=d.id ~~where~~ d.id=14 \G		SELECT d.hostname,d.address,p.Properties FROM Device d JOIN DeviceProperties p on p.DeviceId=d.id WHERE d.id=14 \G
	************************* 1. row *************************		************************* 1. row *************************
	hostname: nas01.iwillfearnoevil.com		hostname: nas01.iwillfearnoevil.com